La fonctionnalité d’audit proposée par le moteur SQL reste assez peu utilisée, bien souvent par ignorance. L’idée de ce post est de présenter l’implémentation de l’audit et les deux / trois points d’attention à avoir lors de son déploiement. A noter que depuis le SP1 de SQL 2016, SQL Server Audit est disponible dans toutes les éditions du moteur.

Création d’un nouvel audit : l’objet Audit

La création d’un nouvel audit se fait à l’aide de Management Studio ou à l’aide de l’instruction T-SQL  CREATE SERVER AUDIT :

CREATE SERVER AUDIT [Audit-20171214-094111]
TO FILE
(        FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL14.WWI_2017\MSSQL\Log\'
         ,MAXSIZE = 0 MB
         ,MAX_ROLLOVER_FILES = 2147483647
         ,RESERVE_DISK_SPACE = OFF
)
WITH
(        QUEUE_DELAY = 1000
         ,ON_FAILURE = CONTINUE
         ,AUDIT_GUID = 'b18d5f98-3259-484e-b20e-48b87279dc17'
)
ALTER SERVER AUDIT [Audit-20171214-094111] WITH (STATE = OFF)
GO

Comme on peut le voir sur l’écran suivant, il est possible de configurer plusieurs audits pour une instance donnée :

C’est au moment de la création d’un Audit que l’on spécifie :

• La destination des informations d’audit collectées.
• La politique à suivre lorsque la destination des évènements d’audit ne peut plus recevoir aucun évènement

Destination des évènements d’audit collectés

Il est possible d’écrire les évènements collectés dans trois types de destinations :

• Fichier plat,
• Journal d’évènements Windows Application
• Journal d’évènements Windows Sécurité

Il est important de noter que lorsque la cible choisie est un fichier, le moteur SQL ne verrouille pas la lecture du fichier, qui est donc accessible à tous les utilisateurs disposant des droits en lecture dans le répertoire (droits NTFS). Qui plus est, chaque utilisateur disposant de l’autorisation CONTROL SERVER au niveau de l’instance SQL pourra accéder au fichier d’audit. Il convient donc de porter une attention particulière à cette gestion d’accès.
L’accès au fichier d’audit peut également se faire depuis le moteur SQL Server ce qui permet d’une part d’offrir un meilleur contrôle sur les accès, et d’autre part, de conserver un trace sur ces accès, grâce à la DMF master.sys.fn_get_audit_file qui permet de suivre les logins ayant accédé au fichier d’audit.
Mais le plus sécurisé reste malgré tout l’inscription des évènements d’audit dans le journal Windows Sécurité. Cependant, deux prérequis doivent être mis en place pour ce faire :

• Ajouter le compte de démarrage du service SQL à la policy windows Générer des audits de sécurité (depuis l’outils Secpol.msc)

• Activer l’audit des objets, à la fois en cas d’échec et en cas de réussite.

Politique à suivre lorsque la destination des évènements d’audit ne peut plus recevoir aucun évènement

Suivant la criticité de l’audit, il convient de configurer le comportement de l’audit lorsqu’il ne parvient plus à écrire dans sa destination (disque saturé, problèmes de droits, etc …). Les trois options suivantes sont possibles :

• Continuer l’audit sans que les nouveaux évènements ne soient collectés : dans ce cas, un simple message informe que l’évènement ayant déclenché l’audit ne peut être loggé. Aucun impact sur le service, l’activité se déroule normalement.
• Faire échouer (rollback) l’opération ayant générée l’évènement d’audit : lorsque cette option est activée, l’instruction ayant provoquée la tentative d’audit et d’écriture dans le journal d’audit échoue, la transaction correspondante est annulée (rollback). Le reste de l’activité continue normalement.
• Arrêter l’instance SQL : dans ce cas extrême, l’échec d’un évènement d’audit provoque l’arrêt du moteur SQL. Cette configuration est réservée à des applications très sensibles, qui ne tolèrent aucun fonctionnement sans audit.

Attention donc à bien configurer cette option, suivant la criticité de l’audit et le comportement attendu en cas d’échec.

Les spécifications d’audit

Lorsqu’il est créé, le nouvel audit est désactivé et ne collecte aucun élément. Il convient donc de créer des spécifications d’audit, ce qui revient à définir les évènements que l’on souhaite auditer. Il est possible de créer une spécification de niveau instance et une de niveau bases de données, par base de données et par Objet d’Audit.
Le détail des spécifications d’audit est décrit ici, je ne reviendrai donc pas dessus dans ce post : https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

Pour aller plus loin

Déplacement d’une base ayant des spécifications d’audit actives

Le déplacement d’une base de données contenant des spécifications d’audit peut poser problème lorsque le serveur de destination ne contient pas l’objet d’audit auquel se rattache les spécifications de la base déplacée. En effet, l’association des spécifications d’audit de la base déplacée avec l’objet d’audit (au niveau de l’instance de destination donc) se fait via le GUID de l’objet d’audit et non pas à l’aide de son nom. Ainsi, le serveur de destination peut héberger un objet d’audit portant le même nom que celui existant sur le serveur source, sans pour autant que les spécifications d’audit de la base déplacée ne puissent s’y enregistrer. Deux solutions sont possibles pour contourner ce problème :

• Soit l’on créé l’objet d’audit sur le serveur de destination avec le même GUID que sur le serveur source. Il suffit pour cela de générer le script de création de l’objet d’audit à l’aide de Management Studio, depuis le serveur source
• Soit l’on associe les spécifications d’audit de la base déplacée à l’objet d’audit existant sur la destination à l’aide de l’instruction ALTER DATABASE AUDIT SPECIFICATION

Démarrage de l’instance en échec

Si un audit est configuré pour que l’instance s’arrête en cas d’échec d’écriture des informations d’audit (option ON_FAILURE = SHUTDOWN) son redémarrage ne sera possible que lorsque la raison de l’échec aura été identifiée et corrigée (espace plein par exemple). Si l’on tente de démarrer l’instance alors que l’audit ne peut pas être initialisé, le démarrage échouera et le message d’erreur suivant apparaitra dans le journal d’évènements Windows Application :

Il est néanmoins possible de forcer le démarrage de l’instance en mode mono-utilisateur à l’aide des paramètres de démarrage -f ou -m. Un administrateur peut alors se connecter, régler le problème en définissant par exemple une nouvelle destination d’audit et redémarrer l’instance normalement.
Dans un prochain post, nous verrons comment implémenter cette fonctionnalité dans AZURE.
Bon audit !
O.