Database auditing – Introducing ApexSQL Audit

As part of our gearing toward the release of ApexSQL Audit beta, we will be releasing a series of posts addressing issues of why build another auditing tool, what new value it brings and how it achieves it. In the coming weeks we will be releasing posts with following abstracts:

Why We Developed ApexSQL Audit

This post will, rather obviously, address the issue of “why”:

1. What we offer in a way of auditing today: who-changed-what through triggers and transaction log reading. Why that isn’t enough for some of our customers: they also need to know who-saw-what
2. What we wanted to solve: make compliance with regulations easy (including things like HIPPA). We did what was needed to solve that but then what we did to make it more secure and reliable for our customers and easier and cheaper to manage
3. We built ApexSQL Audit to solve:
   • Who saw what
   • Fault tolerant auditing
   • Centralized storage of auditing configuration and results
   • Centralized reporting
   • Make tampering evident even when done by trusted parties (hacked or otherwise)
4. Short introductions on how we managed to do that

Capturing Who Saw What

This is a hands-on post that will show how not to capture who-saw-what, how to do it manually and how to do it easily – with ApexSQL Audit:

1. Why it’s sometimes important to know who saw what (e.g. HIPAA)
2. In order to capture who saw what we have to know what was executed on the server. This isn’t supported either by triggers or by transaction log reading, which are the tools we already offer, so we had to create another tool
3. There are manual ways to capture who saw what (e.g. all access is done through a stored procedure, store procedure keeps a track of who saw what in a table) but these methods are fragile as they involve coding for it and don’t solve a problem of ad hoc queries done by trusted parties
4. Capturing of who-saw-what can be done through SQL Server Profiler but there are difficulties: you have to do everything manually, figuring out events, capturing them, exporting the data, loading it, etc. and it *still* doesn’t solve the problem of trusted parties)
5. SQL Server Profiler uses traces so we built our solution on top of traces, essentially automating the work that could be done with the use of SQL Server Profiler and then adding value through additional curation of captured data, automatic transfers and loading into centralized repository, parsing for referenced objects, etc.
6. An example of end results in reports

Fault Tolerant Auditing

A post dealing with fault tolerance, problems with achieving it in auditing and how it was done in ApexSQL Audit

1. Describe problems with centralized auditing solutions (network outages mean auditing outages) and challenges with manual solutions
2. How we make sure that our service is always monitoring SQL Server even if itself is killed how it’s restarted, even if loses connection with SQL Server, how we leverage the fact that tracing is independent of our own processes
3. What happens with audited data at-rest when the application crashes or is stopped, how we restart our work, how we ensure that such outages don’t duplicate the data, how we ensure that even our own possible defects with processing traces don’t totally stop processing by isolating error-inducing files, how we make sure that our own process doesn’t hang in processing by self-monitoring, etc.

Tampering-evident Auditing

In-depth post describing our approach to tamper-evident data storage in general and ApexSQL Audit in particular:

1. Preventing tampering by trusted parties is impossible but making *undetectable* tampering can be made much more expensive (in effort, time, resources required)
2. We make tampering expensive by providing storage in a database that was specifically designed to make undetectable tampering very expensive
3. This is achieved by a combination of hashes, hash chaining, grouping and calculating table hashes on one hand and built-in timestamps on the other. Describe how these are calculated and stored, how they are checked
4. Security through obscurity doesn’t work so what would have to be done to attack this

ApexSQL Audit Auditing Paradigm

The paradigm behind ApexSQL Audit and why we think it works:

1. Data tampering by trusted parties (hacked or real) cannot be prevented but it can be detected
2. ApexSQL Audit makes undetectable tampering very expensive but it was also designed to detect attacks like stopping the service or somehow corrupting it
3. We show downtimes so that auditors can be aware of them and use them in their own reports – there isn’t much that we can do about trusted parties stopping the service, changing the data and restarting it again. But we will detect it and inform about it
4. Regarding corruption nothing is impossible to crack and corrupt but we try to detect corruption early. Obviously a corrupted application can be made to fake its un-corruptness but we try to make it expensive
5. To repeat: no software-only solution can prevent tampering by trusted parties so ApexSQL Audit doesn’t pretend that it can do that. What it does instead is to make tampering much more expensive and hopefully more expensive than its worth. For example, attacks that subtly tamper financial data are almost always meant not to be detected and ApexSQL Audit shines a light on those