Encrypting the ADO.Net connection to SQL Server and verification

Why should we encrypt ADO.Net communication

We all might have heard about encrypting the web server traffic using http(s) protocol. It make sense to any beginner that it should be encrypted since the communication is going via vulnerable public internet. But ADO.Net is also providing us option to encrypt the connections to SQL Server Database. Lets why some reasons to encrypt.

Connecting from client machine to database server

Nobody might be doing direct connections from client to database using windows identity or custom identities. But it was an option people used earlier and if we ended up in legacy systems this is one thing to take care. A low hanging fruit to increase security.

Connecting from web or queue processing server to ADO.Net

This is more often scenario. The client machine to web server communication is already encrypted. Nobody can intercept it. Once the communication reaches the hosting environment, it is difficult for an outside attacker to intercept the communication between web server and database server. This is true if we are in a corporate environment with someone else taken care of the network level protection.

Still there are chance that some insider attacker can intercept.

When we are in cloud environment

Another reason to encrypt the database communication is the cloud hosting. Though the cloud vendor says they are the best in the world to secure everything and obliged to keep things secret, there is still a factor of belief. What if something goes wrong and someone intercept the communication? So it is better to encrypt than taking a chance.

If someone hacks into the environment and obtain the encryption key as well, there is nothing to be done. For example the cloud provider is in a country which is in war with our country and our application is significant enough to help them to win the war. Its Unavoidable. If there is effect, there will be some side effects. We save cost but exposed to less secure environment. 

How to encrypt the SQL Server Communication

Though encrypting the communication is to be final answer to the security, let's see how to encrypt. SQL Server supports mainly certificate based encryption. In other words the certificate as key for encryption or used for key exchange to finalize the encryption. Digging into the details of how it works is not in the scope of this post. Below are some links as those are readily available.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine

https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

More visual help here.

When the connection gets encrypted? It is a great question. There are so many combinations of configuration and below link explains when the SQL Server encrypt, when it use self signed certificate, when it fails etc...
https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-2017

Problem 1 - When connecting from SQL Profiler

Client unable to establish connection ssl provider the target principal name is incorrect

https://stackoverflow.com/questions/37734311/mssql-with-ssl-the-target-principal-name-is-incorrect

We have to use the full computer name when connecting.

Other problems

There are variety of problems users reported when they enable encryption without reading all the docs. Some are related to permission of SQL Account to the certificate, some related to expired or wrong issued to certificates, some experienced because the certificate was in wrong store etc...

Verify the ADO.Net connection is encrypted

As we saw earlier, even after doing the settings there are chances of connection falling back to plain mode without encryption. This section is about how to ensure the connections are encrypted.

Using the sys.dm_exec_connections

There is a DMV called dm_exec_connections. It can be simply queries like below to check the connections and its encryption status.

SELECT session_id, connect_time,client_net_address, net_transport, auth_scheme, encrypt_option, local_tcp_port

FROM sys.dm_exec_connections

WHERE net_transport = 'TCP'

Play with the above code to explore more properties of  SQL server connections. If we need to check whether the connection is encrypted from .Net code, we have to use the @@SPID to get the connection detail in the same DMV.

What is the certificate used for encryption? - Registry

After we enable the encryption via user interface, we can go to registry and ensure the certificate. Below is the location in registry.

HKLM:\Software\Microsoft\Microsoft SQL Server\<SQL InstanceID>\MSSQLServer\SuperSocketNetLib

As instructed in the guidelines, the encryption is enabled per SQL Instance. So one machine can have 2 SQL Instances. One encrypted and other not.

What is the certificate used for encryption? - SQL ErrogLogs

The SQL logs is another place to ensure the certificate what is used for encryption.

References

https://blog.coeo.com/securing-connections-to-sql-server-with-tls
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-client-protocols?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-native-client-11-0-configuration?view=sql-server-2017

https://gyorgybalassy.wordpress.com/2012/09/14/how-to-encrypt-sql-tds-connections/
https://msdn.microsoft.com/en-us/library/ee210043%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
https://stackoverflow.com/questions/25541281/what-is-the-default-network-protocol-in-sqlconnection-class