SQL auditing – Why We Developed ApexSQL Audit – Part I

ApexSQL has a long history in SQL Server auditing space with two tools that either prepare databases for auditing, or actually perform auditing. ApexSQL Trigger automates creation of triggers (among other things) that track data modifications on selected tables, while ApexSQL Log reads the database transaction log, extracting data modifications from it. Both of these tools show who-did-what, achieving it in different ways, with their own relative strengths and weaknesses. But these tools also lack the means to satisfy stringent regulations on data access (including who-saw-what), and out-of-the-box prevention from tampering, and that is why we built ApexSQL Audit: to make compliance with auditing regulations easy

In order to make compliance easy we had to solve the following problems:

1. Capturing of what-was-executed (especially, when needed, of who-saw-what data) 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)

SQL Server offers a way to collect and monitor a wide variety of events that happen on it: SQL Server traces. Among other things, it allows capturing of events related to execution of queries, changes made to permissions, failed login attempts and other similar data. An application could monitor all such events and filter them per its requirements, auditing or otherwise. SQL Server Profiler is one such application, built completely on top of SQL Server traces, and there is arguably no better known example of how useful tracing is. ApexSQL Audit was built on top of the same infrastructure already offered by SQL Server

That said, the tracing on its own is not, nor it is intended to be, an out-of-the-box solution for auditing. There are complexities related to tracing that have to be solved by any enterprise auditing solution that relies on it:

1. Traces can produce prodigious amount of data, and their configuration has to be carefully selected for optimal results
2. Trace data can be stored in trace files or trace tables, but this data is produced in a non-relationally friendly format where each captured event is represented by many different rows (one for each property of the event) so, in most cases, this data has to be post-processed
3. The captured data should be stored somewhere where it’s easily accessible – hopefully in a centralized data repository. This requires transferring of the audited data to remote servers or capturing it directly on the centralized server, making network outages and auditing outages equivalent
4. Transferring of audited data has to be reliable even in the case of network outages, and it requires encryption to prevent in-flight eavesdropping and/or tampering
5. Centralized data repository has to be able to expose tampering while audited data is at-rest in the database, and at the same time, allow fast querying of audited data

ApexSQL Audit is our attempt to solve all these problems at the same time, with a single solution designed from the ground up for centralized configuration of auditing options, and centralized reporting of audited data. We will delve more a bit more deeply into each problem and its solution in the 2nd part of this article

Short Introduction to ApexSQL Audit Architecture

ApexSQL Audit’s architecture consists of five components:

1. Central Database Repository: a database hosted on a SQL Server, designed to centrally store configuration and audited data in a tampering-evident form
2. Central ApexSQL Audit instance, running on the same server where central repository database is hosted, responsible for handling storage of configuration and audited data to central repository database
3. Web reports hosted on the Internet Information Services (IIS) server, usually on the same server where central repository database is hosted
4. ApexSQL Audit GUI configuration application that allows configuration of auditing options by communicating with the central instance
5. Distributed ApexSQL Audit instances, one on each server, capable of auditing one or more SQL Server instances local to them

Audited data is captured by distributed instances, and stored locally until transferred to the central instance. There it is processed and stored into the Central Repository Database. This allows distributed collection to the centralized storage, and querying of audited data by reports or custom user queries

Configuration data is both queried and stored only by the central instance, which is accessed by the GUI configuration application. This allows centralized configuration of all distributed ApexSQL Audit instances

As mentioned before, in the 2nd part of this article we will delve more deeply into solutions for aforementioned problems that we had to solve in order to make compliance easy