Activer l'audit SQL Server

Par Arian Papillon le dimanche 8 août 2021, 12:09 - Lien permanent

Par les temps qui courent, la sécurité des accès à nos serveurs devient une forte préoccupation. On veut donc savoir qui tente de se connecter,  échoue ou réussit.

Une configuration courante consiste à enregistrer les échecs de connexion dans l'errorlog de SQL Server. Ce qui est dommage, c'est la pollution que cela génère dans ce journal, encore bien pire si on veut aussi enregistrer les connexions réussies.

Et si nous activions l'audit de SQL Server ? Plutôt que d'enregistrer tout cela dans l'errorlog, on oublie qu'il existe cette fonctionnalité bien plus puissante, qu'on pourrait presque activer par défaut sur tout serveur un peu sensible.

Cet article vous permet de mettre en place cette configuration.

C'est un simple script, à dérouler en post-configuration :

  • Ne plus journaliser dans l'errorlog l'activité de connexion
  • Configurer l'audit à la place, en y ajoutant un ensemble d'évènement de sécurité qu'il est important de tracer : changements dans les logins, dans les mots de passe, dans les rôles ou les permissions...

Le journal d'audit peut être consulté depuis SSMS ou peut être importé dans une table pour en faire des listings ou rapports avec la fonction sys.fn_get_audit_file.

DECLARE @sql NVARCHAR(MAX);
DECLARE @errorlogpath NVARCHAR(1000);
SELECT @errorlogpath = LEFT(path, LEN(path) - 1)
FROM sys.dm_os_server_diagnostics_log_configurations;
-- Create server audit
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = 'Security-Audit')
    PRINT 'Security Audit already existing, not modified';
ELSE
BEGIN
    SET @sql
        = N'CREATE SERVER AUDIT [Security-Audit]
TO FILE
(    FILEPATH = ''' + @errorlogpath
          + N'''
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 10
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);';

    EXEC (@sql);

    -- Add server audit specifications
    SET @sql
        = N'
    CREATE SERVER AUDIT SPECIFICATION [Security-Audit-Specification]
    FOR SERVER AUDIT [Security-Audit]
--        ADD (SUCCESSFUL_LOGIN_GROUP),
       ADD (FAILED_LOGIN_GROUP)
      , ADD (AUDIT_CHANGE_GROUP)
      , ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
      , ADD (LOGIN_CHANGE_PASSWORD_GROUP)
      , ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
      , ADD (SERVER_PERMISSION_CHANGE_GROUP);
    -- Audit activation
    ALTER SERVER AUDIT [Security-Audit]
    WITH (STATE = ON);
    ALTER SERVER AUDIT SPECIFICATION [Security-Audit-Specification]
    WITH (STATE = ON);';

    EXEC (@sql);
END;
GO

Bon audit !