Il est important d’avoir un environnement sécurisé, en appliquant le principe du moindre privilège dans vos serveurs et bases de données, mais cela peut être difficile à réaliser sur de grands environnements, où beaucoup de personnes ont des privilèges élevés ou plusieurs personnes ont des autorisations pour effectuer des tâches sur vos données ou votre schéma.
Comme mesure supplémentaire, un journal d’audit devrait être mis en œuvre dans ces scénarios et les revoir souvent.
SQL Server fournit la fonctionnalité pour mettre en œuvre des journaux d’audit au niveau du serveur et de la base de données. À partir de SQL Server 2008, toutes les éditions de SQL Server prennent en charge les audits au niveau du serveur. Les audits au niveau des bases de données sont pris en charge à partir de SQL Server 2016 (13.x) SP1. Avant cela, l’audit au niveau de la base de données était limité aux éditions Enterprise, Developer et Evaluation.
Sur ce post, je vais vous montrer comment configurer et visualiser les journaux d’audit :
Mise en place du journal d’audit
Sous sécurité, allez dans Audits et sélectionnez Nouvel audit…
Spécifiez un nom pour l’audit et sélectionnez une destination d’audit (je préfère personnellement un emplacement de fichier).
Sélectionnez les paramètres souhaités et cliquez sur OK.
L’audit est maintenant créé, pour qu’il fonctionne il suffit d’activer l’audit.
NOTE : avant d’apporter des modifications à la configuration de l’audit, vous devrez d’abord le désactiver.
Mise en place de l’audit au niveau du serveur
Nous avons créé notre emplacement d’audit, maintenant nous allons procéder à la création des actions que nous voulons auditer au niveau du serveur.
Sous le dossier Sécurité, localisez les spécifications d’audit de serveur, faites un clic droit et sélectionnez Nouvelle spécification d’audit de serveur….
Dans la fenêtre suivante, spécifiez un nom pour l’audit (vous pouvez en avoir plusieurs) et sélectionnez l’audit créé à l’étape précédente.
Choisissez l’action ou les actions à auditer. Pour vérifier ce qui est inclus sur chaque action, veuillez lire ici la documentation de Microsoft.
Une fois que vous avez terminé, cliquez sur OK pour créer la spécification d’audit.
Pour cet exemple, j’ai juste inclus l’action DBCC à auditer (comme nous le verrons plus tard dans ce post)
La spécification d’audit est également créée désactivée, pour la faire fonctionner, activez-la comme indiqué dans l’image.
NOTE : avant d’apporter des modifications à la spécification d’audit, vous devrez d’abord la désactiver.
Mise en place de l’audit de niveau base de données
Le processus est relativement le même pour créer une spécification d’audit de niveau base de données, la principale différence est que l’option est située sous le dossier Sécurité sur chaque base de données.
Cliquez avec le bouton droit sur le dossier Spécification d’audit de base de données, puis Nouvelle spécification d’audit de base de données…
Sélectionnez le nom de l’audit de base de données et la destination de l’audit (la même créée à la première étape pour cet exemple).
Une autre des différences clés pour les Audits de base de données est que nous devons sélectionner l’objet ou le schéma que nous voulons vérifier.
Sélectionnez les actions à auditer (vous pouvez voir la description de chacune d’entre elles ici), puis cliquez sur Ok.
Pour cet exemple, je vais auditer la commande SELECT sur la table nommée Cours dans un échantillon de base de données École.
Après la création de la spécification d’audit, vous devez l’activer pour qu’elle s’exécute.
NOTE : avant d’apporter des modifications à la configuration d’audit, vous devrez d’abord la désactiver.
Examiner les journaux d’audit
Avoir les audits configurés et en cours d’exécution n’est que la moitié du travail, cela ne vaudra rien si vous n’examinez pas les journaux de manière proactive souvent.
Il existe 2 options pour examiner les journaux, en utilisant SSMS et T-SQL
Pour cet exemple, j’ai exécuté quelques phrases pour déclencher les audits du serveur et de la base de données configurés ci-dessus afin que nous puissions voir quelques données.
SSMS
Pour vérifier les journaux d’événements en utilisant Management Studio, il suffit de sélectionner l’audit créé, de faire un clic droit dessus et de sélectionner l’option View Audit Logs
Le journal d’audit s’affichera dans un type de journal SQL Server, et vous pouvez voir certains des exemples que j’ai déclenchés
T-SQL
Pour utiliser cette option, vous utilisez le DMF sys.fn_get_audit_file, en spécifiant l’emplacement du fichier, et pour le nom du fichier, utilisez *.sqlaudit pour inclure tous les fichiers.
C’est la requête que j’utilise pour cet exemple:
SELECT event_time,action_id,statement,database_name,server_principal_name
FROM sys.fn_get_audit_file ('C:\temp\TestAudit_*.sqlaudit',default,default) ;
La sortie de la requête ressemble à ceci :
J’ai marqué en jaune les actions qui ont été capturées dans la spécification de la base de données.
Avec cette info vous êtes bon pour aller configurer vos propres audits pour vos serveurs critiques de mission et les revoir souvent, aussi vous pourriez mettre en œuvre une logique d’alerte ou de rapport automatique pour être notifié sur une activité suspecte.