Auditing, Katmai Style

Ok, once again I've been unable to keep up with daily posts, so I've decided to stop fighting it. Since I'll most likely never be able to consistently post daily, I'm going to quit worrying about it and just focus on posting good content as I can.

In this installment though, let's explore the new Audit feature included in SQL Server 2008. SQL Server 2005 provided auditing via SQL Trace, C2 audit model, and DDL Triggers. While these methods are adequate they leave some room for improvement.

Into that gap steps the SQL Server Audit. This new feature is based on Extended Events and allows you to monitor server-level and database-level events, individually and in groups. The new SQL Server Audit object collects a single instances of the server or database-level actions and groups of actions to be monitored. The Audit object exists at the SQL Server instance level and you can have multiple audit objects per instances.

There are two specification objects can be included in an Audit object, the Server Audit Specification and the Database Audit Specification. You can only specify one server audit specification per audit, however you can have a database audit specification for each database in the instance. You can also only have a server audit specification or database audit specifications, not both.

As you can imagine a server audit specification collects information about the server, which are raised by the Extended Events feature. There are several predefined groups of actions, which are events exposed by the database engine, known as audit action groups. These can be included in your server audit specification object within the audit object.

The database audit specification collects what else but database-level audit actions, which are raised by the Extended Events feature. You can include either groups of actions or single actions to a database audit specification. Again, audit action groups are provided as predefined groups of actions.

So, we can collect all this audit information, now what? Well, obviously this feature wouldn't be complete without the ability to record the information somewhere. This is where the audit target comes into play. Each audit object sends the audit results to a target. That target can be a file, the Windows Security event log, or the Windows Application event log. Note that writing to the Windows Security event log requires elevated permissions, so in order for audit results to be logged there, the SQL Server service account will have to be granted the "Generate security audits" permission in the group or local policy.

The process of creating and using a SQL Server Audit is as follows;
1. Create a SQL Server Audit object
2. Create either a server audit specification or a database audit specification and map it to the audit object.
3. By default the Audit and Specification objects are disabled, so in this step enable them.
4. Auditing occurs and  you can read the data in the target you defined as part of the Audit object.

Let's see an example:

USE master
GO

--Create the audit object
CREATE SERVER AUDIT SrvAudit TO APPLICATION_LOG;
GO

--Create the audit specification object
CREATE SERVER AUDIT SPECIFICATION AuditSpec
--and map it to the audit object
FOR SERVER AUDIT SrvAudit
--then add the audit action group
ADD (FAILED_LOGIN_GROUP);
GO

--Enable the audit
ALTER SERVER AUDIT SrvAudit WITH (STATE = ON);
GO

So looking at our Application log after attempting to log in with invalid credentials we see:

(Details)

For more information about auditing in SQL Server 2008 check out the SQL Server 2008 BOL, topic Auditing.

Next time we'll look into some of the IDE and configuration tool enhancements. Until then, keep slinging.

Technorati Tags: SQL Server 2008,SQL Server,Auditing,Event Log,Enhanced Event feauture,Server audit,Database audit,database,RDBMS