December 1, 2010 at 6:25 pm
Hi All,
I have a SQL Server 2008 R2 (10.50.1600) cluster running on Windows Server 2008 R2. It is a three-node cluster and there are four instances. We are pre-production and the customer has tasked me with setting up client/server encryption for three of the four instances.
I have researched lots of different KB articles and blogs (see below), but I'm still having trouble installing the cert. I know the basic approach is to:
1. Get a cert that matches the FQDN of each clustered SQL instance (e.g. Instance01.domain.local), using a cert that has "Server Authentication" capability.
1a. Certificate has to be requested and installed by the SQL Server service account (e.g. Domain\svcSQL).
2. In the SQL Configuration Manager, go to "SQL Server Network Configuration", right click on "Protocols for Instance01", and click on Properties. Then click on the Certificates tab, and select the cert I just generated.
3. Repeat this for all nodes in the cluster, for each instance I am adding a cert to.
But it's been a struggle, and though I finally was able to request and receive a cert, it does not show up for selection in the Protocols dialog. What have I done wrong?
Here is what I have done:
1. The CA is in an empty forest root. Because my customer's network staff has not set up autoenrollment for different types of certs (and I do not have domain admin or enterprise admin access), I don't have the abilty to go to the Certificates MMC (Local Computer-->Personal-->Certificates) and right-click select "Request New Certificate". When I do this I see that I have permission to none of the default templates.
2. So I browse to the CertSrv website on the CA server and click "Request a certificate", then "Advanced Certificate Request", then "Create and Submit a Request to this CA".
3. The only cert templates I have access to are "User" and "Web Server". I select "Web Server" but note that it does not give me the ablity to export the key, which I will need to be able to do (each node in the cluster needs to have the same cert installed for the SQL instance).
4. If I go ahead and request the cert, it downloads and installs in my Personal-->Personal-->Certificates folder.
5. Now when I go to SQL config manager (as described above) the cert doesn't show up. No matter where I put the cert (e.g. in Local Machine-->Personal-->Certificates) it does not appear for selection in SQL config manager.
So it occurs to me that one of the following may be true:
1. The "Web Server" template is the wrong type of SSL cert. The requirements for the cert, according to BOL, are as posted below. So I had the network services staff create a copy of the "Web Server" template and enable "Export". (I am still waiting for the template to show up for me, actually).
2. The CA server isn't set up correctly. (Very possible, since it is a new server set up with all defaults. And the network admins aren't used to Windows 2008 R2).
3. There is something wrong with SQL Server (Very unlikely, as everything else is behaving exactly as I expect).
SQL Server requires the following certificate type:
For SQL Server to load a SSL certificate, the certificate must meet the following conditions:
• The certificate must be in either the local computer certificate store or the current user certificate store.
• The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
• The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
• The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
• The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
• SQL Server 2008 R2 supports wildcards certificates. For more information, see KB258858.
Here are the articles / blog posts I have read in the last day or so:
Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067(SQL.100).aspx
SQL: SSL and SQL Server 2008 – Creating the Certificate
http://nickstips.wordpress.com/2010/09/08/sql-ssl-and-sql-server-2008-creating-the-certificate/
How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms191192.aspx
Enabling Certificate for SSL on a SQL Server 2005 Clustered Installation
Implementing and Administering Certificate Templates in Windows Server 2008
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3C670732-C971-4C65-BE9C-C0EBC3749E24
So... any ideas?
December 30, 2010 at 11:38 am
I have the same problem. If I find a resolution I will let you know.
always get a backup before you try that.
December 30, 2010 at 12:34 pm
Stephen Harris-233385 (12/30/2010)
I have the same problem. If I find a resolution I will let you know.
Hi, sorry I didn't follow up here. I found a blog post that explained why the cert wasn't showing up. On a cluster, the instance name doesnt match the hostname. You have to go into the registry on each node and add the cert's "thumbprint" to a reg key. I will post links when I get a chance.
February 1, 2011 at 5:18 pm
I had posted a detailed writeup of this issue on the Technet forums. A Microsoft employee responded and pointed me to this blog post. Apparently the reason the cert is not showing up is that the name of the cert does not match the hostname of the server (this is expected because it is a clustered instance).
"Note that if you try to select the corresponding SSL certificate on the "Certificate" tab of the SQL Server Protocols properties, you will see that the installed certificate does not show up. This behavior is a known issue in a clustered installation. SQL Server configuration manager search by default on the local computer personal certificates store and tries to match an existing certificate with the fully qualified domain name (FQDN) of the local computer. Since the installed certificate is not associated to the cluster node FQDN but with the virtual SQL Server FQDN, the corresponding certificate is not shown on the GUI. To make sure what certificate is in use for SSL check the corresponding thumbprint string on the abovementioned registry key."
This explains why the cert wasn't showing up, but it does not solve the issue of my not being able to enroll the right type of cert. In fact, I am still not sure which type of cert I need to use. This, however, is probably more due to a lack of permissions than anything else. I will post back here if I ever get it figured out.
May 19, 2011 at 7:24 pm
I had the exact same problem. Not with the cluster cert but not having the ability to generate the cert I needed.
The only resolution was to make the SQL service account a Domain Admin. Once I did this I was able to start request the cert I wanted and start SQL.
I tried EVERYTHING else. This is the only thing that worked for me.
May 20, 2011 at 6:39 am
I wanted to clarify my last post. My goal was to create a SSL connection from a Web Server to a Server 2003 R2 server running SQL 2005 SP2. To do this I used a self created CA hosted on an internal server and did the following (I was logged in as a Domain Admin account not the SQL service account:
browse to http://<server>/certsrv/
Click "Request a Certificate"
Create and submit a request to this CA
Change Certificate Template to "Web Server"
In the Name field enter the fully qualified domain name of the server. Fill in all other fields as necessary.
CSP should be "Microsoft RSA SChannel Cryptographic Provider"
Check off "Store Certificate in the local computer certificate store"
Click Submit
Click to install the certificate.
GO to Start - Programs - Microsoft SQL Server 2005 - Configuration Tools - SQL Server Configuration Manager
Expand "SQL Server 2005 Network Configuration" and right click "Protocols for MSSQLSERVER"
On the Flags tab, leave Force Encryption "NO"
Click the Certificate Tab and from the drop down menu select the cert that has the server name and click apply.
You will then need to restart the SQL Server Service to apply the cert.
Once the service has been restarted, view the SQL Logs from within Management Studio. There will be an entry there which states "The Certificate was successfully loaded for encryption"
I set Encryption to NO because I specify the encryption in the connection string of the Web App using Encrypt=YES. I also had to import the root chain to the SQL and Web Server so that it trusted the certificate created from my internal server.
Browse to http://<server>/certsrv/
Click - Download a CA certificate, certificate chain or CRL
CA Certificate should read "Current [Server]
Encoding Method - BASE64
Click "Download CA certficate chain"
Name the file RootChain.p7b
From the server, launch MMC and add the "Certificates" snap-in and select "Computer Account" then "Local Computer"
Expand "Trusted Root Certification Authorities" - "Certificates"
Right Click "Certificates" and click "All Tasks - IMport"
Browse to the location of the Root Chain cert and select it. Click Next
Choose "Place all certificates in the following store"
Click Finish.
The CA server should now appear in the Trusted Root Certification Authorities
I then had to build a Server 2008 R2 server using SQL 2008 SP2. I followed the exact same steps (again logged in as a Domain Admin not the SQL service account). SQL would not start when I applied the cert. I tried everything to get it to work and nothing would. Finally after 2 days I logged in as the SQL service account and requested a cert. The problem was that when logged in as the service account, my only option for the cert template type was USER or EMF something. I needed "WEB SERVER".
By granting the SQL service account Domain admin rights I was then presented with "Web Server" as a cert option. I created the Cert and, applied it to SQL and everything worked fine. No idea why it worked but it is some sort of Server 2008 security thing.
Anyone have any ideas? I dont want to leave my SQL account as a domain admin.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply