SQL auditing – Why We Developed ApexSQL Audit – Part II

As we have seen in the 1st part of this article, in order to solve the overarching problem of easy compliance, we had to solve many different problems main of which were:

1. Capturing of what-was-executed and of other auditing events of interest
2. Fault tolerant auditing
3. Centralized storage of audited data and integrity checks
4. Centralized reporting
5. Prevention from tampering of audited data, or exposure when prevention is not possible (e.g. data tampered by trusted user accounts, hacked or otherwise)

In this 2nd part of this article, we will go deeper into solutions that we applied to each of these problems

Capturing of Auditing Events

ApexSQL Audit, as already noted, is built on top of SQL Server traces. It configures traces based on user configuration or, in case one isn’t available, uses the default configuration, which was carefully selected to cover the most common auditing requirements without capturing too much data. By default, it does not capture who-saw-what queries (in essence SELECT statements) as those are by far the most frequent statements, and capturing them by default would lead to huge amounts of data being captured by default whether its needed or not. This is however easily changeable through the ApexSQL Audit’s central console

By default, ApexSQL Audit captures data modification statements, schema modification statements, executions of stored procedures, functions and dynamic statements, security changes and login activities

Fault Tolerant Auditing

ApexSQL Audit was designed to be fault tolerant in a wide variety of scenarios. It transparently handles:

1. Network outages: since each audited SQL Server instance has its own local ApexSQL Audit instance, network outages don’t translate into auditing outages
2. SQL Server outages: ApexSQL Audit instances are Windows services independent of SQL Server service and are designed to reliably reestablish connections to monitored SQL Server instances
3. Central ApexSQL Audit instance outages: distributed instances will keep auditing data even when the central instance is unavailable (this can be seen as a superset of network outages – they are handled in the same manner)
4. Its own outages: ApexSQL Audit is very unlikely to be the only bug-free software on the face of the planet, and was designed recognizing this fact. For example, if it becomes unresponsive it will try to automatically restart itself
5. Data corruption or unexpected data: ApexSQL Audit will isolate data files that it cannot process correctly, and will periodically try to reprocess them

Centralized Data Storage

ApexSQL Audit stores all its configuration and audited data in a single database hosted on SQL Server 2008 R2 or later. The database is designed to:

1. Store in tamper-evident form, all configuration data, and capture all changes made to it by ApexSQL Audit. Any and all changes done through other means (e.g. direct queries) are considered tampering, and are exposed during integrity checks
2. Store in tamper-evident form, all audited data allowing for fast integrity checks by ApexSQL Audit
3. Logarithmically grow per number of captured events in general case, and linearly in the worst case scenario (when every captured event has completely different user, server, database and other similar properties). Considering the amount of potentially captured data, this was critical in allowing for fast reporting and controlling growth

Centralized Reporting

ApexSQL Audit includes a web application with 30 built-in reports and a custom report designer. Reporting was designed, in conjunction with a centralized data storage, to be quick even for very large amounts of audited data (dozens of millions of rows), offering paging, filtering and sorting of audited data. It also includes server-side exporting of audited data in several common formats

Built-in reports are split into several groups:

1. General reports show captured auditing events
2. Auditing overview reports show changes in auditing settings and integrity checks
3. Change and activity reports show audited activity with regards to changes in schema and data, and data access
4. Security reports show history of roles and users, logon history and similar

Exposure of Tampering of Audited Data

ApexSQL Audit was designed by recognizing and accepting the fact that completely preventing tampering by trusted parties is not possible with software-only solutions. It is thus designed to make undetectable tampering computationally expensive even for trusted parties. This is achieved by several techniques that we used in our database design:

1. Per-row hashes for audited and configuration data
2. Hash chaining of configuration data as it changes
3. Hash chaining of audited data in its captured order
4. Calculating hashes for groups of related rows (e.g. all audited data loaded in a single transaction)
5. Calculating hashes for all rows in selected tables
6. Tracking timestamps for all transactions done, validly performed through the ApexSQL Audit central instance, which allows triangulation of changes

Enterprise Auditing by ApexSQL

Other posts in this series will delve deeper into all these aspects of ApexSQL Audit and many others, including: when ApexSQL Audit is appropriate and when it’s not, what are its requirements and limitations and so on. Our intent with these articles is to inform, and be transparent in advance about all aspects of ApexSQL Audit so that our customers and users can make informed decisions, and come to understand for themselves, how it can, hopefully, fulfill their auditing needs. We have significant plans on extending ApexSQL Audit with enterprise and auditing features in the near future, and we look forward to hearing back from our community regarding our take on enterprise auditing