SQL server Audit Failure to Security log

Question

SQL server Audit Failure to Security log

Prati 21

2021-02-19 12:01:54.750 spid137 Audit: Server Audit: 65542, Initialized and Assigned State: START_FAILED
2021-02-19 12:01:54.750 spid137 Audit: Server Audit: 65542, State changed from: START_FAILED to: RUNTIME_FAILED

2021-02-19 12:01:54.750 spid137 Error: 33204, Severity: 17, State: 1.
2021-02-19 12:01:54.750 spid137 SQL Server Audit could not write to the security log.
2021-02-19 12:01:54.750 spid137 Audit: Server Audit: 65542, Initialized and Assigned State: RUNTIME_FAILED

I am getting above error when setting up a database audit on a Always on database on SQL server 2017 ( CU 17) .
All the permissions as mentioned in the article exist .
https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/write-sql-server-audit-events-to-the-security-log?view=sql-server-ver15

Also , another server level audit is able to write to the security log . But this one is generating a RUNTIME_FAILED message.

Is there anything else that is missing ?

Abimbola Adeniran 106 Reputation points

2024-01-19T22:31:06.99+00:00

Hello all, I need some help. I am seeing this issue for “SqlThreatDetection_Audit” SQL Server audit on SQL Server instances running SQL Server 2016 and older (2014,2012 etc) in my client environment. They didn’t use to have these issues before until servers rebooted a few days ago and I have tried all the steps highlighted in the main thread and comments from Cris above. The service account is an AD account and I have confirmed it’s an admin on the server with perms on the security fold in windows registry, the key flag changed to 1, Local security policy changed to grant audit access to the service account and allowing success/failure auditing. I have also granted sysadmin to the service account as a server principal on SQL server with the required server level and database level perms. Not sure what else is required here. Please help urgently.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2024-01-19T22:35:29.89+00:00

House rules: Don't piggyback on old questions but ask a new Question, describing your problem from start to end.

Answer accepted by question author

0 additional answers

Your answer

Abimbola Adeniran 106 Reputation points

2024-01-19T22:31:06.99+00:00

Hello all, I need some help. I am seeing this issue for “SqlThreatDetection_Audit” SQL Server audit on SQL Server instances running SQL Server 2016 and older (2014,2012 etc) in my client environment. They didn’t use to have these issues before until servers rebooted a few days ago and I have tried all the steps highlighted in the main thread and comments from Cris above. The service account is an AD account and I have confirmed it’s an admin on the server with perms on the security fold in windows registry, the key flag changed to 1, Local security policy changed to grant audit access to the service account and allowing success/failure auditing. I have also granted sysadmin to the service account as a server principal on SQL server with the required server level and database level perms. Not sure what else is required here. Please help urgently.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2024-01-19T22:35:29.89+00:00

House rules: Don't piggyback on old questions but ask a new Question, describing your problem from start to end.

Answer 1

Cris Zhan-MSFT 6,676

Hi,

can you try the Workaround in this kb article. 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

Also have a look on this blog.
http://sqltouch.blogspot.com/2020/10/sql-server-audit-could-not-write-to.html

Important: Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend that you back up any valued data on the computer.

Prati 21 Reputation points

2021-02-27T16:35:05.807+00:00

Changing the registry key to 1 solved it .

Share via

SQL server Audit Failure to Security log

0 additional answers

Your answer