Implementing SQL 2008 SQL Server Audit

Last week I attended another Hands-On-Lab at the MTC center in Waltham, MA. One of the new SQL 2008 features I was able to kick around was SQL 2008 SQL Server Audit. Truthfully, I did not even know about this feature until I was handed the lab sheet. Fortunately I was already planning on spending the day learning new things, so this started off my day quite nicely.

Now, auditing a SQL Server instance is not anything new. However, SQL 2008 Enterprise version allows for you to quickly and easily configure SQL Server Auditing to automate your auditing needs. The simple version for how this all works is as follows:

1. Create a SQL Server Audit object
2. Create an Audit Specification (can be at the server or database level)
3. Turn it on
4. Enjoy some bacon

Creating a SQL Server Audit

A SQL Server Audit is a security object that collects and logs either actions or groups of actions. Which is another way of saying it is a hall monitor. It checks the halls and writes down what it sees, and pokes its head into the classroom windows (i.e., a database) as well, if you tell it to do so. SQL Server Audits are always in a disabled state when created, much like a hall monitor who needs to be told when to report for their first day of work.

You can create a SQL Server Audit with T-SQL or through SSMS, look for it under the Security folder and you will find a folder named ‘Audits’. It is worth noting that you create a server audit, not a database audit. This is because the audit object is associated with the entire instance. You will next specify what exactly you want to audit, which will be either an instance or database specification, or both if desired.

Creating a Server Audit Specification

A Server Audit Specification can also be created with T-SQL or through SSMS. In SSMS, the folder is located right below the Audits folder, you really cannot miss it unless you go looking somewhere else in which case go back to the start of this sentence and try it again. You can create only one Server Audit Specification per SQL Server Audit, but the specification can encompass multiple audit action groups, which just means that you define all of the instance level actions you want to be audited.

I will say there is a plethora of action groups for you to choose from, because no one knows how many actions groups make up a plethora and I like being intentionally vague. You can check the BOL for more details, or just be lazy and click on this link. You’re welcome.

When you create the specification you can define the state to be either ON or OFF, unlike the SQL Server Audit, which will be OFF upon creation. The specification must be enabled before you can audit the action groups which only makes logical sense and I fell silly for even bothering to remind you.

Creating a Database Audit Specification

A Database Audit Specification can be created for each database on the instance for each SQL Server Audit. This allows for a great deal of customization as you are able to have different types of audits on different databases.  A Database Audit Specification utilizes Extended Events to track and log events, which means you can add audit action groups or audit events to a Database Audit Specification, which is nice.

You can create a Database Audit Specification with T-SQL or through SSMS, you can find it in SSMS by going to your database and expanding the Security folder. Good luck.

Enabling the SQL Server Audit

If you are one of those that just love to use T-SQL, then go ahead and enable the SQL Server Audit using T-SQL. If you are lazy like me, just go into SSMS, find the Audit, right-click, and enable it. Done.

Once enabled you can view events in a log file viewer, providing of course that the events to be audited have happened. In other words, if you look to enable auditing on failed logins, you need to make certain a failed login has actually happened before you decide to send me a nasty email and say “this crap doesn’t work”. Thanks.

SQL 2008 Security Audit is very easy to implement. There is no wizard, but the overall architecture is not so complex that a wizard would even be necessary. The GUI itself will help lead you in the right direction. For example, you cannot create specifications unless a SQL Server Audit exists, which should be a big red flag for you to help guide you to create the SQL Server Audit first.

I love the direction that Microsoft is taking with regards to auditing. This new functionality is much more robust than anything they have had previously. It’s like the difference in eating at a restaurant that serves lobster bisque and foie gras with one that has a menu with words like “bucket”, “basket”, or “gutbuster”.