In Sql Server I can Force Encrytion in protocols settings. If I leave that set to No any client can still require encryption.
Can I somehow see what connections to the database are using encryption?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
From TechNet sys.dm_exec_connections DMV: the encrypt_option will display FALSE if the connection is not encrypted.
Here is an example TSQL statement that can be run by administrators (VIEW SERVER STATE is the required privilege):
SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections
There are various methods to check the connections :
You can use a third party tool like Wireshark which lets you see what's happening on your network at a microscopic level and will help you in getting those encrypted connections.
You can also use the Microsoft Network Monitor 3.4 to get those connection details.
And as mentioned by travis you can also look at
encrypt_option column of the
In addition please read the article which will help you understand more on this Encrypting connections in SQL Server 2005 & SQL Native Client with SS
Please note that in SQL Server Management Studio (SSMS) the "View connection properties" does not show if the connection is Encrypted. It only shown if the client (SSMS) has asked for the connection to be encrypted.
If you are using
Force Encryption = true on the server configuration all connections will be encrypted, but SSMS will not indicate it.
You can validate the connection on the server with:
SELECT encrypt_option,* FROM sys.dm_exec_connections
encrypt_option will be
true for encrypted connections