SQL Server Audit failed to access the security log

**janlouk** · 03/12/2020, 11h42

Bonjour à tous,

L'équipe de sécurité de mon client, souhaite mettre un audit sur tous nos serveurs SQL. Sur SQL2012 et SQL2016, pour les éditions Developer, Standard et Enterprise.

Pour le moment, je fais sur des versions Developer.

Voici le script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE [master]
GO
 
/****** Object:  Audit [Audit_Login_DMG]    Script Date: 28/09/2020 13:15:13 ******/
CREATE SERVER AUDIT [Audit_Login_DMG]
TO SECURITY_LOG
WITH
(      QUEUE_DELAY = 1000
       ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_Login_DMG] WITH (STATE = ON)
GO
 
USE [master]
GO
 
CREATE SERVER AUDIT SPECIFICATION [ServerLoginAuditSpecification_DMG]
FOR SERVER AUDIT [Audit_Login_DMG]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO

Malheureusement j'obtiens ce message :

Msg 33222, Level 16, State 1, Line 11
Audit 'Audit_Login_DMG' failed to start. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

Quand je vais dans l'errorlog, je vois ceci :

12/02/2020 11:15:48,spid455,Unknown,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.
12/02/2020 11:15:48,spid455,Unknown,Error: 33208<c/> Severity: 17<c/> State: 1.
12/02/2020 11:15:48,spid455,Unknown,SQL Server Audit could not write to the security log.

Pour que cela fonctionne, il faut faire celà :

SQL Server should run with a service account which will need to have full control permissions to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security.

SQL Server to write to the security log, it will require the service account to have permission to generate security audits either directly via secpol.msc (Generate security audits) or via Generate Security Audit GPO.
It will be needed to change the auditpolicy for the server directly on the server (auditpol /set /subcategory:"application generated" /success:enable /failure:enable), via GPO.

Le sysadmin qui s'occupe de donner les droits, à fait ceci suite à ces articles :

I stumbled on this MS article: https://docs.microsoft.com/en-us/sql...l-server-ver15
And another non MS article but better explained: http://sqltouch.blogspot.com/2020/10...-write-to.html

1. Provide full permission for the SQL Server service account to the registry hive HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security.
2. Configure the audit object access setting in Windows using auditpol
3. Grant the generate security audits permission to an account using secpol:
Our hardening policies only allow the the built-in accounts LOCAL an NETWORK SERVICE accounts to have the privilege to write to the windows security events log:

Pourtant je continue à avoir le message d'erreur, malgré que je les ai effacé et réexécuté le script.

La seule chose depuis leurs changements, je n'ai pas redémarré le serveur.
Est-ce cela ou vous avez une autre idée ?

Merci,
Jean-Luc