01 June,2021 by Tom Collins
Question: I'm completing some security reviews to develop a profile of the SQL Server environment. I want to assess the amount of encrypted connections active on SQL Servers & specifically if a connection is using an SSL certificate
Answer:According to the Microsoft documentation for sys.dm_exec_connections encrypt_option definition is : "Boolean value to describe whether encryption is enabled for this connection".
This example query extracts the current connections and identifying - via the encrypt_option column whether a secured connection is used. It won't tell you which cipher suite or protocol is used
Using the encrypt_option value for a connection will give you the VALUE of 1 or 0 - checking if the connection is encrypted.
Using the sqlcmd -N switch will give you an encrypted connection , but it's not necessarily using the certificate configured on a target SQL Server. This command will result in an entry on sys.dm_exec_connections with encrypt_option = TRUE
sqlcmd -E -N -S server\myinstance
EXEC master.dbo.xp_readerrorlog 0, 1, N'cert'
The certificate [Cert Hash(sha1) "A2374234234AXXXXXXvNMLP0987645" was successfully loaded for encryption.
A Client machine should trust the applied certificate - the methods used are:
1- Direct Trust - SQL Server certificate is installed on the client server
2-Chain of trust - The root certificate authority and chain certificates are trusted
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |