sqldbatips.com blog

New in SQL 2008: Server and Database Auditing Part II

In this post I'll cover how to create a Server Audit Specification to audit Instance level events. This post is part of a series so if you missed Part I you can find it here

Server Audit Specification

Server Audit Specifications define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 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 (e.g. disabling or altering audit objects) are automatically audited. There are a large number of audit action groups, to find details on all of them check Books Online.

Example Walkthrough

In the following example we will create a couple of Server Audit Specifications using the Server Audits we created in Part I

1. For the first example we'll create a Server Audit Specification to audit changes to logins using the SERVER_PRINCIPAL_CHANGE_GROUP and use the ApplicationLog Server Audit. If you don't specify the WITH clause then the Server Audit Specification will be created in a disabled state.
   
   CREATE SERVER AUDIT SPECIFICATION [AuditLoginChanges]
   FOR SERVER AUDIT [ApplicationLog]
   ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
   WITH (STATE = ON)  
   
2. 
   
   Now lets generate a couple of events that will be audited. We will create a login, alter it and then drop it.
   
   -- 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
   


3. To view the audit events that are written to the Application Log we can either use Windows Event Viewer or SQL Server Management Studio. When viewing events using Event Viewer it is 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. To view the events in SSMS, expand the Security top level node, expand the Audits node, right click the Application Log Server Audit and choose View Audit Logs as shown below.
   
   
   
   This launches the SSMS Log Viewer which enables you to easily review audit events written to any of the available Server Audit targets
   
   
   


4. 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, expand the Security top level node, right click the Server Audit Specifications node and choose New Server Audit Specification. This will open the Create Server Audit Specification dialog as shown below. You need to supply a name and select the Server Audit you want to use. Choose ServerAuditFile from the drop down list. You can then add one or more audit action groups as shown below. Click OK to create the Server Audit Specification.
   
   
   
   As with all the audit objects, the Server Audit Specification will be created in a disabled state. To enable it using SSMS, right click on the Server Audit Specification and choose Enable Server Audit Specification as shown below
   
   
   
   Here is the equivalent TSQL command for the actions above which demonstrates how to add multiple audit action groups to a Server Audit Specification
   
   CREATE SERVER AUDIT SPECIFICATION [ServerOperationAndState]
   FOR SERVER AUDIT [ServerAuditFile]
   ADD (SERVER_OPERATION_GROUP),
   ADD (SERVER_STATE_CHANGE_GROUP)
   WITH (STATE = ON)
   


5. Now lets generate some audit events, in this case we will use sp_configure to modify some configuration settings to trigger events in the SERVER_OPERATION_GROUP action group
   
   EXEC sp_configure 'show advanced options',1
   RECONFIGURE
   GO
   EXEC sp_configure 'clr enabled',1
   RECONFIGURE
   GO  
   


6. In order to view the audit events we could use the SSMS Log viewer as in Step 3 above but since the ServerAuditFile Server Audit has a file target, we can also use the new function fn_get_audit_file to view the contents of the file from TSQL as shown below. First we query the sys.server_file_audits catalog view to get the audit folder and then pass that to the fn_get_audit_file function.
   
   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  
   


7. There are a number of new catalog views and DMV's that allow us to query the new audit objects and these are show below as well as a query that will list all Server Audit Specifcations, the audit action groups defined and the asoosciated Server Audit details. For details of the individual views see Books Online.
   
   -- new catalog views and DMV's
   SELECT * FROM sys.server_audits
   SELECT * FROM sys.server_file_audits
   SELECT * FROM sys.dm_server_audit_status
   SELECT * FROM sys.server_audit_specifications
   SELECT * FROM sys.server_audit_specification_details
   
   -- query to list all server audit specifications with details of
   -- the audited action groups, server audits and audit files (if applicable)  
   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

All the sample code is attached to this post. In Part III I'll examine Database Audit Specifications