Thursday, April 12, 2012 5:45 PM
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?
Thursday, April 12, 2012 7:55 PM
have you read the following MSDN article?
and the following KB (for 2005 but it should work for 2008R2 too):
and look at this thread for possible other issues when installing SSL Certificate into SQL Serve r 2008:
- Edited by Daniel_Steiner Thursday, April 12, 2012 7:59 PM
Thursday, April 12, 2012 8:55 PM
Thanks for the help Daniel...
I had already read 2 of these articles:
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:"
- Click to select the Personal folder in the left-hand pane. <-- This step is valid
- Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate.... <-- This step is valid
- 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:
This article describes creating a cert using the makecert utility.
Regarding this article:
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 184.108.40.206.220.127.116.11.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...
- Edited by davestech Thursday, April 12, 2012 9:05 PM
Thursday, April 12, 2012 9:05 PM
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
- Edited by Daniel_Steiner Thursday, April 12, 2012 9:11 PM
Friday, April 13, 2012 2:09 PM
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.
"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.