1

I have an extended event session setup. This is the definition.

CREATE EVENT SESSION [Successful-Logins] ON SERVER
ADD EVENT sqlserver.connectivity_ring_buffer_recorded (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_principal_name,
    sqlserver.session_id, sqlserver.sql_text ) ),
ADD EVENT sqlserver.login ( SET collect_options_text = ( 0 )
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) ),
ADD EVENT sqlserver.logout (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) )
ADD TARGET package0.event_file ( SET filename = N'Successful-Logins' ,
                                  max_file_size = ( 10 ) ,
                                  max_rollover_files = ( 0 ) )
WITH ( MAX_MEMORY = 4096 KB ,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
        MAX_DISPATCH_LATENCY = 30 SECONDS ,
        MAX_EVENT_SIZE = 0 KB ,
        MEMORY_PARTITION_MODE = NONE ,
        TRACK_CAUSALITY = ON ,
        STARTUP_STATE = OFF )
GO

My intention is to query sys.fn_xe_file_target_read_file regularly with a filter on server_principal_name. Unfortunately, this field is not always populated. From the same client app and client host, I have some events where server_principal_name is NULL and other where it is populated.

I'm using this query for testing.

SELECT  --TOP 10 
        CONVERT(VARCHAR(19),DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')),120) AS [timestamp] ,
        xevents.event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        DB_NAME(xevents.event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS [database_id],
        xevents.event_data.value('(event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(max)') AS [server_principal_name],
        xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client_app_name],
        xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname]
FROM sys.fn_xe_file_target_read_file
            ('C:\Program Files\Microsoft SQL Server\MSSQL11.INSTANCENAME\MSSQL\Log\Successful-Logins*.xel',
            NULL,null, null) f
CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
ORDER BY DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')) DESC 
8
  • Which events are returning NULL? Jan 15, 2015 at 19:20
  • Both login and logout. Out of 3387 events only 15 are NOT NULL.
    – SQL Hammer
    Jan 15, 2015 at 19:37
  • What is @@VERSION? I tried to repro and both SQL and Windows auth logins populated correctly in all cases. Is there a logon trigger? Are any of these logins coming in through AD group membership without an explicit login name for them? How about contained database users? Jan 16, 2015 at 3:41
  • Great questions. No logon triggers. No these particular users are all AD logins (non-groups). No contained database users. @@VERSION = "Microsoft SQL Server 2012 - 11.0.5532.0 (X64) Jul 14 2014 15:00:27 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)"
    – SQL Hammer
    Jan 16, 2015 at 13:34
  • 2
    It's funny, four years ago I challenged Derik for a repro. Today I hit it myself (it was a domain user accessing via a group through SSMS) and I'm still not sure how - but I added username in addition to server_principal_name to the session definition, and then coalesce on the output. Haven't hit NULL again since (but I'll keep watching). Feb 23, 2020 at 22:00

1 Answer 1

0

This question deserves an answer, so cribbing from @Aaron Bertrand's comment (plus my own experience of this today):

This appears to happen when a domain user has access to login because of group membership, rather than directly.

The connecting client/application doesn't appear to matter: Aaron mentions SSMS, but I'm seeing this for SQL Server linked server connections.

In these cases, the true username should be recorded in a different field. Likely ones to capture and check are:

  • username
  • session_server_principal_name

If in doubt, configure the XE session to capture all fields that might be relevant, and look at the raw XML for an example record to see where you see a username. You can then combine these for output via COALESCE() or similar.

Side note: strictly, it's not that this field is being recorded as a null, but rather that the XML tag is not always present in the XE results. The null only appears when you shred the XML results to a columnar format. Noted in case this helps any reader's understanding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.