How to validate SQL Server traffic is encrypted?

Question

I need to prove that the encryption settings we have in our app's connection string are working.

What would be the simplest way to validate that traffic from our web site to the SQL Server is in fact encrypted?

Answer 1

You check the encrypt_option column of the sys.dm_exec_connections DMV. This way you can no only prove that is encrypted, but you can also validate in your application at start up time. To enforce the encryption you follow the methods described in this MSDN How To: Enable Encrypted Connections to the Database Engine. If either the client or the server forces encryption and a certificate is provided and the client accepts the server certificate, the connection will be encrypted. To validate that the traffic is encrypted you can use the built-in netmon.exe tool (must be installed from ad/remove system components), download the improved Microsoft Network Monitor 3.2 or other third party tools.

As an alternative the deployment site can enforce IPSec encryption.

Answer 2

You could use something like Wireshark to view the packets at they're transmitted over the network

Answer 3

I would set the Force Protocol Encryption to true and Trust Server Certificate to true in the db connection string. The server should fail to establish a connection if it cannot provide you with an encrypted connection as requested. There is an article that covers encryption with sql server 2005 and later.

Simple test is to try a connection with and without encryption and fail when it hands out the undesired type of connection. then its up to the DBA, IT or you to configure the server to match your requirements.

Answer 4

There is another much underrated tool from Microsoft itself: 'Microsoft Network Monitor'. Basically this is very similar to wireshark with the exception that some specific MS protocols have better parser and visualisation support than wireshark itself and obviously it would only run under windows ;-).

The tool is quite old and looks abandoned (havn't seen a newer release so far) but still does an good job and the grammar for defining new protocols is quite neat/interesting - so this still possess a lot of power for the future.

Analysis Example - Recording is filtered for TDS - so the other packets are discared mostly:

This is also true for sql server connections. The MNM can even visualize the resultsets going over the wire - quite neat. Nonetheless wireshark as mentioned above would be sufficient to validate encryption and applied certificates on the wire itself. Means it can understand the TDS-Protocoll fully.

Handling TLS

Also with an extension (so called experts) 'NmDecrypt' and the right certificates (including private keys) - it is possible to decrypt protocolls - quite nice for TDS which uses TLS INSIDE of TDS - no wonder - no one has really implemented that yet as a fully supported protocoll for wireshark ;)

Links for the tools:

• Microsoft Network Monitor: http://www.microsoft.com/en-us/download/details.aspx?id=4865
• NMDecrypt: http://nmdecrypt.codeplex.com/releases/view/85581