SQL Server 2008 / 2012 – Encrypting connections with an SSL Certificate

/in /by

Valid for: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

If you are communicating with a SQL server 2008 or SQL Server 2012 over an unencrypted internet link then it is essential that the traffic is secured. The best way to do this is using an SSL (Secure Sockets Layer) certificate. This blog post provides the instructions necessary to accomplish it. If this is a new setup I would suggest testing that standard connection to your server works before implementing an encrypted connection. Troubleshooting is always more difficult if the basics have not been tested previously.

Pre-requisites

  • Ensure that the computer name on your SQL server matches the name you wish to use for client connections. For example if client applications connect to “sqlsrv.cloudpeople.com.au” then the computer name should be sqlsrv. This name will also need to be registered as an A record on your public DNS name servers.
  • Allow port 1433 through the firewall (or appropriate port as configured on your SQL server)

Step 1 – Generate a certificate request

The easiest way is to use certutil and an input file.

Edit the subject line of this request.inf file and change the following

E = your email address
CN = the FQDN of your server name
OU = this can be anything you like that reflects your organisation structure (e.g. Internal Infrastructure)
O = your organisation name
L = city
C = country code

Open an elevated command prompt on your server and run this command

  • certreq -new request.inf %computername%.req.txt

Step 2 – Request a certificate

Use any public certificate authority to sign your SSL request. Most authorities will require you to upload the req.txt file or copy the contents into their portal. This file can be opened using notepad (or similar). Godaddy is always reasonably priced – google “godaddy discount coupon” prior to purchasing as there is ALWAYS coupons available. More expensive certificate are NOT more secure in terms of encryption. This is controlled by the keylength.

Step 3 – Install certificate and set permissions

Copy the signed certifcate files locally to your SQL server. On your SQL server open a blank MMC console and add the certificates snap-in. Choose “Computer Account”, then Local computer and hit finish. Browse to this location

  • Console Root \ Certificates \ Personal \ Certificates

Right click, choose all tasks and then import. Locate the certificate file and import it. Refresh the MMC console (F5) and you should now see the new server certificate. Double click the certificate and you should see “You have a private key that corresponds to this certificate” – if this is not present there has been a problem during generation and the private key has been lost. Do not go any further until this is correct.

Right click on the certificate – choose all tasks and Manage Private Keys… Add the SQL Database Engine Service account user with READ permissions.

Note: If this last step is missed the SQL Service will not start and the failure message in the windows application log will be: Event ID 26014, source MSSQLSERVER, contents

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.

Step 4 – Configure the SQL server to enforce SSL encryption

Open the SQL Server Configuration Manager (Start – Microsoft SQL Server – Configuration Tools – SQL Server Configuration Manager). Expand the SQL Server Network Configuration and right click on the protocol properties. On the certificate tab configure the Database Engine to use the certificate. If you cannot see the certificate one of the previous steps is not correct. On the Flagstab set the ForceEncryption option to Yes.

The SQL Server must be restarted after changing this setting.

Step 5 – Test

There are a couple of test that can be done. The first is to check the Windows Application log for event ID 26013 with content:

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

The next test should be to setup a Netmon or Wireshark session between the client and the server.

Step 6 – Relax

Sit back and relax in the knowledge that you’ve secured your client server application and are ahead of the other 99% of sysadmins who haven’t read this article.