How to Encrypt SQL communication on the wire

A lot of applications are moving to the cloud. The Iaas model provides a great way to lift and shift the applications and start leveraging the goodness of the cloud without having to re-write to adapt to the Platform-As-a-Service (Paas) model. Of course, the Paas is going to be immensely efficient in the longer term, but just moving the infrastructure is perhaps the easiest step for most app teams. Most of the times, it also means moving the database servers to the cloud.

While on-premises, most applications did not take advantage of securing the communications to the database on the wire just because of the inherent isolation advantages of having the application ONLY on the internal LAN. However, as more and more of these applications start moving to the cloud in Iaas model, the security of data over the wire is rightfully starting to gain increased importance.

Here is a quick guide to how you can setup your databases and database clients to encrypt communications over the wire. Do note that if you are moving your databases to Azure SQL database, you don't really need to worry about this. Azure SQL Database enforces encryption (SSL/TLS) at all times for all connections, which ensures all data is encrypted "in transit" between the database and the client. There are some minor considerations with Azure SQL databases that we will cover below.

But first, let's fry the big fish. Let's look at what happens to your database servers that are lifted and shifted to Azure in Iaas model. Basically moving your database server VMs to the cloud and having your applications connect to these databases. For most purposes, this model is very similar to how the databases are deployed in your data centers except that the underlying hardware is now the cloud. Which also means that you need to explicitly take care of encrypting the communication over the wire using TLS. TLS is a common security  mechanism used with web applications. That's what is happening behind the scenes when you see a URL that starts with HTTPS. If you are familiar with the mechanism of TLS on the web applications, you would be glad to know that your SQL communication can use exactly the same style of communication. To make it really simple, you simply setup a certificate that is issued for Server Authentication and set some properties to enable encrypted communication. The server can either force all the connections to be encrypted from the client or simply let the client determine if the connection needs to be encrypted or not. In terms of SQL, this gets determined by the SQL connection string.

On the server side, you will be Installing a certificate to allow connection encryption. The certificate must be issued for Server Authentication and the name of the certificate must be the FQDN of the computer. To use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.<your company>.com and test2.<your company>.com, and you have a virtual server named virtsql, you need to install a certificate for virtsql.<your company>.com on both nodes. Once the certificate is installed, the SQL Server Configuration Manager would allow you to force encryption from the client connections. After this point, if the certificate is from a well-known certification authority, then the process is pretty much done. However, if you are using a self-signed certificate, you would want to set the "Trust Server Certificate" setting on the server along with the Force Encryption.

Once the process on the server is done, the client connection string can be configured for secured connections as well. There are two key parameters on the connection string that need to be looked at for this purpose.

• Encrypt: Setting this to true encrypts all data sent between client and server.

• TrustServerCertificate: When set to true, SSL/TLS is used to encrypt the channel when bypassing walking the certificate chain to validate trust. If TrustServerCertificate is set to true and Encrypt is set to false, the channel is not encrypted. Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail.

The client application settings never reduce the level of security set by SQL Server Client Configuration Manager, but may strengthen it. For example, if Force Protocol Encryption is not set for the client, an application may request encryption itself. To guarantee encryption even when a server certificate has not been provisioned, an application may request encryption and "TrustServerCertificate". However, if "TrustServerCertificate" is not enabled in the client configuration, a provisioned server certificate is still required.

If you are using Azure SQL Database, as mentioned above, all the communication is always encrypted. However, in those cases the client connection string guidance is slightly different. In your application's connection string, ensure that you specify an encrypted connection and not to trust the server certificate (For the ADO.NET driver this is Encrypt=True and TrustServerCertificate=False). This helps to prevent your application from a man in the middle attack, by forcing the application to verify the server and enforcing encryption. If you obtain your connection string from the Azure portal, it will have the correct settings.

 

Here is a quick cheat-sheet of how the settings on the client and server are going to be impacting your security of communication on the wire.

Force Protocol Encryption client setting	Trust Server Certificate client setting	Encrypt/Use Encryption for Data connection string/attribute	Trust Server Certificate connection string/attribute	Result
No	N/A	No (default)	Ignored	No encryption occurs.
No	N/A	Yes	No (default)	Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.
No	N/A	Yes	Yes	Encryption always occurs, but may use a self-signed server certificate.
Yes	No	Ignored	Ignored	Encryption occurs only if there is a verifiable server certificate; otherwise, the connection attempt fails.
Yes	Yes	No (default)	Ignored	Encryption always occurs, but may use a self-signed server certificate.
Yes	Yes	Yes	No (default)	Encryption occurs only if there is a verifiable server certificate; otherwise, the connection attempt fails.
Yes	Yes	Yes	Yes	Encryption always occurs, but may use a self-signed server certificate.