Authors: Ayad Shammout, Denny Lee, Jack Richins

As noted within the Reaching Compliance: SQL Server 2008 Compliance Guide (you can also check the sqlauditcentral codeplex project), an easier way to view and manage all of the audit logs within your SQL Server environment is to place all of the audit logs in one central location. As per the guide, you can then use a SSIS package to import in all of these logs files into a separate SQL database where you can then generate reports to view all of the audits within your entire SQl Server environment.

The problem that we recently discovered is that if SQLAudit loses connectivity to the folder it places the audit files, provided that you did not tell SQL Server to shutdown if it cannot write an audit: 

• The audit’s is_state_enabled column in sys.server_audits will remain 1, meaning true, but the audit status in sys.dm_server_audit_status will be “RUNTIME_FAILED” and no events will be written to the audit log. 
• Even when connectivity to the folder has returned, the audit will remain in the “RUNTIME_FAILED” state - meaning it still tries to write to the log but will always fail as it is using an old and now invalid handle, or reference, to the audit log from before the connectivity loss. Currently the only way to get the audit to create a new valid handle for the audit log is to stop and restart the audit – which will create a new audit file.
   

There is a bug assigned to this issue and will be resolved in the future. But for us whom are working with SQL Audit right now, to work around this problem, please go to the sqlauditNetworkConnectivity Codeplex project where you can download the full Centralized Audit Framework project.  Within this project is the Restart SQL Audit Policy and Job folder. This folder contains three pieces of source code:

• Server Audit Status (Started).xml - Import this on-schedule policy into your server's Policy-Based Management as it will determine if the audit is enabled and able to write to the file system.
• Create Audit Job.sql - This is a SQL Server Audit job that will execute the noted policy; you will need to schedule this yourself
• Create Audit Alert.sql - This is a SQL Server Audit job that will create an audit alert.

Together these three source components will (whenever manually executed or scheduled) determine if all of the audits on your server are able to write to the folder. If they are not, they will send out an alert as well as stop and restart the audit job re-initializing it so that way the audits will start writing again.