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: