SQL Server Audit Log Reporting

There's no built-in audit log reporting in SQL Server. (See Recommended Reports for SQL Server via LOGbinder for SQL Server.)

However you may consider creating your own reports with SQL Reporting Services if you configure your SQL Audit to use binary log files instead of the Windows event log. (More reasons to use binary log files.)

To access a SQL audit log you must use the fn_get_audit_file() system stored procedure which opens the audit log file and returns a result set based upon it. You can perform SQL Selects against the audit log result set to find the events of interest to you. Below is an example:

Before attempting to building reports by querying SQL audit logs consider the following issues:

  • Performance: - There are no indexes available on SQL audit log files.
  • Security - Commonly accepted security best practice mandates that audit logs be moved from the system where they are created to a different system to protect the integrity of the audit trail against intruders or malicious admins. Note that you can access SQL audit log files from a different SQL server and while only premium editions of SQL server can generate audit logs, all versions of SQL server can read them.
  • Learning Curve - As you can see from the screen print above, the SQL audit log is very cryptic and requires a strong knowledge of SQL and the SQL auditing system in particular to decode and interpret SQL audit events.

Instead of trying to manage SQL audit logs manually and build reports based on them, I believe SQL audit logs belong in your SIEM/log management solution where you can take advantage of centralized collection, secure archival and built-in alerting and reporting - not to mention correlation.

You might consider sending SQL audit logs directly to the Windows event log since your log management/SIEM can probably collect them from there. But remember that all of the hundreds of different operations that can be performed in SQL Server are written to just one event ID with the same message format for all actions.

To allow SQL audit logs to be managed by your existing log management/SEIM and to obtain easy to understand, clearly formulated audit events I designed LOGbinder for SQL Server.

 

Upcoming Webinars
    Additional Resources