How to write SQL server audit events in Windows security logs

There are two primary ways of logging SQL audit events. One way is to write the SQL server's logs to a .sqlaudit file and view it on SQL Log Viewer. Another way is to write them to Windows Security log.

Writing SQL events as Windows Security logs has the added advantage of being tamper-proof.

This article explains how to write SQL Server Audit Events to Windows Security log.

Step 1: Configure the audit object access setting in Windows using auditpol

To ensure that the necessary SQL server events are being captured and logged, you need to configure the audit object access setting in Windows. This setting can be configured on auditpol.exe, using the below steps.

• On the SQL Server, open Command Prompt with administrator permissions.
• Execute the following statement:
auditpol /set /subcategory:"application generated" /success:enable /failure:enable
• Auditing from SQL server is enabled. Close the Command Prompt.

Step 2: Grant the generate security audits permission using secpol

The generate security audits permission should be granted to the account running the SQL Server service. This enables the account to write the captured SQL server events to the Windows Security Log.

The steps to grant this permission using secpol are as follows:

• On the SQL server, open Run and type secpol.msc.
• In the Local Security Policy dialog box that opens, navigate to Security Settings >> Local Policies >> User Rights Assignment.
• Double-click Generate security audits. The setting window opens.
• On the Local Security Setting tab, click Add User or Group.
• In the Select Users, Computers, or Groups dialog box that opens, type the name of the user account in the 'domainname\username' format. You may also click Advanced and search for the account.
• Click OK and close the Local Security Policy dialog box.
• Restart SQL Server to enable this setting.

Once done, your SQL Server Audit events are written to Windows Security Log. You can create the required server audit objects, server audit specification, and database audit specification on your SQL server.