SQL Server 2008 Auditing

In an era of increasingly concern regarding regulations and their impact in the production environment, auditing is one of the most important topics. And, SQL Server 2008 introduces a new SQL Server Audit feature. This post will not cover all the details of it, but it will give you an opportunity to taste it.

In a very simple way audit is about WHAT to log and WHERE to log. So, with that in mind, let’s define the three most import audit objects in SQL Server 2008:

• Server Audit: defines WHERE the audit data should go. You can choose among file, Windows Security Log, and Windows Application Log.
• Server Audit Specification: defines WHICH server instance actions should be audited.
• Database Audit Specification: defines WHICH database actions should be audited.

Let’s do it!! In this post we will use SQL Server Management Studio to create an example, keep in mind that you can also use Transact-SQL to create these objects.

Let’s start with a new Server Audit:

1. Open SQL Server Management Studio, connect to your server, and expand the Security.
2. Right-click Audits and select New Audit…
3. On Create Audit dialog box (figure 1) note that I have Queue delay is equals to 0, configuring the audit to be synchronous. You can leave the default value of 1000 (1000 milliseconds) for an asynchronous option and to lessen performance impact.
   
4. Click OK.
5. Right-click the new Audit object and Enable it.

Now that we know where the audit data will go, let’s create a Database Audit Specification.

1. Expand the database you want to create an audit specification on.
2. Expand Security and right-click New Database Audit Specification.
3. Configure the Audit to be audit object created previously, and add some actions. (see figure 2)
   

   Figure 2

4. Click OK, and do not forget to enable it.

The steps to create a Server Audit Specification are almost the same.

Now that you have everything configured you can test it. And, to see the audit data you can use:

In a just few steps we created an audit solution. To know more about auditing:

• Join us @ ISACA Philadelphia Chapter this Tuesday, November 11th, 2009.
• Attended one of our classes: 6158 and 6231
• http://msdn.microsoft.com/en-us/library/dd392015.aspx
• Post an question.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.