Capturing Successful Logins With an Extended Events Session

Successful logins can be captured with a small Extended Events session, but a login event is a connection, not a user action. One query can leave several login events behind. Let me show you.

Why capture logins at all

Someone from security asks, “Who connected to this server last Tuesday?” You open your tools and find nothing, because nobody turned on login tracking. It is an awkward conversation. A small Extended Events session fixes that going forward.

Before you build it, look at what the login event can tell you. The first query finds the event. The second lists its columns.

SELECT name, description
FROM sys.dm_xe_objects
WHERE object_type = 'event' AND name = 'login';

SELECT c.name, c.type_name, c.description
FROM sys.dm_xe_object_columns AS c
JOIN sys.dm_xe_objects AS o
  ON o.name = c.object_name AND o.package_guid = c.object_package_guid
WHERE o.object_type = 'event' AND o.name = 'login'
ORDER BY c.column_id;

The event fires for a new connection and also when a connection is reused from a connection pool. The column is_cached tells them apart. Keep it in every report. Without it, you will count pool reuse as people connecting.

Create a small session

To get a login event on demand, the demo connects back to its own server through a linked server. That is a quick way to open a second connection from a script. Linked-server connections call themselves Microsoft SQL Server, so the session filters on that name.

The demo creates two server-level objects, a linked server and an event session, and removes both at the end. It needs permission to create event sessions. The session keeps its events in a small ring buffer in memory and does not start with the server.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'LoginDemo')
    DROP EVENT SESSION LoginDemo ON SERVER;

IF EXISTS (SELECT 1 FROM sys.servers WHERE name = N'LoopbackDemo')
    EXEC sys.sp_dropserver @server = N'LoopbackDemo', @droplogins = 'droplogins';

EXEC sys.sp_addlinkedserver
     @server = N'LoopbackDemo', @srvproduct = N'', @provider = N'MSOLEDBSQL',
     @datasrc = @@SERVERNAME, @provstr = N'TrustServerCertificate=yes';
EXEC sys.sp_serveroption N'LoopbackDemo', N'data access', N'true';

CREATE EVENT SESSION LoginDemo ON SERVER
ADD EVENT sqlserver.login
    (ACTION (sqlserver.username, sqlserver.client_hostname, sqlserver.client_app_name)
     WHERE ([sqlserver].[client_app_name] = N'Microsoft SQL Server'))
ADD TARGET package0.ring_buffer (SET max_memory = 1024, max_events_limit = 200)
WITH (STARTUP_STATE = OFF, MAX_DISPATCH_LATENCY = 1 SECONDS);

ALTER EVENT SESSION LoginDemo ON SERVER STATE = START;

Make one connection and read what was recorded

Now run a single, boring query through the linked server. It counts databases. That is all the application “did.”

SELECT COUNT(*) AS DatabasesSeenThroughLoopback
FROM LoopbackDemo.master.sys.databases;

The ring buffer stores its events as XML, so the next block shreds them into a temp table. It waits two seconds first, because events reach the buffer after a short dispatch delay.

WAITFOR DELAY '00:00:02';

DECLARE @Events xml;

SELECT @Events = CONVERT(xml, t.target_data)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
WHERE s.name = N'LoginDemo' AND t.target_name = N'ring_buffer';

DROP TABLE IF EXISTS #LoginEvents;

SELECT e.value('@timestamp', 'datetime2')                                      AS EventUtc,
       e.value('(data[@name="is_cached"]/value)[1]', 'varchar(10)')            AS IsCached,
       e.value('(action[@name="username"]/value)[1]', 'nvarchar(128)')         AS UserName,
       e.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')  AS ApplicationName
INTO #LoginEvents
FROM @Events.nodes('/RingBufferTarget/event') AS n(e);

SELECT EventUtc, IsCached, UserName, ApplicationName
FROM #LoginEvents
ORDER BY EventUtc;

SELECT IsCached, COUNT(*) AS LoginEvents
FROM #LoginEvents
GROUP BY IsCached
ORDER BY IsCached;

In my run, that one query produced three login events. One has is_cached false, a brand-new connection, and it carries the user name. The other two are true, reused connections, and the user name is NULL. Your counts may differ a little, but you should see more than one event.

This is the lesson. If you counted events, you would say “three logins” for one query from one user. If you counted only new connections, you would say one. Decide what you are counting before you report a number. And notice that the user name here is only the login. A service account used by a website can stand for thousands of real people.

Do not trust the labels

The host name and application name come from the client. A client can set them to anything, so use them as clues, not as proof. The same goes for any filter built on them. My filter on the application name would also catch any other linked-server traffic on a busy server.

Choose retention separately

A ring buffer is fine for a short test. It is not an audit trail, because it lives in memory and old events fall out of it. For a lasting record, use an event file target with a size limit, rollover and a folder the service account can write to. Capture only what your question needs. Recording every connection forever just because the demo was easy is how disks fill up.

Also remember that a gap in your records is unknown history, not proof that nobody connected. Test with a connection you know should appear and one that should not, so a quiet report does not hide a bad filter. Now remove both server-level objects.

ALTER EVENT SESSION LoginDemo ON SERVER STATE = STOP;
DROP EVENT SESSION LoginDemo ON SERVER;
EXEC sys.sp_dropserver @server = N'LoopbackDemo', @droplogins = 'droplogins';
DROP TABLE IF EXISTS #LoginEvents;

Next time someone asks who connected, you will know exactly what you can and cannot say.

A login event is not a user action, it is a connection that may be reused.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.