usp_get_tls_connection_stats.sql

/*
 * 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