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 =

               
    THEN 'Y' ELSE 'N' END AS SpecificationEnabled
    ,            
          
    d.AuditActions
    ,
          
    a.name AS ServerAudit
    ,
          
    a.type_desc AS ServerAuditType
    ,
          
    CASE WHEN a.is_state_enabled =

               
    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 THEN 
    'N' 
               
    WHEN f.reserve_disk_space 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('')), 11''AS 
    AuditActions
               
    FROM sys.server_audit_specification_details AS 
    d1
               
    GROUP BY server_specification_idAS 
    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

Attachment: AuditingPartII.zip
Published Friday, June 27, 2008 6:10 PM by sqldbatips
Filed under: , ,

Comments

No Comments