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:
- 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?
- 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?
- 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?)
- Buy auditing software and just be done with it.
Thoughts? Let me know if this is too broad.