I am looking into getting some basic SQL auditing set up (for both compliance and security), for both Server and Database level specifications. I know that the specification groups should have about 90% of what I need to log for compliance requirements, but have a question about how to handle the logs after generation. I think I also need statement-level/DML logging in some instances.

The options as I see them:

  1. Generate to file(s) as .sqlaudit files, and write a SQL query/job to grab them and store/insert them into a separate "Audit" database, then utilize SSRS for front-end filtering and reporting?
  2. Send the audit events to the Windows Security/Application logs, then extract the logs with Powershell to a front-end script for filtering and reporting, probably using a simple out-gridview for filtering and such?
  3. Use Extended events since I won't actually be taking any 'action' with the Auditing function, merely capturing/logging data. I don't think the raw .xel files give me much flexibility here though, an I would need to import them back into a database for analysis. (Increased SQL Overhead?)
  4. Buy auditing software and just be done with it.

Thoughts? Let me know if this is too broad.

  • 1
    Since your question boils down to "Thoughts?" I think it is definitely too broad. I imagine the correct approach is unique to you/your environment. If you have auditing requirements, you should defer to the policy or team responsible to see which covers your bases most appropriately. – LowlyDBA - John McCall Jun 14 '19 at 18:04
  • This is me simply exploring technical solutions. "Ask your policy team" doesn't really apply (nor help), or else I wouldn't be asking for technical input in the first place. :) – user58700 Jun 14 '19 at 19:13
  • Exploration doesn't really fit the Q&A model here well. In What types of questions should I avoid asking it clarifies "If your motivation for asking the question is “I would like to participate in a discussion about ______”, then you should not be asking here. " – LowlyDBA - John McCall Jun 14 '19 at 19:22
  • I am fully aware of the Q&A page, hence the insight at the end of the post. Do you have any technical input into the conversation? If so, I would love to hear it! This is a technical forum, and I came looking for technical input. – user58700 Jun 14 '19 at 19:31
  • I'm only trying to help you avoid getting your question closed by confirming it is too broad in my view, since Stack Exchange is not a forum. Best of luck with your problem. – LowlyDBA - John McCall Jun 14 '19 at 19:40

This question may get tagged as too broad because the solution chosen depends on who's responsible for the configuration and data collection.

  • Option 1 (Audit DB with SSRS reporting) works using a combination of the sys.fn_get_audit_file function and an ETL solution (sprocs, SSIS, etc.) to get into the database.
  • Option 2 (Application/Security log with PoSh) is an option for server admins to help gather the data but what's involved in parsing the various attributes? Could the server admins help with log imports with Splunk and the like?
  • Option 3 (Extended Events) will require XQuery calls to parse out the data capture but is robust. One missing feature with XE: if your business wants to shutdown an instance if it can't audit, I believe only SQL Audit can do that, not XE.
  • Option 4 (3rd Party) would shrink-wrap any combo of the above. It may also give the option of delegating responsibility of configuration and upkeep to another business team (IT Security or Internal Audit) so that DBAs and other sysadmins are out of the loop if they are subject to audits like the developers and end-users.
| improve this answer | |
  • Thanks for the input. I might segment each of these out into their own questions. – user58700 Jun 14 '19 at 19:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.