SQL2019 Extended Events missing EVENT debug_channel and EVENT sqlsni.trace ?

  • I want to detect usage of TLS on our SQL Server 2019 instances.

    Copilot suggested usinig eXtended Events with EVENT debug_channel or EVENT sqlsni.trace.

    I want to have the extended events session store the captured data in a file for later processing

    CREATE EVENT SESSION [DBA_Detect_TLS_communication] ON SERVER
    ADD EVENT debug_channel(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.0%'))
    OR ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.1%'))
    )
    ADD TARGET package0.event_file(SET filename=N'C:\MSSQL15.SQL2019DE\MSSQL\Log\DBA_Detect_TLS_communication_.xel',max_file_size=(25),max_rollover_files=(1))
    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=OFF
    ,STARTUP_STATE=ON)

    However, both fail when trying to create the eXtended Events session.

    Msg 25623, Level 16, State 1, Line 13

    The event name, "debug_channel", is invalid, or the object could not be found

    The instance is a SQL Server 2019 Developer Edition ( 15.0.4390.2 )

    Have these event channels been removed ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hey Johan! I don't think debug_channel is an event. There is a Debug Channel in Extended Events, but it's a grouping of the events that are undocumented, minimally documented, and subject to change without notice and also some events that can put your server into a debug state, careful with all these. Look in the gui to turn that on and then you can see those additional events. Again, caution, caution, caution.

    I did a search, and I don't see any events containing TLS. There are a bunch of debug events that contain the word security and no regular events marked that way. Maybe one of those? I'm honestly not sure.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you for the cautions, Grant.

    I always develop xEvents related stuff on my local box, then on a dev box, then on a QA box and only then ....

    As you see, Copilot doesn't always provide working solution, let alone a responsable solution.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    Thank you for the cautions, Grant.

    I always develop xEvents related stuff on my local box, then on a dev box, then on a QA box and only then ....

    As you see, Copilot doesn't always provide working solution, let alone a responsable solution.

    Hallucinations are a real thing. I got co-pilot to go off the deep end with three questions. I was able to get OpenAI to lose it's mind in two, felt like a triumph.

    The issue is simple. Microsoft has not documented Extended Events well. On purpose for the debug events. So, the training for the AIs has inadequate information. You might give Erik Darling a bump & see if he knows. He's more up on XE than almost anyone I know (except maybe Erin Stellato or Jonathan Kehayias, possibly Gianluca Sartori too).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You may have found this by now, but the event you want to use is sqlsni.sni_trace for 2019.

    https://dba.stackexchange.com/questions/305966/monitoring-sql-server-encryption-with-extended-events

    This isn't shown in the list of available events in SSMS, even though it can be used if you create a session using tsql.

  • doesn't work !!! ( see OP )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan, did you try doing sni_trace? That is an event, in the debug channel. I guess I somehow missed that on the first read through.

    I don't have the setup handy to test the encryption, but this did create a trace and it ran:

    CREATE EVENT SESSION [SNITrace] ON SERVER 
    ADD EVENT sqlsni.sni_trace
    GO

    I can't tell if it's returning the data you want though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • It did generate the same error as the debug channel at create time.

    However, now I see it accepts the create of the event session.

    Time to investigate ( for a short time )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • In the OP you say you tried sqlsni.trace, I pointed out you should try sqlsni.sni_trace (not the same event). The former would have worked on SQL Server 2016 (which is why it didn't work for you), the latter is what's needed on SQL Server 2019 and above. Glad you have it working now.

    It works; I've been using it today for the same sort of reason.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply