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.
In the following example we will create a couple of Server Audit Specifications using the Server Audits we created in Part I
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]
WITH (STATE = ON)
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',
ALTER login AuditLoginDemo WITH
DEFAULT_DATABASE = model,
DEFAULT_LANGUAGE = British
DROP login AuditLoginDemo
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
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 SpecificationCREATE SERVER AUDIT SPECIFICATION [ServerOperationAndState]
FOR SERVER AUDIT [ServerAuditFile]
WITH (STATE = ON)
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
EXEC sp_configure 'clr enabled',1
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
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,
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