SQL Server TLS 1.2 communication really encrypted?

Question

I'm writing a VB6 application which connects with SQL Server. To secure the connection with the database I'm using MSOLEDBSQL as provider which supports TLS 1.2. I also enabled TLS 1.2 in my machine. I verified the connection status using sys. dm_exec_connections and SQL server displays all the connections are encrypted. To double confirm I tried to use the echomirage to check the traffic and the results are surprising. The data is not encrypted and I can read all the data flow as shown in the below image. My question is

1. This communication is really encrypted. If not why I'm seeing different status in SQL server
2. I read somewhere that TCP is binary protocol. If so, why I'm seeing plain text even if this communication is not encrypted?

Answer 1

This is a TDS issue (the protocol used by SQL Server), and is not caused by WireShark in any way.

TLS encryption is only used during the login process to ensure that the credentials are not passed unencrypted over the network.

After that, the remaining bulk-transfer of queries and result sets are not encrypted on the wire.

In order to enable encryption for data (and not just for the login process), you need to use the Use Encryption for Data connection string keyword in your connection string:

Use Encryption for Data=true

• Use Encryption for Data
• SSPROP_INIT_ENCRYPT
• "Specifies whether data should be encrypted before sending it over the network. Possible values are "true" and "false". The default value is "false"."

Be aware: If you enable encryption for data, and the server doesn't have a valid certificate (i.e. the default), then you will get an error when connecting. The client will detect the server presented an invalid certificate, and stop the connection. In order to make your system reliable, you need to include Trust Server Certificate=true in your connection string:

Use Encryption for Data=true;Trust Server Certificate=true

A better alternative is to turn on the option on the server that forces encryption for data, and do not specify Use Encryption for Data or Trust Server Certificate in your connection string.

When it is the server that requires the encryption, the client will not care about the certificate presented by the server (excluding MSOLEDBSQL19, which is broken by default, and you will need to specify Trust Server Certificate=true even when it was the server who wanted the encryption in the first place.

Answer 2

This isn't a TDS issue (the protocol used by SQL Server). The same thing happens with your browser when you use a debugging proxy like Fiddler and trust the proxy's certificate, or configure it to use a trusted certificate. Most likely, you trusted EchoMirage's certificate during setup or through its Settings and forgot about it.

SSL/TLS protect agains Man-In-The-Middle attacks by verifying the other party through certificates. Encryption isn't enough. Without verification a proxy between client and server could pose as the other party, set up encrypted communications with each side, decrypt the packets it receives, inspect them and then encrypt them again using the other side's keys and send them along. Without verification neither client nor server would know someone intercepted the connections.

With SSL/TLS, a connection is established only if both parties trust each other's certificates. Both sides verify the certificates by checking either whether the certificate is explicitly trusted, or if it was issued by a Certificate Authority trusted by the application. If validation fails, the connection fails as well.

Fiddler, WireShark and other similar tools decrypt traffic by acting like a proxy and establishing communications on either side using their certificate. With certificate validation enabled though, the browser (or the SQL Server client) would reject the connection. If you try to connect to a web site through HTTPS while Fiddler is in use you'd get a red warning page saying that the connection isn't safe.

To allow such connections someone would have to go and explicitly trust the tool's certificate. All tools can do this through their settings, but all OSs require privilege elevation and user confirmation before they add the certificate to their trusted list.

By default, drivers and network libraries perform validation. To allow WireShark to intercept the SQL Server connection you'd have to either explicitly disable validation with TrustServerCertificate=true;, or trust the tool's certificate, which is probably something you already did and forgotten about it.

The page Using Encryption Without Validation in SQL Server Native Client in the docs explains what happens when you don't use validation, and warns against it.

If you use Encrypt=true and leave TrustServerCertificate to its default, false, WireShark or EchoMirage won't be able to intercept, much less decrypt the traffic. In this case :

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.