How to send alert on SQL Shutdown due to Audit Failure

Question

Looking for a way to implement this STIG. What method should I use to monitor SQL Server, when it is shutdown due to "Shutdown on Audit Failure"?

SQL Server must provide an immediate real-time alert to appropriate support staff of all audit failure events requiring real-time alerts.

I have found Event ID: 33219

Parameters:

• Cannot store a password, so difficult to use "Windows Scheduled Task"
• On a domain, remember not able to store a password
• PowerShell script:

 $recipients = "<[email protected]>,<[email protected]>"
 Send-MailMessage -To $recipients -Subject "SQL Audit Failure" -Body "SQL has shutdown due to Audit Failure." -SmtpServer NameOrIPAddress -From [email protected] -Priority High

Answer 1

User assigned to Task in Task Scheduler must be assigned the "User Rights Assignment" "Log on as a batch job"

1. Select the radio button: "Run whether user is logged on or not"
2. Check the box: "Do not store password. The task will only have access to local computer resources."
3. Click Ok.
4. Enter password for account to run the script.
5. Click Ok.

Answer 2

You might get more coverage satisfying the stig by also trapping the below events, where you *should already have alert notices configured for severity 17 at min.

EventId Severity Description 33240 16 No A failure occurred during initializing of an Audit. See the errorlog for details. 33086 10 No SQL Server Audit failed to record %ls action.

33203 17 No SQL Server Audit could not write to the event log.

33204 17 No SQL Server Audit could not write to the security log.

33207 17 No SQL Server Audit failed to access the event log. Make sure that the SQL service account has the required permissions to the access the event log.

33208 17 No SQL Server Audit failed to access the security log. Make sure that the SQL service account has the required permissions to access the security log.

33215 10 No One or more audits failed to start. Refer to previous errors in the error log to identify the cause, and correct the problems associated with each error.

33240 16 No A failure occurred during initializing of an Audit. See the errorlog for details.

Or 24052 (audit failure) as a wrapper.