How to check a SQL Server connection is encrypted with SSL

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