6

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?

7

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
  • By default, this does not work for non-sysadmins. Accessing the dm_exec_connections view requires VIEW SERVER STATE permission on the server, which is not granted by default. Do you know of an alternative, which by default, all logins can access ? – William Jan 25 '17 at 16:01
  • why would a non 'VIEW SERVER STATE' user have any business checking whether connections are encrypted? – Mitch Wheat Oct 1 '18 at 16:42
3

There are various methods to check the connections :

  1. 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.

  2. You can also use the Microsoft Network Monitor 3.4 to get those connection details.

  3. And as mentioned by travis you can also look at encrypt_option column of the sys.dm_exec_connections DMV

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.