Monitoring SQL Server Encryption With Extended Events



  • Is there a way to monitor for encrypted connections through Extended Events? I've monitored for TLS 1.0, 1.1 and 1.2 handshakes in the past using sqlsni.TRACE but cannot find something along the same lines for encrypted connections themselves. Unless "encrypt=yes" is not added to the connection string only the handshake will be encrypted and nothing else.

    I can use sys.dm_exec_connections to capture this but it is not real time and I'd have to run it very frequently (every minute or less) to find what I'm looking for.



  • Yes, this can be done using the below trace event,

    " You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:"

    CREATE EVENT SESSION [tls] ON SERVER 
    ADD EVENT sqlsni.trace(
        WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') 
    AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
    ADD TARGET package0.event_file(SET filename=N'tls_trace')
    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=OFF);
    

    " The original answer is https://dba.stackexchange.com/a/189626/10267 by https://dba.stackexchange.com/users/44434/dan-guzman

    Also note, if this needs to be done in 2019 change the event to sqlsni.sni_trace

    and the code will be like

    CREATE EVENT SESSION [tls_new] ON SERVER 
    ADD EVENT sqlsni.sni_trace(
        WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') 
    AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
    ADD TARGET package0.event_file(SET filename=N'tls_trace')
    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=OFF)
    

    EDIT After the OP's comment, the requirement is to find the connection is encrypted or not and not TLS.

    I don't know an option in Extended event for this, but to acheive this you can leverage https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver15 in SQL server.

    Below is an attempt to capture the sys.dm_exec_connections after each login.

    DROP TABLE IF EXISTS test.dbo.tmp_conn
    SELECT * INTO test.dbo.tmp_conn FROM sys.dm_exec_connections dec
    WHERE 1=0
    GO
    CREATE TRIGGER connection_encrypt
    ON ALL SERVER WITH EXECUTE AS N'sa'  
    FOR LOGON  
    AS  
    BEGIN  
    INSERT INTO test.dbo.tmp_conn
    SELECT * FROM sys.dm_exec_connections dec
    WHERE dec.session_id=@@spid
    END;  
    

    Please change the code as per your need for the audit.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2