Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
* Creates an XEvent session utilizing the 'Trace' event added to SQL 2016
* This event will capture the protocol negotiation details of all TLS connections
* This allows you to capture how many connections are being made and with what
* protocol version and cipher.
* This information can be used to predict the impact of disabling a given
* protocol version or cipher suite before taking such actions.
*/
CREATE OR ALTER PROCEDURE usp_get_tls_connection_stats
@session_name SYSNAME = 'TLS'
AS
DECLARE @query NVARCHAR(MAX);
DECLARE @event_name NVARCHAR(100);
DECLARE @major_version INT = CONVERT(INT, SERVERPROPERTY('ProductMajorVersion'));
DECLARE @build_version INT = CONVERT(INT, SERVERPROPERTY('ProductBuild'));
IF @major_version < 13
BEGIN;
THROW 50000, 'The sni trace event does not return the necessary detail for this procedure on product versions before SQL Server 2016.', 1;
END;
IF NOT EXISTS (SELECT NULL FROM sys.server_event_sessions WHERE [name] = @session_name)
BEGIN;
--From 2019 onward, the event names have a new prefix of 'sni_'. The old events are still defined (removed in 15.0.4073) but never fired.
IF @major_version >= 15
SET @event_name = N'sni_trace';
ELSE
SET @event_name = N'trace';
SET @query = N'
CREATE EVENT SESSION ' + QUOTENAME(@session_name) + N' ON SERVER ';
IF @major_version = 15 AND @build_version < 4073
BEGIN;
--Before 15.0.4073, sqlsni.sni_trace event does not fire unless sqlsni.trace is also set (in any session)
SET @query += 'ADD EVENT sqlsni.trace(),';
END;
SET @query += '
ADD EVENT sqlsni.' + @event_name + N'(
WHERE ([function_name]=''Ssl::Handshake'' AND [sqlserver].[like_i_sql_unicode_string]([text],N''SNISecurity Handshake Handshake Succeeded.%'')))
ADD TARGET package0.histogram(SET filtering_event_name=N''sqlsni.' + @event_name + N''',slots=(2048),source=N''text'',source_type=(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=OFF,STARTUP_STATE=ON)
';
EXEC (@query);
END;
IF NOT EXISTS (SELECT NULL FROM sys.dm_xe_sessions WHERE [name] = @session_name)
BEGIN;
SET @query = 'ALTER EVENT SESSION ' + QUOTENAME(@session_name) + ' ON SERVER STATE = START;';
EXEC (@query);
END;
;WITH session_target AS (
SELECT
s.name AS [session_name]
, st.target_name
, CONVERT(XML, st.target_data) AS [target_data]
FROM
sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets st
ON st.event_session_address = s.address
WHERE
s.name = @session_name
AND st.target_name = 'histogram'
)
, histogram_data AS (
SELECT
st.session_name
, st.target_name
, st.target_data
, ROW_NUMBER() OVER (ORDER BY st.session_name ASC, st.target_name ASC) AS [histogram_id]
, histogram_target.xml_node.value('@truncated', 'INT') AS [truncated_events]
, histogram_target.xml_node.value('@buckets', 'INT') AS [total_bucket_count]
FROM session_target st
OUTER APPLY st.target_data.nodes('/HistogramTarget') histogram_target(xml_node)
)
, histogram_buckets AS (
SELECT
hd.histogram_id
, ROW_NUMBER() OVER (ORDER BY hd.histogram_id ASC, bucket.slot.value('@count', 'INT') DESC) AS [bucket_id]
, bucket.slot.query('.') AS [bucket_xml]
, bucket_value.bucket_text.query('.') AS [value_xml]
, bucket.slot.value('@count', 'INT') AS [bucket_entries]
, bucket_value.bucket_text.value('(text())[1]', 'NVARCHAR(MAX)') AS [bucket_text]
FROM histogram_data hd
OUTER APPLY hd.target_data.nodes('HistogramTarget/Slot') bucket(slot)
OUTER APPLY bucket.slot.nodes('./value') bucket_value(bucket_text)
)
, bucket_chomp_1 AS (
SELECT
hb.bucket_id
, SUBSTRING(
hb.bucket_text
, PATINDEX('%Protocol: %', hb.bucket_text)
, LEN(hb.bucket_text)
) AS [remainder]
FROM histogram_buckets hb
)
, bucket_chomp_2 AS (
SELECT
b.bucket_id
, SUBSTRING(
b.remainder
, LEN('Protocol: ') + 2 --Snip the space
, CHARINDEX(',', b.remainder) - (LEN('Protocol: ') + 2)
) AS [protocol]
, SUBSTRING(b.remainder, CHARINDEX(',', b.remainder) + 2, LEN(b.remainder)) AS [remainder]
FROM bucket_chomp_1 b
)
, bucket_chomp_3 AS (
SELECT
b.bucket_id
, b.protocol
, SUBSTRING(
b.remainder
, LEN('Cipher: ') + 2 --Snip the space
, CHARINDEX(',', b.remainder) - (LEN('Cipher: ') + 2)
) AS [cipher]
, SUBSTRING(b.remainder, CHARINDEX(',', b.remainder) + 2, LEN(b.remainder)) AS [remainder]
FROM bucket_chomp_2 b
)
, bucket_chomp_4 AS (
SELECT
b.bucket_id
, b.protocol
, b.cipher
, SUBSTRING(
b.remainder
, LEN('Cipher Strength: ') + 2 --Snip the space
, CHARINDEX(',', b.remainder) - (LEN('Cipher Strength: ') + 2)
) AS [cipher_strength]
, SUBSTRING(b.remainder, CHARINDEX(',', b.remainder) + 2, LEN(b.remainder)) AS [remainder]
FROM bucket_chomp_3 b
)
, bucket_chomp_5 AS (
SELECT
b.bucket_id
, b.protocol
, b.cipher
, b.cipher_strength
, SUBSTRING(
b.remainder
, LEN('Hash: ') + 2 --Snip the space
, CHARINDEX(',', b.remainder) - (LEN('Hash: ') + 2)
) AS [hash]
, SUBSTRING(b.remainder, CHARINDEX(',', b.remainder) + 2, LEN(b.remainder)) AS [remainder]
FROM bucket_chomp_4 b
)
, bucket_chomp_6 AS (
SELECT
b.bucket_id
, b.protocol
, b.cipher
, b.cipher_strength
, b.hash
, SUBSTRING(
b.remainder
, LEN('Hash Strength: ') + 2 --Snip the space
, CHARINDEX(',', b.remainder) - (LEN('Hash Strength: ') + 2)
) AS [hash_strength]
, SUBSTRING(b.remainder, CHARINDEX(',', b.remainder) + 2, LEN(b.remainder)) AS [remainder]
FROM bucket_chomp_5 b
)
, bucket_chomp_7 AS (
SELECT
b.bucket_id
, b.protocol
, b.cipher
, b.cipher_strength
, b.hash
, b.hash_strength
, SUBSTRING(
b.remainder
, LEN('PeerAddr: ') + 2 --Snip the space
, LEN(b.remainder)
) AS [peer_addr]
FROM bucket_chomp_6 b
)
, bucket_text_parse AS (
SELECT
b.bucket_id
, b.protocol
, b.cipher
, b.cipher_strength
, b.[hash]
, b.hash_strength
, b.peer_addr
FROM bucket_chomp_7 b
)
/*
* Alternate query to get full bucket details instead of summaries
*/
-- SELECT
-- hd.session_name
-- , hd.target_name
-- , hd.truncated_events
-- , hd.total_bucket_count
-- , b.bucket_text
-- , b.bucket_entries
-- , bp.bucket_id
-- , bp.protocol
-- , bp.cipher
-- , bp.cipher_strength
-- , bp.[hash]
-- , bp.hash_strength
-- , bp.peer_addr
-- FROM
-- histogram_data hd
-- INNER JOIN histogram_buckets b
-- ON b.histogram_id = hd.histogram_id
-- INNER JOIN bucket_text_parse bp
-- ON bp.bucket_id = b.bucket_id
-- WHERE
-- hd.session_name = @session_name
SELECT
btp.protocol
, btp.cipher
, SUM(b.bucket_entries) AS [connection_count]
--, STRING_AGG(btp.peer_addr, ', ') AS [peers] --Only valid in SQL 2017. ~50% more performant
, REPLACE(REPLACE(STUFF((SELECT ', ' + peer_addr FROM bucket_text_parse WHERE protocol = btp.protocol AND cipher = btp.cipher FOR XML PATH('')), 1, 2, ''), '&lt;', '<'), '&gt;', '>') AS [peers]
FROM
histogram_data hd
INNER JOIN histogram_buckets b
ON b.histogram_id = hd.histogram_id
INNER JOIN bucket_text_parse btp
ON btp.bucket_id = b.bucket_id
WHERE
hd.session_name = @session_name
GROUP BY btp.protocol, btp.cipher
GO
EXEC usp_get_tls_connection_stats
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment