Chapter 28. Practical auditing in SQL Server 2008

Jasper Smith

In SQL Server 2008 Enterprise Edition, instance- and database-level audit is now built into the Database Engine with its own set of instance- and database-level objects—Server Audit and Server Audit Specification at the instance level, and Database Audit Specification at the database level.

SQL Server 2005 introduced event notifications and data definition language (DDL) triggers as mechanisms for auditing DDL statements, but coverage of events wasn’t complete. There was no support for auditing access to data, and there was no tool support available in SQL Server Management Studio (SSMS).

Generating audit event s in SQL 2008 is extremely lightweight compared to previously available mechanisms, and is based on the new extended events infrastructure, which is designed to have an extremely low overhead even for large numbers of events. It also allows much finer-grained event filtering.

---

Note

All of the new audit features described in this chapter require SQL Server 2008 Enterprise or Developer Edition, and aren’t available in lower editions.

---

Overview of audit infrastructure

In SQL Server 2008, all events are now auditable using the new audit objects, including those not available via event notifications in previous versions of SQL Server. Configuration is greatly simplified with built-in tool support in SSMS. Figure 1 gives an overview of the various audit objects.

Figure 1. Overview of audit object relationships

Server audit objects

You can define the properties of an audit, such as Queue Delay or Action on Audit Failure, as well as the output target, such as File, Windows Application Log, or Windows Security Log. You can create multiple server audits, each of which defines its own target.

Server audit specification objects

You can define the audit action groups that you want to audit at the instance level, along with the server audit they belong to. There can be a maximum of one server audit specification per server audit. You can create multiple server audit specifications, as long as each one uses a separate server audit.

Database audit specification objects

You can define the individual audit actions or action groups that you want to audit at the database level, including any filters and the server audit they belong to. There can be a maximum of one database audit specification per database per server audit. You can create multiple database audit specifications for the same database, but they need to belong to separate server audits.

Server audits

The Server Audit object is the first object you create when enabling auditing for an instance of SQL Server 2008. It defines the output target for audit events generated by audit specifications. You can choose from three possible audit output types for a Server Audit object:

• Windows Application Event Log
• Windows Security Event Log
• File (local or remote)

When you specify the Application or Security Log target, the settings listed in table 1 are available for configuration.

Table 1. Server Audit configuration settings for Application and Security Log targets

Setting	Description
Queue Delay	Amount of time in milliseconds that events are buffered before being forced to be processed. To enable synchronous event delivery, you’d set this to 0. Synchronous delivery may have a performance impact. The default value is 1000.
Shutdown on Failure	Whether the SQL instance will shut down if audit events can’t be written to the target. The default value is CONTINUE.
Audit GUID	To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID can’t be modified after the audit has been created.

---

Note

Writing events to the Windows Security Log isn’t allowed on Windows XP.

---

Configuring the Windows Security Log target

