Ayad Shammout, Denny Lee

Project Description

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 isstateenabled column in sys.serveraudits will remain 1, meaning true, but the audit status in sys.dmserverauditstatus 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, the Release History section above links you do the updated sqlauditcentral codeplex project which includes the Restart SQL Audit Policy and Job. 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.
  • 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.

Last edited Feb 8, 2012 at 1:53 AM by ashammout, version 3