07.15
I recently was trying to enable SSL encryption on my SQL 2008 R2 server to allow a remote client web site application to connect securely. I started searching the web for blogs, forums, and technet articles that would explain how to do this since I’ve never done it. This blog post will not cover how to set up this configuration in general, Microsoft (http://technet.microsoft.com/en-us/library/ms191192.aspx) and others have good documentation on this. The scenario I was having was that even after I had created a certificate, the drop down menu within the SQL Server Configuration Manager for the protocols properties was empty. I believed I had a couple things working against me that maybe are affecting you as well if you’re reading this.
- My SQL server was in a domain that had the Certification Authority role installed, but only partially configured, and the web services portion of the role was not installed. This limited my ability to create the proper certificate in the first place.
- I am not using a SQL cluster, and many of the blog posts and forum threads I read were focused on resolving the empty drop down menu in a cluster environment.
For me, this is what worked:
1. Create the certificate request on the SQL server
- Open the MMC console and add the Certificates snap-in for the Local Computer (read the MS technet article above on this for more background)
- Right-click the Personal folder and select All Tasks -> Advanced Operations -> Create Custom Request, then click next on the first screen of the enrollment wizard
- Select “Proceed without enrollment policy” under the custom request section and click Next
- Select “No Template” Legacy Key under the template drop down, leave other values as default and click Next
- On the next screen, click the little down arrow Details button to expand an additional properties window, then click Properties
- Type in the friendly name as the fully qualified host name, for me, this seemed to even require including proper capitalization because my server name was SRVSQL01.domain.loc, so that is that I used. I left description blank
- On the Subject Tab, I added the values in the screenshot below.
- On the Extensions Tab, I added “Key encipherment” under the Key Usage setting box, and “Server Authentication” and “Client Authentication” under the Extended Key Usage (application policies) settings box.
- On the private key tab, under key type, I changed the value from Exchange to Signature.
- Finally, click OK, then click Next back in the Enrollment Wizard window.
- Enter a file name and click Finish.
2. Process the CSR on your Certificate Server
I won’t go into detail here, but you need to copy the file you created to your Certificate Authority server, process and approve the request, then export the binary key file of the certificate. Then copy that exported binary file back to your SQL server.
3. Import the certificate into the local certificate store
Back in your MMC console and Certificates snap-in, you can now right-click on the Personal folder again and select Import. Complete the import wizard using your recently created binary export of the cerficate and the new cert should now show up in the certificates folder under Personal in the Certificates snap-in.
One last step here, and its and important one, on the certificate itself, right-click on the cert name, and select All Tasks -> “Manage Private Keys…”, then give the user the SQLSERVER service runs as Read permission in the security tab.
4. Tell SQL Server which certificate to use
Now, when you follow Microsoft’s instructions and you open the properties of the protocols instance for your SQL Server and view the Certificates tab, you should see the new certificate in the drop down menu! Select the certificate here, click OK, then restart the SQL service. Clients can now use the encrypted connection and you won’t see errors like, “SSL Provider, error: 0 – The certificate’s CN name does not match the passed value”
I hope this saves some of you some time, I spent the better part of 3 days working on this. I went through the whole process many times, and for me, I believe the biggest change I made was changing the Exchange key type to Signature. I don’t know for sure if this is true, but that’s what I’m thinking. In the comments below, let me know if the process works for you or if you have any questions I’ll be glad to help where I can.
On the computer at home I have a pair of 
headphones. They are great bluetooth headphones, but I’ve never taken the time to use the phone features on my 