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
Once opened, click on “File” then “Add/Remove Snap-in…”
Add the Certificates Snap-in
Be sure to add it under the Computer account
Then hit OK
Expand Certificates, right-click on Personal, select All Tasks, Advanced Operations then click on Create Custom Request…
Select Proceed without enrollment policy and click Next
Select the highlighted options and click Next
Open the Properties dialog
Give your request a Friendly Name and a Description
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.
On the Extensions tab, click the arrow in the Extended Key Usage box and add the Server Authentication option.
On the Private Key tab, select the options highlighted below, and click OK, and then click Next.
Save your CSR somewhere and then either sign it yourself or submit it to a CA.
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…
Click Next, click Browse, locate your certificate file in the Open dialog, and then click Next again:
Place all certificates in the Personal store … click Next and then click Finish (and then OK).
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.
Navigate to Protocols for INSTANCENAME, right click on it, and select Properties
Select your certificate and then hit OK
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:
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:
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:
A familiar Permissions dialog will appear. Click Add…:
Click the Locations… button and select the location where the account resides… most of the time this will be the local computer:
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.
The SQL Server service should restart successfully now.