We are looking for the optimal sql login audit solution, with usable filtering.
Thus far we have looked into Server Audits and profiler but have concluded that extended events look most promising, unless anyone can show us otherwise.
Question is, is there any good way to filter unnecessary login events? e.g. when I connect using SSMS, immediatly about 10 login events are added and continue to pile up if I leave SSMS connected to a instance. Why is that?
This is our current implementation:
USE master;
GO
-- Create the Event Session
IF EXISTS
(
SELECT
*
FROM sys.server_event_sessions
WHERE name = 'ServerLoginAudit'
)
DROP EVENT SESSION ServerLoginAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'D:\audits\Sessions';
GO
CREATE EVENT SESSION ServerLoginAudit
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 TARGET package0.event_file
(SET filename = N'D:\audits\Sessions\ServerLoginAudit.xel', max_file_size = (20), max_rollover_files = (2))
WITH
(
STARTUP_STATE = OFF
, TRACK_CAUSALITY = ON
);
/* start the session */
ALTER EVENT SESSION ServerLoginAudit ON SERVER STATE = START;
GO