Need help setting up ssl on SQL Server 2008 R2

Question

I need to setup a self signed cert on a particular instance of SQL Server 2008 R2.  I have created a self signed cert using makecert and imported into the Personal store using the Certificates MMC Snap In.  The problem I am having is that I can't select the certificate in the SQL Server Configuration Manager.  The list of certs is empty.

How do I enable a self signed cert for a particular instance of SQL Server 2008 R2?

Answer 1

have you read the following MSDN article?

Encrypting Connections to SQL Server

and the following KB (for 2005 but it should work for 2008R2 too):

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console

and look at this thread for possible other issues when installing SSL Certificate into SQL Serve r 2008:

SQL Server 2008 SSL Certificate List Blank

Answer 2

Thanks for the help Daniel...

I had already read 2 of these articles:

Encrypting Connections to SQL Server:

I am stuck at "Install a certificate in the Windows certificate store of the server computer."

This article provides no additional information as to creating the self signed certificate or how to install it.  This leads us to the next link you posted.

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console:

These instructions are not for Windows Server 2008 R2 and I got stuck at "Use the MMC snap-in to install the certificate on the server:"

1. Click to select the Personal folder in the left-hand pane.   <-- This step is valid
2. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate....   <-- This step is valid
3. The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".  <-- This step is not valid

There is no choice to select "Certificate type is "computer.  I am presented with "Select Certificate Enrollment Policy".  I have two choices: 1) Active Directory Enrollment Policy, 2) Configured by you.

If I select 1) Active Directory Enrollment Policy, I am presented with a message that says "Certificate Types are not Available"

If I select 2) Configured by you, I am prompted to enter the "Enrollment Policy URI".  I am not sure what to enter here.

Because the instructions provided in the article do not match what I am presented with on screen, I searched for another way to create the certificate.

I found the following article:

MSDN article

This article describes creating a cert using the makecert utility.

Regarding this article:

SQL Server 2008 SSL Certificate List Blank:

From the linked article: If you could not see the certificate, it means that your certificate is not a valid certificate for SQL Server. To SQL Server instance, a valid certificate must be: "The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer".

Again, it is possible that I have not created the self signed certificate properly because the instructions in this link How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console are not valid for Windows Server 2008 R2 so I created the certificate using makecert:

C:\Program Files (x86)\Microsoft Office\Office10>makecert -r -n "CN=myhost.mydomain" -b 01/01/2000 -e 01/01/2050 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\mycert.cer

I am thinking that either I am not using the correct version of makecert or the correct parameters to it or I am not installing the .cer file properly although it is showing up properly in the Personal --> Certificates in the Certificates MMC Snap In.

Any help is appreciated...

Answer 3

on a standalone Windows Server 2008R2 you can manage certificate store by using the following steps:

1. run mmc.exe

2. File -> Add/remove Snap-in

3. Available snap-ins - Certificates -> Add

4. select either "My user account", "Service Account", "computer account"

   following the above instruction you should select here "my user account"

than you should see the different certificate stores and you can import the certificate (.pfx) and than reference this from SQL Configuration Manager

There's another thread with more detailed instructions to setup SSL on SQL Server

SQL Server SSL and Certificate. Certificate not Listed. Certificate's CN Name does not match

or

how to secure the sql database with ssl certificate

Answer 4

The SQL Server is in a domain, it is not a stand alone server.  I just imported the certificate for "My user account" successfully.  I still cannot see the certificate in Sql Server Configuration Manager.

from: SQL Server SSL and Certificate. Certificate not Listed. Certificate's CN Name does not match

"I was finally able to get SQL Server to list a certificate in the drop down list it provides by getting a brand new certificate for "domainName.domainName.com" which is my server's FQDN."

I have already created the certificate using the FQDN.

From this link: how to secure the sql database with ssl certificate

the links in that article relate to connecting from the client which I am not at that step yet.  The other two articles linked are the articles that we have previously discussed as being incomplete or not applicable to Windows Server 2008 R2.

Does anyone know the exact steps to take to configure a self signed certificate on Windows Server 2008 R2 in a Domain environment for SQL Server 2008 R2?