Auditer les connexions et les échecs de connexion avec Extended Events
Sessions Extended Events pour enregistrer les connexions, déconnexions et échecs de connexion SQL Server dans des fichiers .xel.
Les sessions Extended Events ci-dessous enregistrent dans des fichiers .xel les connexions, les déconnexions et les échecs de connexion. La première requête liste les événements login et logout disponibles sur l'instance.
Lister les événements de connexion
SELECT xp.name AS PackageName,
xo.name AS EventName,
xo.description
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.name = 'login'
OR xo.name = 'logout';
Auditer les connexions et déconnexions
Cette session enregistre les événements login et logout, avec le texte SQL et le nom de l'utilisateur. Elle n'est pas démarrée automatiquement au démarrage de l'instance.
USE master;
GO
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'SVRLoginOutAudit')
DROP EVENT SESSION SVRLoginOutAudit ON SERVER;
GO
CREATE EVENT SESSION SVRLoginOutAudit
ON SERVER
ADD EVENT sqlserver.login
(
SET collect_database_name = (1),
collect_options_text = (1)
ACTION
(
sqlserver.sql_text,
sqlserver.nt_username,
sqlserver.server_principal_name,
sqlserver.client_hostname,
package0.collect_system_time,
package0.event_sequence,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_nt_username,
sqlserver.client_app_name,
sqlserver.session_id,
sqlserver.context_info,
sqlserver.client_connection_id
)
),
ADD EVENT sqlserver.logout
(
ACTION
(
sqlserver.sql_text,
sqlserver.nt_username,
sqlserver.server_principal_name,
sqlserver.client_hostname,
package0.collect_system_time,
package0.event_sequence,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.username,
sqlserver.session_nt_username,
sqlserver.client_app_name,
sqlserver.session_id,
sqlserver.context_info,
sqlserver.client_connection_id
)
)
ADD TARGET package0.event_file
(
SET filename = N'D:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\SVRLoginOutAudit.xel',
max_file_size = (5120),
max_rollover_files = (4)
)
WITH
(
STARTUP_STATE = OFF,
TRACK_CAUSALITY = ON
);
GO
ALTER EVENT SESSION SVRLoginOutAudit ON SERVER STATE = START;
GO
Auditer les échecs de connexion
Cette session enregistre l'erreur 18456 (échec de connexion), de gravité 14 et d'état supérieur à 1. Elle démarre avec l'instance.
USE master;
GO
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'Audit_FailedLogon')
DROP EVENT SESSION Audit_FailedLogon ON SERVER;
GO
CREATE EVENT SESSION Audit_FailedLogon
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.nt_username,
sqlserver.server_principal_name,
sqlserver.session_nt_username,
sqlserver.transaction_sequence,
sqlserver.username
)
WHERE ([severity] = (14) AND [error_number] = (18456) AND [state] > (1))
)
ADD TARGET package0.event_file
(
SET filename = N'D:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\Audit_FailedLogon.xel',
max_file_size = (5120),
max_rollover_files = (4)
)
WITH
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 3 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION Audit_FailedLogon ON SERVER STATE = START;
GO
Remarques
- Le dossier cible des fichiers
.xeldoit exister avant le démarrage des sessions. - Chaque session écrit dans son propre fichier.