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
- L'audit sur
SUCCESSFUL_LOGIN_GROUPpeut générer un volume important d'événements. - Le chemin d'installation et le fichier de configuration de l'agent de collecte dépendent de l'environnement et ne sont pas repris ici.