Encrypting SQL Server connections from .NET applications

When you want to improve the security of your .NET applications accessing SQL Server databases, one way of doing this is to encrypt the communications between your application and the database server. This is often called security during transit.

In SQL Server, the connection can be encrypted using SSL. As you know, SSL encryption is easy to use, but requires a certificate to be created of bought. So, depending on the situation, encrypting SQL Server connections might not be completely free.

A good document on Technet gives you the instructions on how to enable the certificate on SQL Server. Basically, this is done through the properties of the network protocols in SQL Server Configuration Manager.

Once you've configured the server-side, you can simply enable encryption in your ADO.NET connection string in your .NET application. By simply adding "Encryption=yes", the communication channel to SQL Server will be encrypted, if the server is properly configured. You can also enable the "Force Encryption" option in SQL Server to make sure the server accepts only encrypted connections. This allows you to make sure you don't forget to specify the encryption setting in your connection strings.

This setting is available if you use .NET's SQLConnection and SQLCommand classes, Entity Framework, or similar libraries. So all in all, all technologies that use ADO.NET at the end should support this setting in the connection strings.

Note that if you are using SQL Server 2008 R2 or later (at this writing, 2012 or 2014), then you must pay attention to the host name you use in your connection string. SSL certificates are always issued to fully-qualified domain names such as "mydb.domain.local", but in your connection string, you are able to connect to the server using only a simple name such as "mydb". But in SQL Server 2008 R2 or later, this doesn't work with encrypted connections. Instead, you must fully qualify the database server if you wish to encrypt the connection.

Finally, a short reminder. Encrypting the transmission channel (the network connection) doesn't make data encrypted on the disk. To protect your database files on the database server (or the backups of that database), you should employ methods to encrypt the data on disk SQL Server support something called TDE (Transparent Data Encryption) for this.

Hope this helps!