SQL Server Encryption with "localhost" certificate

Question

Using SQL Server 2014/2016:

I'm trying to encrypt my connections from my application to my local SQL Server instance. As I read the documentation, SQL Server seems to very much prefer certificates issued to a fully qualified domain name (not just localhost). SQL Server Configuration Manager only seems to see certs issued to the machine's FQDN.

I found this article which seems to indicate that you can use wildcard certificates as well.

But I am connecting from a local app to a local SQL Server instance that is not exposed to the network. Is there any way to associate a certificate issued to localhost with a SQL Server instance? This is doable with IIS Certificates (Encrypting HTTP traffic to localhost).

Internal security policies require that any inter-process communication with the database, even on a local machine, be encrypted. From what I can tell there are no encryption options for Named Pipes. We are disabling all other protocols and only allowing TCP/IP, because it is the only option that supports encryption, as mandated by our security policies.

The problem is these are 300+ instances of SQL Express on individual workstations. The certificate management if we can't use just one localhost cert will be a nightmare.

Answer 1

Just for fun, I tried to do this with a wildcard certificate. The fact that anyone can do this with a self-signed certificate, for a local application, being forced to use SSL when shared memory would work much more efficiently, is kind of bogus. This adds nothing to the security of the environment.

Create A New Cert

New-SelfSignedCertificate -Subject "CN=*,DC=Dublin,DC=local" -KeySpec KeyExchange 
-KeyFriendlyName "SQLWildcard" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" 
-CertStoreLocation "Cert:\LocalMachine\My"

Note that a FQDN of localhost and a short name of localhost both seemed to work as well. It still doesn't make anything any more secure. If anything, forcing it through the TCP stack adds in more places for filter drivers and other items to inspect and attack.

Give Permissions To The Private Key

Here I am giving the service account access to the private keys. Since most of the time SQL Server Express Edition is running as Local* account you can skip this step if you've put the cert in the local machine's MY location. Put this in for completeness.

Setting The Instance To Use The Certificate For SSL

This won't show up in the certificate picker, because the issued-to and subject don't actually match the server. Plus, if you have 3000+ instances to do this on, chances are you aren't going to do it through the GUI.

The location of the registry key is: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\*InstanceID*\MSSQLServer\SuperSocketNetLib with the value name of Certificate which takes the thumbprint of the certificate.

Double Checking

If everything went well, you'll see that the certificate thumbprint was successfully loaded for SQL Server on the next restart of the service.

The certificate [Cert Hash(sha1) "F830C76FDDAC472807AF46A1C44450BBA56443D7"] 
was successfully loaded for encryption.

If something went poorly, you'll see that there was a failure to initialize SSL. It will be followed by a fairly verbose output of the error code and status code which will help you in determining why the failure occurred.

Unable to load user-specified certificate [Cert Hash(sha1) "ED3D2F4360780AF71ABCE83A1C3A6F3BE13E1B96"]. 
The server will not accept a connection. 
You should verify that the certificate is correctly installed. 
See "Configuring Certificate for Use by SSL" in Books Online.

Alternate DB technologies that are out-of-process will have the same fate. You can use localhost as I explained above.