September 20, 2024 at 7:22 am
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
September 20, 2024 at 12:46 pm
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
September 24, 2024 at 7:58 am
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
September 24, 2024 at 12:51 pm
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
October 3, 2024 at 11:18 am
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.
October 3, 2024 at 11:32 am
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
October 3, 2024 at 12:26 pm
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
October 3, 2024 at 12:41 pm
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
October 3, 2024 at 3:14 pm
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.
October 7, 2024 at 6:30 am
indeed. Thank you for pointing the subtle difference.
Tried it.
Didn't provide the data I aimed for.
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
October 11, 2024 at 2:00 pm
This was removed by the editor as SPAM
October 11, 2024 at 2:01 pm
This was removed by the editor as SPAM
October 14, 2024 at 7:29 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply