Configure Windows Task to Alarm on Audit Trace Failure

The LogRhythm_Audit stored procedure defines the SQL Audit trace in such a way that if the audit trail becomes full (there is an error of any kind writing to the audit trace) the following events occur:

1. No more audit data is written to the audit trace file.
2. 100% of the existing audit data is retained.
3. The SQL Server instance shuts down, preventing any user from performing auditable actions.
4. The SQL Server Agent shuts down, preventing any database jobs from running.
5. SQL Server writes the notice of the audit write error to the Application Event Log on the host system.

The items above do not cover being able to send an alarm to appropriate authorized users when the audit trace stops (and therefore SQL Server stops) for any reason. This must be implemented outside LogRhythm and SQL Server as both of these systems may be unavailable due to the SQL Server shutdown. The Windows operating system on the affected LogRhythm server remains operational and it is at this level the alarm must be raised. The Windows Server 2008 R2 operating system can automatically execute tasks when a specific event is raised in the Windows Event Log. The following procedure outlines setting up this task to alarm on the SQL Server audit trace failure and SQL Server shutdown.

Perform the following to configure a Windows task to alarm on an audit trace failure and subsequent SQL Server shutdown.

On a Windows Server 2008 R2 system:

1. On the Start menu, click Administrative Tools, and then click Task Scheduler.
2. In the Actions Tab, click Create Task.
3. On the General Tab:
   1. Name the task Alarm on SQL Trace Failure.
   2. Set the task to run under an appropriate user account (SYSTEM is preferred).
   3. Set the task to Run whether user is logged in or not.
4. On the Triggers Tab:
   1. Click New.
   2. Set Begin the Task to On an event.
   3. Set the Log as Application.
   4. Set the Source as MSSQLSERVER.
   5. Set the EventID to 566.
   6. Ensure the Enabled check box is checked.
   7. Click OK.
5. On the Actions Tab:
   1. Click New.
   2. Set the Action to Send an email.

   3. Fill in the From, To and Subject lines with appropriate values.

      

      The To field can be set up on a distribution list to notify multiple users when this event occurs.

   4. Fill in the Text field with the following text:

      An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.

   5. Set the SMTP server field to an appropriate SMTP server.
6. The Conditions and Settings Tabs can be left in default configuration.

To correct the situation, an authorized user, who has access to the file system where the audit traces are stored, has to free disk space and then have SQL Server restarted. After SQL Server starts, the LogRhythm_Audit stored procedure ensures a new audit trace is started. In addition, the SQL Server Agent service requires a restart as it depends on the SQL Server service.

To reduce the probability of this happening, it is important to keep adequate disk space available and to perform regular maintenance of the system. Archiving audit trace data helps keep disk space available.