locked
SQL Audits stopped working RRS feed

  • Question

  • We recently noticed that SQL auditing had stopped working on 3 of 4 servers that we had turned on audits.  When I would view the audit, it contained entries related to the startup of the audit but not anything about items the audit should contain. We are writing the audits to a file, parsing the information and storing it in another database using a stored proc.

    We have 3 separate audits.  Once for failed logons, one for instance permission changes and another for database permissions changes.  None of these audits were generating entries when I simulated failed logon or if I changed permissions.  I had a failed logon audit in the logs on 8/21. We patched the instances with July's SQL security update and applied August Windows patches on 8/23.  I didn't see anything as I did an internet search referencing patches breaking the audits.

    The fix was easy, I had to remove and re-enable the audit and audit specification for each of the three audit types.  I'm not clear either why one of the 4 servers continues to work.  All the OSs are 2008R2.  Two of the instances are 2012 STD with SP2 and two of the instances are 2008 R2 ENT w/ SP3.  Audits kept working on one of the SQL 2012 boxes.

    Monday, August 31, 2015 8:28 PM

All replies

  • how do yo configure the audit? Try to repair your sql
    Wednesday, September 2, 2015 12:54 PM
  • I use a Windows PowerShell script to configure the audits.  If SQL needed repair, I would think we'd see other issues with the instance.
    Wednesday, September 2, 2015 1:32 PM