]> SQL Server connection encryption 🌐:aligrant.com

SQL Server connection encryption

Alastair Grant | Wednesday 20 July 2016

I have just been looking at connection encryption for SQL server - the default install of SQL doesn't require any encryption of data between the client and the server. Given the relatively cheap cost of processing power it seems like a good thing to explore where feasible.

The MSDN documentation on SQL connection encryption is very light-weight and it's worth noting a few gotchas in setting this up.

Obviously you'll need an internal CA setup. This is a role easily added to a DC. The simplest way to create a certificate you can use is to use IIS to generate a certificate request. It must be a FQDN (e.g. myserver.mydomain) You can then upload this request to your certificate authorities web site (http://[server]/certsrv) - but if you're using IE, you have to run it in Administrative Mode to be able to access the "Web Server" template type. Simpler to use Firefox and just upload the text of your certificate request.

The certificate you download from the CA doesn't have a key, that's stored in IIS. So complete the request in IIS and you'll have a certificate and private key stored in your local computer's "personal" store. You need to grant access to the SQL Server service account for this to work. By default this is "NT Service/MSSQLSERVER" - it only needs read access.

In the Sql Server Configuration Manager you should now be able to select a certificate from the drop down list and enable forced encryption. A restart of the service enables this.

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.