SQL Server Audit could not write to the Security Log

Description: The error occurs when the SQL Server Audit redirects to Windows Event Security log. The obvious reason is that the SQL Service Account for the Audit Log Event may not have the appropriate permissions to write an event to Windows Security Event log.  When we try to enable the Audit Log Event, we receive the following error in the SQL Server Error log:

Error message:

Audit: Server Audit: 65536, State changed from: TARGET_CREATION_FAILED to: SHUTTING_DOWN
Audit: Server Audit: 65536 Session has been closed
Audit: Server Audit: 65536, Initialized and Assigned State: START_FAILED
Audit: Server Audit: 65536, State changed from: START_FAILED to: TARGET_CREATION_FAILED
Error: 33204, Severity: 17, State: 1.
SQL Server Audit could not write to the security log.
Error: 33208, Severity: 17, State: 1.
SQL Server Audit failed to access the security log. Make sure that the SQL service account has the required permissions to access the security log.
Audit: Server Audit: 65536, Initialized and Assigned State: TARGET_CREATION_FAILED

What needs to be configured?

1. SQL Server Audit registry value, change from 0 to 1, HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security\MSSQL$<InstanceName>$Audit\EventSourceFlags.
2. Configure the application generated setting to allow SQL Server to audit object access (auditpol.exe).
3. Grant Generate Security Audits permission to the SQL Server service account (DOMAIN\ACCOUNT) in local security policies (secpol.msc, User Rights Assignment).

Steps to configure: There are three configuration steps we need to follow before we restart the SQL Server Service so these changes can take effect:

Step#1: Configure Registry key for SQL Server Audit

SQL Server Audit registry value: Run regedit.exe and then change the following registry key from 0 to 1, to enable writing to the SQL Server Security log by multiple Server Audit Events:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security\MSSQL$<InstanceName>$Audit\EventSourceFlags

Step#2: Configure the audit object access setting

This step can be done either by executing the command line tool auditpol.exe or by following the secpol.msc MSDN article referenced (can be found in the reference section):

Using command line tool: Use an elevated command prompt to execute the following command:

auditpol /set /subcategory:"application generated" /success:enable /failure:enable 

Using secpol.msc: From an elevated command prompt, run secpol.msc and then change the following settings:

 

 

Step#3: Grant the generate security audits permission

Use the elevated command prompt to run secpol.msc and navigate to User Rights Assignment.

Find “generate security audits” from the right pane and add the SQL Server Service Account.

 

Step#4: Restart the SQL Server Service

Step#5: All pre-requisite configurations have been completed, you can enable the Audit Event capturing process either through the SSMS or using T-SQL such as following:

ALTER SERVER AUDIT [Audit-Login2020] WITH (STATE = OFF)
GO
ALTER SERVER AUDIT [Audit-Login2020] WITH (STATE = ON)
GO 

 

References:

• Write SQL Server Audit Events to the Security Log: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/write-sql-server-audit-events-to-the-security-log?view=sql-server-ver15
• FIX: SQL Server Audit Events fail to write to the Security log: https://support.microsoft.com/en-us/help/4052136/fix-sql-server-audit-events-fail-to-write-to-the-security-log