Configuring SSL on SQL Server 2012

This is more painful than it should be, but here’s how it’s done.

Step #1: Generate a CSR

In order to get an SSL certificate, you’ll need a CSR. SQL Server is picky about this — the process for creating one differs slightly than it does when creating one for a webserver. The following steps apply to certificates signed by a CA as well as self-signed certificates.

Open the Microsoft Management Console by typing mmc.exe into either the Run or Search boxes found on the Start Menu.

Microsoft Management Console

Microsoft Management Console

Once opened, click on “File” then “Add/Remove Snap-in…”

SQL_CSR_Step_1

Add the Certificates Snap-in

SQL_CSR_Step_2

Be sure to add it under the Computer account

SQL_CSR_Step_3

Local Computer

Then hit OK

SQL_CSR_Step_4

Expand Certificates, right-click on Personal, select All Tasks, Advanced Operations then click on Create Custom Request…

Custom Request

Click Next

Enrollment

Select Proceed without enrollment policy and click Next

Proceed without enrollment policy

Select the highlighted options and click Next

Certificate Enrollment (request options)

Open the Properties dialog

Properties

Give your request a Friendly Name and a Description

SSL for SQL

On the Subject tab, in the Subject name section, you need a Common Name — this should be the FQDN of the server; in the Alternative name section, you need to provide a Type: DNS value for the bare hostname and one for the FQDN of the server. It’s also a good idea to provide a Type: IP address(v4) for each IP that belongs to the server.

Subject tab

On the Extensions tab, click the arrow in the Extended Key Usage box and add the Server Authentication option.

Extensions tab

On the Private Key tab, select the options highlighted below, and click OK, and then click Next.

Private Key tab

Save your CSR somewhere and then either sign it yourself or submit it to a CA.

my_csr.txt

Step #2: Import Your Certificate

Open the Certificates Snap-in just like you did in Step #1, then expand Certificates, right-click on Personal, select All Tasks, and then select Import…

Import Certificate

Click Next, click Browse, locate your certificate file in the Open dialog, and then click Next again:

Certificate Import Wizard

Certificate Import Wizard 2

Browse for the certificate

Certificate import wizard 3

Place all certificates in the Personal store … click Next and then click Finish (and then OK).

Personal Certificate Store

Completing the wizard

Success

Step #3: Configure SQL Server To Use Your New Certificate

Open Sql Server Configuration Manager and restart the SQL Server service. Nothing crazy should happen… not yet.

SSCM_1

Navigate to Protocols for INSTANCENAME, right click on it, and select Properties

Protocols for instancename

Select your certificate and then hit OK

Protocols for instancename 2

Restart the SQL Server service one more time… if it starts up successfully, then you’re done. If it doesn’t (which is highly likely) then continue reading.

Step #4: SQL Server service does not restart successfully

Check the event viewer. If you see the following event IDs: 17120, 17826, 17182, 17663 then the user account the service is running as probably cannot read the server’s private key:

TDSSNIClient initialization failed 'n stuff

First, who is running the service? Open the Sql Server Configuration Manager, locate the SQL Server service, right-click on it, and select Properties. Find the Account Name input box on the Log On tab and make a note of the account name:

SQL Server Properties

Then, re-open the Certificates Snap-in just like you did in Step #1, locate your certificate (Certificates (Local Computer) -> Personal -> Certificates), right-click on it, select All Tasks, then select Manage Private Keys:

Manage Private Keys

A familiar Permissions dialog will appear. Click Add…:

Permissions for private keys

Click the Locations… button and select the location where the account resides… most of the time this will be the local computer:

locations2

locations

Add the account that the SQL Server service runs as. Sometimes for local accounts, it is necessary to type the entire account name into the box titled Enter the object names to select and then hit OK (i.e., do not use the Check Names button.)

Give this user Read access, and then hit OK.

Read access

The SQL Server service should restart successfully now.

Leave a Reply