SQL Server Audit Logging

SQL Server allows you to output audit events in 2 different formats: the Windows event log and a binary file format readable through a stored procedure.

At first glance it seems an obvious choice to select the Windows event log since most log management and SIEM solutions have built-in support for Windows event log collection. But there are at least 3 reasons why I don't recommend that:

  1. Performance - For best performance, Microsoft recommends outputting audit logs in the alternative binary format because this replaces the overhead of the Windows event API with very fast file I/O. This is especially important on busy and loaded production databases.
  2. Security - Commonly accepted security best practice mandates that security logs be moved off the system where they are generated as quickly as possible to prevent intruders or even malicious administrators from tampering with the audit trail. SQL Server's binary audit log format allows you to write audit logs to a shared folder on a completely different server; this is the absolute fastest and most secure way of protecting SQL audit logs.
  3. Stability - Any organization serious about audit logging for security and compliance, requires their central log management system to collect, alert, report and archive their audit logs - including those from SQL Server. But to efficiently collect large Windows event logs from busy systems I invariably see customers ultimately installing a local agent to collect and forward events - even with so-called agent-optional log management solutions. However, database administrators tend to be extremely resistant to installing additional software on their servers because of understandable concerns for stability. On the other hand, SQL's binary audit log option allows you to write audit logs to a share folder on some other server where it can subsequently be read and processed by a collector like LOGbinder for SQL Server with zero impact to the database server.

For best performance, security and stability the recommended choice for SQL audit logs is the binary file format and optionally to specify a shared folder on a different server as the destination rather than a location on the SQL Server's local filesystem.

How though do you read these binary audit files? The answer is "from inside SQL Server using sys.fn.get_audit_file()." This system stored procedure returns a result set from which you can use standard SQL Select queries. Getting data from sys.fn.get_audit_file() into your SIEM or log management solution though is another story. You still have to understand the cryptic data, translate it and output it in a consumable log format and this is why I designed LOGbinder for SQL Server.

Next:

 

Upcoming Webinars
Additional Resources