LLI Wiki — My Digital Knowledge Base

SQL Server : alertes Agent et audit de sécurité vers le journal Application

Alertes SQL Server Agent sur erreurs critiques et gravités 16 à 25, notifications et audit serveur vers le journal Application.

Ce mémo décrit un jeu d'alertes SQL Server Agent sur les erreurs critiques (corruption, E/S, mémoire, groupes de disponibilité) et sur les niveaux de gravité 16 à 25, puis la configuration d'un audit serveur écrit dans le journal Application de Windows, qu'un agent de collecte de logs (par exemple NXLog) peut ensuite lire.

Alertes sur numéros d'erreur

Chaque alerte suit le même modèle. Exemple pour l'erreur 601 :

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 00601', 
		@message_id=601, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=600, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Le script d'origine crée une alerte de ce type pour chacun des numéros suivants (@enabled=1 sauf indication contraire) :

Domaine Numéros d'erreur
Corruption et E/S 601, 674, 708, 806, 823, 824, 825, 833
Sauvegarde / restauration / journal 3401, 3410, 3414, 3422, 3452, 3619, 3620, 3959
Fichiers et bases 5029, 5144, 5145, 5182, 9001
Service Broker 8539, 8540, 9642, 9691, 9692, 9693
Réplication 14157, 14161, 20572, 20574
Planificateur / mémoire / démarrage 17173, 17883, 17884, 17887, 17888, 17890, 17891, 18401
Divers 26023, 26024, 26070, 28034, 28036, 28047, 28048, 28080, 28091
Groupes de disponibilité 35204, 35207, 35254, 35273, 35274, 35275, 35276, 35279, 41048, 41142, 41144
Désactivées (@enabled=0) 1205, 1480, 35201, 35202, 35206, 35262, 41049 à 41055

Alertes sur niveaux de gravité

Pour les gravités 16 à 25, l'alerte utilise @message_id=0 et @severity :

EXEC msdb.dbo.sp_add_alert @name=N'Severity level 16', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=600, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Le même appel est répété avec @severity de 17 à 25 (noms Severity level 17 ... Severity level 25).

Notifications

Une alerte ne prévient personne sans notification. Exemple pour associer des alertes à l'opérateur DBA par e-mail (@notification_method = 1) :

USE [msdb]
go
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 9602', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 17300', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 17312', @operator_name=N'DBA', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error 18054', @operator_name=N'DBA', @notification_method = 1

L'alerte nommée doit exister au préalable (créée avec sp_add_alert), et l'opérateur également.

Audit de sécurité vers le journal Application

Le script supprime d'abord une éventuelle configuration existante, puis crée un audit serveur vers APPLICATION_LOG et une spécification d'audit.

USE [master]
-- Clean configuration 
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = OFF)
go
drop SERVER AUDIT [Audit_nxlog] 
go
USE [master]
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
WITH (STATE = OFF)
USE [master]
DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
-- create configuration 
go 
CREATE SERVER AUDIT [Audit_nxlog]
TO APPLICATION_LOG
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = OFF)
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]
FOR SERVER AUDIT [Audit_nxlog]
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
-- enable audit 
ALTER SERVER AUDIT [Audit_nxlog] WITH (STATE = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-nxlog]  
FOR SERVER AUDIT [Audit_nxlog]  
    WITH (STATE=ON);  

Remarques

Mastodon LLIMastodon SocialFosstodon