How can I check if connection to Sql Server is encrypted?

Question

In SQL Server I can "Force Encryption" 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?

score 24 · Accepted Answer · edited Jul 05 '17 at 02:22

24

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

edited Jul 05 '17 at 02:22

Mike

648
4
13

answered Oct 13 '15 at 13:10

Travis Page

656
5
7

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
2

why would a non 'VIEW SERVER STATE' user have any business checking whether connections are encrypted? – Mitch Wheat Oct 01 '18 at 16:42
1

@MitchWheat The same reason you, a 'standard user', can see the padlock icon up top in your browser right now - to know that their connection is encrypted. Users must not need administrator privileges to ensure encryption is being used. tldr: for security – Ian Boyd Apr 04 '21 at 23:58
If a standard user (I would say a client application) want a encrypted connection. It has to use/check the dedicated key (Encrypt=True) in the connexion string ! – Marco Guignard May 19 '21 at 10:08

score 5 · Answer 2 · edited Mar 25 '19 at 17:39

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

score 3 · Answer 3 · answered Jan 07 '21 at 14:21

3

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

answered Jan 07 '21 at 14:21

Henrik Høyer

179
1
6

1

This answer doesn't seem to add any new information to the earlier accepted answer. – mustaccio Jan 07 '21 at 14:27
1

It add the very often reason for why people ask this question, "SSMS connection properties does not show if the connection is Encrypted" – Henrik Høyer Jan 09 '21 at 12:59
1

@mustaccio StackExchange is a combination of Reddit and Wikipedia. As such, more relevant information is always welcome. – Ian Boyd Apr 05 '21 at 00:05
1

Thanks, this helped with some head scratching on my end. – csrowell May 10 '21 at 18:32

score 2 · Answer 4 · answered Dec 05 '23 at 12:34

2

You can also check whether the connection in encrypted in the SSMS, when you connect to a server with encryption, you will see a lock icon next to the server name in the bottom panel:

Without encryption, there is only server name without lock icon.

answered Dec 05 '23 at 12:34

Václav Fanfule

21
1

How can I check if connection to Sql Server is encrypted?

4 Answers4

Linked