In order to allow SQL Server to write events to the Windows Security Log, a number of additional configuration steps are required. The following walkthrough demonstrates how to enable the Windows Security Log target for Windows Server 2003. For details of how to configure this target for Windows Server 2008, see the SQL Server Documentation (http://msdn.microsoft.com/en-gb/library/cc645889.aspx).

1. Launch the Local Security Policy MMC snap-in by clicking Start > Run > secpol.msc.
2. Select the Audit Policy folder under the Local Policies folder and double-click Audit Object Access in the right pane.
3. Check both the Success and Failure check boxes in the resultant dialog, as shown in figure 2.

   Figure 2. Enabling Audit Object Access

   

4. Click OK to apply the changes.
5. In the same snap-in, select the User Rights Assignment folder under Local Policies and double-click on Generate Security Audits in the right pane.
6. Add the SQL Service account as shown in figure 3 and click OK.

   Figure 3. Enabling Generate Security Audit for SQL Service account

   

7. Restart the SQL Server service in order for the changes to take effect.

---

Note

The required options can be set by Group Policy in a domain environment, in which case local settings will be overwritten. You should discuss these settings with the team that manages Group Policy to ensure the required settings remain in effect for SQL Servers where you need to be able to write events to the Security Log.

---

Creating a server audit using the Windows Security Log target

To create a server audit, you can either use SSMS or T-SQL. The following example demonstrates how to create a server audit that uses the Windows Security Log using SSMS:

1. In SSMS Object Explorer, expand the Security node, right-click on the Audits node, and select New Audit from the context menu, as shown in figure 4.

   Figure 4. Creating a new audit using SSMS

   

2. In the Create Audit dialog box shown in figure 5, specify the audit name as SecurityLogAudit, choose Security Log from the Audit drop-down list, and click OK.

   Figure 5. Setting server audit properties using SSMS

   

3. Note that the server audit has been created in a disabled state, as indicated graphically by the small downward-pointing red arrow on the server audit icon. In order to use this server audit, it must first be enabled. To enable the server audit, right-click on it and select Enable Audit from the context menu, as shown in figure 6.

   Figure 6. Enabling a server audit using SSMS

   

The code in listing 1 is the equivalent of what we’ve just done via SSMS, but using T-SQL.

Listing 1. Creating a server audit using the Security Log target

CREATE SERVER AUDIT [SecurityLog]
TO SECURITY_LOG
WITH
(  QUEUE_DELAY = 1000,
   ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT [SecurityLog]
WITH(STATE=ON)
GO

---

Note

Windows Security Log is the most secure destination for auditing events from system administrator activity. It can also be used to integrate with the ACS (Audit Collection Service) functionality in SCOM 2007 (System Center Operations Manager).

---

Creating a security audit using the Windows Application Log target

To create a server audit using the Windows Application Log target, use the code shown in listing 2. As with the previous example, the server audit can be created using SSMS or T-SQL. The same options are available as for the Security Log target described in table 1. The server audit will be created in a disabled state and must be enabled before a server or database audit specification can write audit events to it.

Listing 2. Creating a server audit using the Application Log target

CREATE SERVER AUDIT [ApplicationLog]
TO APPLICATION_LOG
WITH
(  QUEUE_DELAY = 2000,
   ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT [ApplicationLog]
WITH(STATE=ON)
GO

Configuring a server audit using the File target

When you specify the File target, the settings in table 2 are available for configuration.

Table 2. Server audit configuration settings for File targets

Setting	Description
Queue Delay	Amount of time in milliseconds that events are buffered before being forced to be processed. To enable synchronous event delivery, you’d set this to 0. Synchronous delivery may have a performance impact. The default value is 1000.
Shutdown on Failure	Whether the SQL instance will shut down if audit events can’t be written to the target. The default value is CONTINUE.
Audit GUID	To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID can’t be modified after the audit has been created.
Filepath	The folder used to store the audit files. The filenames are automatically generated based on the audit name and GUID.
Maxsize	The maximum size of an audit file. The default value is UNLIMITED.
Max Rollover Files	The maximum number of rollover audit files. The default value is 0 (unlimited).
Reserve Disk Space	Whether to preallocate disk space to the Maxsize value. The default value is OFF.

The code in listing 3 demonstrates creating a server audit that uses the File target. In this example, the audit folder is C:AuditServer, the maximum size on any individual file is 100 MB, the number of rollover files is unlimited, and we aren’t preallocating disk space for the audit files. The queue delay is set to 2 seconds; therefore, this is an asynchronous audit and it won’t cause the instance to shut down if audit events can’t be written to the target.

Listing 3. Creating a server audit using the File target

CREATE SERVER AUDIT [ServerAuditFile]
TO FILE
(  FILEPATH = N'C:AuditServer'
   ,MAXSIZE = 100 MB
   ,MAX_ROLLOVER_FILES = 0
   ,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT [ServerAuditFile]
WITH(STATE=ON)
GO

Server audit specifications

Server audit specifications define the audit action groups that you want to audit at the instance level, along with the server audit they belong to. There can be a maximum of one server audit specification per server audit. You can create multiple server audit specifications as long as each one uses a separate server audit.

At the instance level, you can specify one or more audit action groups (for database audit specifications, you can specify individual audit actions and filters as well). Note that actions that modify the audit itself (such as disabling or altering audit objects) are automatically audited. A large number of audit action groups are available; to find details on all of them, check Books Online (http://msdn.microsoft.com/en-gb/library/cc280663.aspx).

Creating server audit specifications

For the first example (demonstrated in listing 4), we’ll create a server audit specification to audit changes to logins using the SERVER_PRINCIPAL_CHANGE_GROUP and use the ApplicationLog server audit we created earlier. If you don’t specify the WITH clause, the server audit specification will be created in a disabled state. We’ll also generate some events for the audit to capture.

Listing 4. Creating a server audit specification using the Application Log target

CREATE SERVER AUDIT SPECIFICATION [AuditLoginChanges]
FOR SERVER AUDIT [ApplicationLog]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO

-- create some events
CREATE LOGIN AuditLoginDemo
WITH PASSWORD = 'sdkfds*)&(9kdsafk',
CHECK_POLICY=OFF
GO
ALTER LOGIN AuditLoginDemo WITH
DEFAULT_DATABASE = model,
DEFAULT_LANGUAGE = British
GO
DROP LOGIN AuditLoginDemo
GO

In the next example, we’ll create a server audit specification using SSMS that audits the SERVER_OPERATION_GROUP and SERVER_STATE_CHANGE_GROUP audit action groups and uses the ServerAuditFile server audit. To create a new server audit specification, follow these steps:

1. Expand the Security top-level node.
2. Right-click the Server Audit Specifications node and choose New Server Audit Specification. This will open the Create Server Audit Specification dialog box, as shown in figure 7.

   Figure 7. Creating a server audit specification using SSMS

   

3. Supply a name and select the server audit you want to use. Choose ServerAuditFile from the drop-down list.
4. You can then add one or more audit action groups. Select SERVER_OPERATION_GROUP and SERVER_STATE_CHANGE_GROUP.
5. Click OK to create the server audit specification.

The server audit specification will be created in a disabled state. To enable it:

1. Expand the Server Audit Specifications node in Object Explorer.
2. Right-click the ServerOperationsAndState audit specification and choose Enable Server Audit Specification.

The T-SQL code in listing 5 generates some events that will be captured by our audit in order for us to have some activity to view.

Listing 5. Creating events for the server audit specification

EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'clr enabled',1
RECONFIGURE
GO

Viewing audit events

To view the audit events that are written to the Application or Security Log, we can use either Windows Event Viewer or SSMS. When viewing events using Event Viewer, it’s helpful to create a filter for only Success and Failure audits, to cut down the number of records. When viewing the audit in SSMS, this is automatically done for you. Follow these steps to view the events in SSMS:

1.  

Expand the Security top-level node in SSMS.

2.  

Expand the Audits node and right-click the ApplicationLog Server Audit.

3.  

Select View Audit Logs, as shown in figure 8.

Figure 8. Viewing audit logs using SSMS

This launches the SSMS LogFile Viewer, which enables you to easily review audit events written to any of the available server audit targets, as shown in figure 9.

Figure 9. Viewing audit logs in the Log File Viewer

To view events from server audits that use the File target, we can use a new built-in system function called sys.fn_get_audit_file. This function allows you to extract the audit events from one or more audit files in a tabular format that can be queried with standard T-SQL or loaded into a table for further analysis. Listing 6 demonstrates how to query the sys.server_file_audits catalog view to obtain the audit folder and pass that to the sys.fn_get_audit_file function in order to view the audit events in SSMS.

Listing 6. Viewing audit events from T-SQL

DECLARE @folder VARCHAR(255)
SELECT @folder = log_file_path + '*'
FROM sys.server_file_audits WHERE name = 'ServerAuditFile'

SELECT * FROM sys.fn_get_audit_file(@folder,DEFAULT,DEFAULT)
ORDER BY event_time DESC

A number of new catalog views expose metadata about server audit specifications. The query in listing 7 demonstrates some of them and lists all server audit specifications, along with their properties.

Listing 7. Viewing details of all server audit specifications

SELECT sp.name AS ServerAuditSpecification,
      CASE WHEN sp.is_state_enabled =1
           THEN 'Y' ELSE 'N' END AS SpecificationEnabled,
      d.AuditActions,
      a.name AS ServerAudit,
      a.type_desc AS ServerAuditType,
      CASE WHEN a.is_state_enabled =1
           THEN 'Y' ELSE 'N' END AS AuditEnabled,
      st.status_desc AS AuditStatus,
      a.queue_delay AS QueueDelay,
      a.on_failure_desc AS OnFailure,
      st.audit_file_path AS CurrentFile,
      f.max_file_size AS MaxFileSize,
      f.max_rollover_files AS MaxRolloverFiles,
      CASE WHEN f.reserve_disk_space = 0 THEN 'N'
           WHEN f.reserve_disk_space = 1 THEN 'Y' END AS ReserveSpace
FROM sys.server_audit_specifications AS sp
JOIN sys.server_audits a
   ON sp.audit_guid = a.audit_guid
JOIN sys.dm_server_audit_status st
   ON a.audit_id = st.audit_id
LEFT JOIN sys.server_file_audits f
   ON a.audit_id = f.audit_id
LEFT JOIN (SELECT server_specification_id,
           STUFF((SELECT ',' + audit_action_name AS [text()]
                  FROM sys.server_audit_specification_details AS d2
                  WHERE d2.server_specification_id = d1.server_specification_id
                  ORDER BY audit_action_name
                  FOR xml path('')), 1, 1, '') AS AuditActions
           FROM sys.server_audit_specification_details AS d1
           GROUP BY server_specification_id) AS d
   ON sp.server_specification_id = d.server_specification_id
ORDER BY ServerAuditSpecification

Database audit specifications

Database audit specifications define the audit action groups, individual audit actions, and filters that you use to audit events at the database level, as well as the server audit they belong to. There can be a maximum of one database audit specification per database per server audit. You can create multiple database audit specifications for a database as long as each one uses a separate server audit.

Creating database audit specifications

In the following examples, we’ll create a number of database audit specifications for the AdventureWorks2008 sample database using a new file-based server audit.

---

Note

You can download the latest version of the AdventureWorks 2008 database from Codeplex: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx.

---

We’ll create a new file-based server audit that we’ll use for our database audit specification. The T-SQL code is shown in listing 8.

Listing 8. Creating a server audit for a database audit specification

CREATE SERVER AUDIT [AWDMLAudit]
TO FILE
(  FILEPATH = 'C:AuditDBAdventureWorksAWDMLAudit'
   ,MAXSIZE = 100 MB
   ,MAX_ROLLOVER_FILES = 0
   ,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [AWDMLAudit]
WITH(STATE=ON)
GO

We’ll now create a new database audit specification to audit the DML (Select, Insert, Update, and Delete) events for the HumanResources.EmployeePayHistory and Sales.CreditCard tables. As for the audit action groups available in server audit specifications, we can also specify individual audit actions and filter them based on individual objects and the user or role accessing them. In this example, we’ll specify the public database role so that DML for all users is captured. The T-SQL code is shown in listing 9.

Listing 9. Creating a database audit specification to audit DML activity

USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION AWSensitiveDMLAudit
FOR SERVER AUDIT AWDMLAudit
ADD (SELECT , INSERT , UPDATE, DELETE
     ON HumanResources.EmployeePayHistory
     BY PUBLIC),
ADD (SELECT , INSERT , UPDATE, DELETE
     ON Sales.CreditCard
     BY PUBLIC)
WITH (STATE = ON)
GO

Now generate some DML activity against the audited tables using the code in listing 10.

Listing 10. Creating DML activity

USE AdventureWorks2008
GO
SELECT TOP 10 * FROM Sales.CreditCard
GO
BEGIN TRAN
   UPDATE Sales.CreditCard
   SET ExpYear = 2009,ModifiedDate = GETDATE()
   WHERE CreditCardID = 6
ROLLBACK TRAN
GO
SELECT * FROM HumanResources.EmployeePayHistory
GO

To view the results of the DML activities, we’ll use the fn_get_audit_file function. We can join the results of this function with the sys.dm_audit_class_type_map and sys.dm_audit_actions management views to translate the action and object types to help make the results easier to understand. The T-SQL code is shown in listing 11, followed by the results in figure 10.

Figure 10. Results of listing 11

Listing 11. Viewing DML activity audit events in File target

USE master
GO
-- get the audit file
DECLARE @filepattern VARCHAR(300)
DECLARE @folder VARCHAR(255)
DECLARE @auditguid VARCHAR(36)
SELECT @auditguid = audit_guid,@folder = log_file_path
FROM sys.server_file_audits WHERE name = 'AWDMLAudit'

SELECT @filepattern = @folder + '*_' + @auditguid + '*'
-- view the results
SELECT a.name AS Action,c.class_type_desc AS ObjectType,
f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement
FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f
JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type
JOIN sys.dm_audit_actions a ON f.action_id = a.action_id
AND c.securable_class_desc = a.class_desc
WHERE f.action_id <> 'AUSC'
ORDER BY event_time DESC,sequence_number

A couple of interesting points are raised by the results. As you can see, the update statement is present twice (because the table needs to be read to qualify rows for the update, which is why both SELECT and UPDATE actions appear for the UPDATE statement), even though the transaction it was in was rolled back. Also note that the statement for the update is the autoparameterized version, rather than the statement text. Database audits don’t capture any before or after data images for DML actions.

---

Note

To capture the data that has changed, you could combine auditing with the new Change Data Capture functionality in SQL 2008 Enterprise Edition. See Books Online for details (http://msdn.microsoft.com/en-gb/library/bb522489.aspx).

---

Listing 12 shows more examples demonstrating the additional filtering available for database audit specifications. If you try to create these without deleting the previous database audit specification, they’ll fail because of the limit of one database audit specification per database per server audit. In order to DROP or ALTER a database audit specification, it must first be disabled.

Listing 12. Additional examples of database audit specifications

USE AdventureWorks2008
GO

-- audit all execution of stored procedures
CREATE DATABASE AUDIT SPECIFICATION Example1
FOR SERVER AUDIT AWDMLAudit
ADD (EXECUTE
     ON DATABASE::AdventureWorks2008
     BY PUBLIC)
WITH (STATE = ON)
GO

-- audit all updates in the Sales schema
CREATE DATABASE AUDIT SPECIFICATION Example2
FOR SERVER AUDIT AWDMLAudit
ADD (UPDATE
     ON SCHEMA::Sales
     BY PUBLIC)
WITH (STATE = ON)
GO

-- audit all schema changes in the database
CREATE DATABASE AUDIT SPECIFICATION Example3
FOR SERVER AUDIT AWDMLAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

SSMS has built-in tool support for database audit specifications. If you expand the Security node in a Database node in Object Explorer in SSMS, you’ll see the new database audit specifications node. To create a new database audit specification, follow these steps:

1.  

Right-click on the Database Audit Specifications node and choose New Database Audit Specification, as shown in figure 11.

Figure 11. Creating a database audit specification in SSMS

2.  

This will bring up the Create Database Audit Specification dialog box, which allows you to select the audit actions and groups, along with any applicable filtering.

3.  

Select some audit action groups and click OK to create the database audit specification, as shown in figure 12.

Figure 12. Configuring a database audit specification in SSMS

4.  

New database audit specifications are created in a disabled state. To enable the database audit specification, right-click on it and select Enable Database Audit Specification.

Summary

That brings us to the end of this introduction to the new built-in auditing features in SQL Server 2008. As you’ve seen, it’s extremely easy to configure and administer via T-SQL or SSMS. It’s also designed to be extremely lightweight and have less overhead than the existing mechanisms (SQL trace, DDL triggers, and event notifications).

About the author

Jasper Smith is an independent SQL Server consultant based in the UK, specializing in performance tuning and high availability solutions. He’s been working with SQL Server for more than eight years and has been a Microsoft MVP for SQL Server for the past six years. He also runs http://www.sqldbatips.com, home of popular free SQL tools and utilities such as Reporting Services Scripter, Expressmaint, and SQL 2005 Service Manager.