Enable SQL SSL with low-privileged service account

One of the neat security features with SQL 2005 and later is the ability to use a SSL certificate to encrypt off-host SQL server communications over port 1433. Encrypting communications between your SQL server and your remote applications is strongly recommended. Do you really want credit card data, personal information or sensitive data traversing the network in clear text? Probably not. Yes you could use IPsec between your SQL server and applications, but that’s not for the faint of heart. But once you know the trick to making SSL work with SQL, it’s a no brainer.

First, your computer needs a server authentication certificate with an allowed enhanced key usage of 1.3.6.1.5.5.7.3.1. For simplicity the domains I manage have an auto-enroll policy that doles out machine certificates to all computers. If you don’t know how to create a machine certificate and don’t have auto-enrollment enabled, I’ll leave that as an exercise for the reader to explore. Assuming you have a valid server authentication certificate, we can continue.

SQL best practices urge that the SQL database engine, and agent service, run under a non-privileged service accounts. Should your SQL server get compromised, it can help limit the damage to the underlying operating system. But if you pick a machine certificate within SQL Configuration manager to use, SQL will fail to start.

The failure message, on Windows Server 2008 R2 with SQL Server 2008 R2 is: Event ID 26014, source MSSQLSERVER. Content of the error is:

Unable to load user-specified certificate [Cert Hash(sha1) “A3B….913C”]. 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.

Hmm…why is that? Well the answer is simple, and the fix is simple as well. The non-privileged service account is attempting to read the private key for the selected certificate and is unable to do so. The solution is to enable the SQL database engine service account read access to the private machine certificate key.

How do you do that? Glad you asked!

1. Open a blank MMC and add the Certificates snap-in. Chose the Computer account.

2. Open the Personal Certificates store and you need to see one certificate with the FQDN of your SQL server. If not, then no SSL for you!

3. Right click on the certificate and select All Tasks, Manage Private Keys.

4. Click Add and locate the SQL database engine service account. Change the permissions to Read only.

5. Click OK.

6. Open the SQL Server Configuration Manager and expand SQL Server Network Configuration. Open the properties of Protocols for MSSQLSERVER.

7. Change Force Encryption to Yes. Click on the Certificate tab and from the drop down select the certificate that is listed. It should be the same one you changed the permissions on from the previous step.

8. Close all windows and restart the SQL service. With any luck the SQL services will start.

9. To verify SQL is using the PKI certificate, check the Windows Application log for event ID 26013. It should read something like:

The certificate [Cert Hash(sha1) “A3B2…7913C”] was successfully loaded for encryption.

And there you go! To recap, we assigned an existing machine certificate to the SQL service, changed the permissions on the private key, and restarted the SQL service. Pretty easy once you know the permissions trick.

If you are really paranoid, whip out Network Monitor 3.4 (not Wireshark) and capture a trace during a connection attempt to the SQL server. Under the protocol column you should see a TLS session between the SQL server and your application server.

I noticed that it appears the SQL client that comes with SQL Server 2008 R2 does NOT attempt to negotiate a TLS 1.2 session, like IIS 7.5 and IE8 on Windows 7 does as mentioned in my prior blog.