Verifying if a connection to SQL Server is Encrypted


If you do not have a network parser such as netmon and want to verify if the connection from the client to the SQL server is encrypted, the following query can be utilised:

SELECT encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

— Note: remove the WHERE clause to see all connections.

Comments (3)

  1. N.Raja says:

    Hi James. Pls refer to the ‘Note’, by Shashank. Removing the Where Condition will throw out the status of all the connections and if you add other cols to the select statement, you can even locate the client machines that are not using encryption as well.

  2. zeelaf says:

    good tip.. without installing  network parser able to find  encryption status.

    MSDN KB article should have  listed this.. instead of pushing for  netowkr parser install…

    Thank you

  3. james says:

    Is there a way of determining if all connections to a shared SQL server is encrypted.
    I know you can force encryption server side, but if you wanted to know the ratio of encrypted vs unencrypted connections in totality.