sqldbatips.com blog

New in SQL 2008: Server and Database Auditing Part I

In this post I'll cover how to create the Server Audit Object itself and the configuration for the targets of the audit (Application Event Log, Security Event Log or File)

Server Audit Object

The Server Audit object is the first object you create when enabling auditing on an instance of SQL Server 2008. It defines the target for audit events generated by Audit Specifications. In SQL Server 2008 there are 3 possible audit outputs

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

When you specify the Application or Security Log, the only options available for configuration are

• Queue Delay - the amount of time in milliseconds that events are buffered before being forced to be processes. To enable synchronous event delivery you would set this to 0. The default value for this is 1000 (1 second)
• Shutdown on Failure - if this option is selected then the instance will shutdown if audit events cannot be written to the target

There are some additional taks that need to be performed to enable SQL Server to write audit events to the Windows Security Log which I'll cover in the example below. They are also well documented in Books Online. Note that writing to the Security Log is not supported on Windows XP.

When you specify File you can configure the folder that the audit files will be generated in, the maximun number of rollover files, the maximum size of each file and whether to reserve the disk space for the audit file if a maximum size is specified. We'll cover the actuall DDL for this later on in the example code.

When a Server Audit object is created it is initially in a disabled state. In order for audit events to be written to the target it must first be enabled.

There can be a maximum of 1 Server Audit Specification per Server Audit (you can of course create multiple Server Audits) and 1 Database Audit Specification per database per Server Audit (again you can create multiple Database Audit Specifications for a database but they need to use multiple Server Audits)

Example Walkthrough

In this example we'll configure three Server Audit objects utilising each of the available targets (application Log, Security Log,File). I'll demonstrate how to do this via TSQL and via SSMS.

1. For the first object we will create a Server Audit object that targets the Windows Application Event Log
   
   CREATE SERVER AUDIT [ApplicationLog]
   TO APPLICATION_LOG
   WITH
   (  QUEUE_DELAY = 2000
      ,ON_FAILURE = CONTINUE
   )
   GO
   
   In this example, we have specified a Queue Delay of 2000 milliseconds and to not shutdown if audit records cannot be written to the application log. Initially this Server Audit is created in a disabled state. To enable it we can use the following command
   
   ALTER SERVER AUDIT [ApplicationLog] WITH(STATE=ON)
   
   To perform the same action using SSMS we would connect to our instance using Object Explorer and expand the Security node, right click on Audit and choose New Audit from the context menu
   
   
   
   This will bring up the Create Audit dialog where we can specify the parameters for our Server Audit Object as shown below
   
   
   
   This will create the Server Audit object in a disabled state exactly as if we created it via TSQL so to enable it via SSMS simply select the Server Audit Object, right click and choose Enable Audit from the context menu
   
   
    


2. For the second object we will create a Server Audit object that targets the Windows Security Event Log. Before we can do this however, we need to configure a couple of windows settings to allow SQL Server to write events to the Security Event Log. The example below is for Windows 2003, the procedure for Windows 2008 is slightly different and is documented in Books Online. Note that the options that need to be set here may well be set by Group Policy in a domain envrionment in which case local settings could be overwritten. You would need to discuss these settings with the group that manages Group Policy to ensure the required settings remain in effect for SQL Servers where you want to be able to write events to the Security Log.
   
   First we need to enable Audit object access for Success and Failure. We do this using the Local Security Policy mmc snapin (Start>Run>secpol.msc)
   
   
   
   Then we need to assign the Generate Security Audits right to the SQL Server service account. This is done through the same mmc snapin as above by selcting the User Rights Assignment node and double clicking on the Generate Security Audits entry in the right hand pane and adding in the SQL Service account. In this example I'm using a local user account as my server is not part of a domain.
   
   
   
   Once these 2 changes have been made, restart the SQL Server service and you will then be able to create a Server Audit targetting the Windows security log as shown below. Again, this will be created in a disabled state so you will need to enable it exactly the same as for the Application Log example. Note SQL Sever 2008 RC0 has a bug with regard to viewing events written to the Security Log in Event Viewer. See this post for the workaround.
   
   CREATE SERVER AUDIT [SecurityLog]
   TO SECURITY_LOG
   WITH
   (  QUEUE_DELAY = 2000
      ,ON_FAILURE = CONTINUE
   )
   GO  
   


3. For the third object we will create a Server Audit object that targets a local file. There are some additional options available when specifying a file target inluding the folder that the audit files will be generated in, the maximun number of rollover files, the maximum size of each file and whether to reserve the disk space for the audit file if a maximum size is specified as shown below
   
   CREATE SERVER AUDIT [ServerAuditFile]
   TO FILE 
   (  FILEPATH = N'C:\Audit\Server\'
      ,MAXSIZE = 100 MB
      ,MAX_ROLLOVER_FILES = 2147483647
      ,RESERVE_DISK_SPACE = OFF
   )
   WITH
   (  QUEUE_DELAY = 2000
      ,ON_FAILURE = CONTINUE
   )  
   
   In this example we are specifying a target folder of C:\Audit\Server, a maximum individual file size of 100MB, an unlimited number of rollover files and not to reserve disk space when creating the audit files. Note that you can also specify a UNC path for the FILEPATH if you want to write to a remote share as long as the SQL Service account has permissions to the share. Because of the increased latency due to the network you may need to increase the QUEUE_DELAY parameter to avoid impacting the instance. Also with a remote target it may not be wise to specify ON_FAILURE = SHUTDOWN (you can increase the availability of the share by creating it on a cluster)
   
4. There is one more option available when creating a Server Audit and that is AUDIT_GUID. All Server Audits have an audit_guid (which you can find by querying sys.server_audits) . When you create a Database Audit Specification (covered in Part III) you associate it with a Server Audit. If you restore or attach the database to another instance for example when configuring database mirroring, the Database Audit Specification becomes orphaned because there is no Server Audit on the new instance with a matching audit_guid. Thus by providing the option to specify the audit_guid, you can create the required Server Audit on the new instance. For a Server Audit that uses a File target in a database mirroring environment you will probably want to use a file share rather than a local file and will need to make sure poth the principal and mirror SQL service accounts have access to the share.

So as we have seen from these examples, it is extremely straightforward to create Server Audit objects in SQL Server 2008 using TSQL or SSMS. The Security Event Log target is a good choice to ensure that audit records cannot be tampered with and should also integrate well with ACS (Audit Collection Services) in SCOM 2007 (System Center Operations Manager) or other security log based audit collection systems. Again, as with a number of the new features in SQL Server 2008 it will be interesting to see the performance impact (if any) of the various audit configurations and I'm sure some best practice white papers will be available at some point post RTM.

All the code for the examples in this post are in the attached zip file. In Part II we'll cover how to create Server Audit Specifications and relate them to the Server Audits we created.