How to send alert on SQL Shutdown due to Audit Failure

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

How to send alert on SQL Shutdown due to Audit Failure

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply