Configuring SQL Server Audit Logging with Audit Objects

To configure an audit log destination in SQL Server you must create an Audit object (actually called simply an Audit). Don't confuse Audits with Audit Specifications like a Server Audit Specification or a Database Audit Specification. Audit specifications define which actions are audited on the SQL Server instance or within a database. Audits define the destination log where audit events will be written.

The Audit allows you to send events to the local Windows Security Log, Application Log or to a binary file which can be on the local file system or in shared folder on some other server. Learn why I recommend using binary log files.

Security or Application Log

When you configure a SQL Server Audit to send events to the Application or Security log, there are no other options to be set because all log management options like maximum log size and overwrite behavior are handled by Windows according to the settings on the respective event log.

We recommend binary audit logs as the best option for performance, stability and security, and if you plan to use LOGbinder for SQL Server to connect SQL audit logs to your SIEM or log management solution. But if you plan to use the event log anyway, the Security log is preferable the Application log because the Security log has tighter access controls than the Application log to discourage log tampering. Note however that to send events to the Security log the Windows account which the SQL Server service is running as will need the "Generate security audits" right and you will need to enable the Application Generated audit subcategory. Both of these options are found in Group Policy.

Binary Log Files

If you plan to use LOGbinder for SQL Server to connect SQL Server audit logs to your SIEM/log management solution, use this option so that LOGbinder for SQL Server can find and process your log files.

Regardless, we recommend using this option as illustrated in the above screen print. To understand the performance, security and stability reasons for this recommendation please click here.

To send audit logs to a shared folder on a different server, specify the UNC path and make sure the Windows user account which the SQL Server service is running as has permissions to create files in that folder. We strongly recommend that if you have multiple SQL Servers sending audit logs to the same server that you use a different shared folder as the destination for each SQL server to avoid comingling files within the same folder and the possible confusion and file name collisions that could result.

With the binary audit file option you can choose to have SQL Server manage the audit logs in terms of file size, deleting old files and whether to reserve disk space.

Next:

 

Upcoming Webinars
    Additional Resources