Walking through the database auditing process for SQL Server 2008 R2

Before creating your auditing policies, you have some decisions to make.  Obviously you need to decide on what information to audit, but you’ll also have to figure out where you want to store the audit records.  You have a few different choices on this second point; you can store the information in a flat file on the server’s hard drive, the Windows application log, or the Windows security log.  While you could technically setup the audit to write to a network share on a remote server in a secure manor, this usually isn’t recommended as you can miss audit information if the network storage location is rebooted while the SQL Server is online accepting connections.  This could potentially lead to holes in your SQL Server audit trail, causing a failure of whatever audit is driving the collection of the audit data.

Creating an audit in SQL Server

Before you can begin auditing data, you first need to tell SQL Server where you want to store the audit information.  For the purposes of this article, I’ll be writing the audit information to a flat file on the D drive in a folder called “Audit”. We can import those files later on using SQL Server Integration Services (SSIS).  To do this, connect to the server in Object Explorer and navigate to ServerName > Security > Auditing.  Right-click on Auditing and select New Audit from the context menu that opens.  You’ll then be shown a screen that when filled out will look similar to Figure 1.

Figure 1. Creating an audit in SQL Server 2008 (click to enlarge)

As you can see, in addition to naming the audit you can also set the queue delay (I’ll explain this below), where you want the audit to be written, and the amount of space each file can take up (if you are using files on disk).

If you are using the Application or Security Event Log as your destination, you’ll need to be aware of how these logs are configured to react when they fill.  This is especially true if you have the “Shut down server on audit log failure” option selected.

The Audit name field is fairly self-explanatory in its meaning; it is simply the name of the audit.  SQL Server will generate a name for you that you can either keep or change to something more meaningful than “Audit-{Date}-{Time}”. 

The Queue delay field tells SQL Server how much audit data can be stored within the queue before it is forced to be processed.  I like to think of it this way:

“In the event that the disks hosting the database are randomly lost, how much audit data can be lost without taking us out of compliance?”

The field will accept values from 0 to 2,147,483,647 milliseconds (24 days, 20 hours, 31 minutes, 23.647 seconds).  A value of 0 will turn the auditing from an asynchronous process to a synchronous process where the audit information will be written to the audit destination in real time instead of after the fact.

The Shut down server on audit log failure checkbox takes you from a “should audit” mode into a “must audit” mode.  If for some reason SQL Server can’t write its auditing information to the audit destination, the SQL Server instance will be shut down.  Depending on the industry you are in and the purpose of the SQL Server service and audit, you’ll have to make the judgment call when it comes to enabling this setting.  For example, if you work at a bank and this is the main database that holds all of your account information, then you probably will want to enable this option.  However, if you work at a book store and the database holds the inventory for your stores, you probably don’t need to enable this setting.

If you choose to log to a file, you’ll be presented with some additional options such as the number of files to keep, how large the files can get, and of course, where to store them.  In regards to where to store the audit files, SQL Server is looking for a valid path here rather than a specific file name, because it will be creating the file names for you as it writes the files.

The first of the file-specific settings is Maximum rollover files, which tells SQL Server how many of these audit files to keep on the disk.  The second setting tells SQL Server how large each file can get.  By default, both of these settings are set to “Unlimited,” which tells SQL Server to audit information until it fills the disk that you are writing audit data to.  Personally, I recommend setting the maximum file size to 1 GB or smaller to ensure the files are small enough that you can easily move them around as needed if space on the disk starts to become an issue.  You can then specify the number of rollover files as the amount needed for the audit data your auditors want to keep (or that your management is willing to provide storage for). 

The last setting on the screen is the Reserve disk space setting. This tells SQL Server to pre-allocate the space for the audit file so that once the audit file has been created, the entire amount of space for the file is available. That way, as the file is being used the disk won’t suddenly run out of space.  When logging your audit information to the disk, you’ll need to ensure the account that the SQL Server is running under has CREATE FILE rights within the folder at the NTFS level.

Now if you prefer to work within T-SQL to run the script on multiple servers and use the same configuration for each, see Figure 2.

Figure 2. Creating an audit for multiple servers using T-SQL (click to enlarge)

Creating audit specifications

Once you’ve created the audit, you need to create an audit specification.  This tells the SQL Server audit exactly what information to capture.  You can capture as much or as little information as you’d like, so long as the information that you capture meets your needs.  It is recommended that you not audit every event if you don’t need to as this will put a strain on the server and your storage subsystem -- especially on a very busy SQL Server.

To create an audit specification, look under the audit tree that we navigated to before within Object Explorer.  The next item on the list should be Audit Specifications.  Right-click on this option and select New Server Audit Specification from the context menu that pops up.  This will present you with a single page screen to select all the various events within the SQL Server that can be audited.

As you can see from the sample audit specification shown in Figure 3, you can select multiple different audit action types from the list so that a single specification can capture a wide variety of information.  In the case of the example below, we want to capture all DML access to the tables within the instance and all password changes to logins.  You’ll also notice that the names of the audit action types aren’t the easiest to read.

Figure 3. Creating an audit specification in SQL Server (click to enlarge)

As with the audit creation example above, you can also create audit specifications through T-SQL, as shown in Figure 4.

Figure 4. Using T-SQL for audit specifications (click to enlarge)

Once you have created the audit and audit specification, you’ll need to enable both objects.  In SQL Server Management Studio, this is done by right-clicking on each object and selecting the Enable {Object Name} option from the context menu.  If you are creating these objects via T-SQL, you can use the ALTER SERVER AUDIT and ALTER SERVER AUDIT SPECIFICATION statements, setting the STATE value to ON, as shown in Figure 5.

Figure 5. Enabling audits and audit specifications via T-SQL (click to enlarge)

At this point, all data that the audit specification has been configured to capture is being written to the audit files as specified by the audit that the specification is bound to.

In our case, we can see this by looking at the D:\Audit\ folder on the SQL Server. From there we can see if the file has been created and if SQL Server is writing data to it, as shown in Figure 6.

Figure 6. Verifying a SQL Server audit (click to enlarge)

Hopefully this short introduction to auditing in Microsoft SQL Server 2008 R2 has given you some idea of the simple configuration and amazing auditing power that Microsoft has put into our hands.  Like most new features, however, these capabilities are not backwards compatible with SQL Server 2005 and earlier releases, so you will need to explain to your auditor (probably more than once) that this audit data is only available on databases that are hosted on the newer servers. You may even be able to use this as a driving factor to get applications upgraded from SQL Server 2000 and 2005 to the newer versions from Microsoft. 

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. Check out his blog at SQL Server with Mr. Denny.