locked
Enabling Encryption on SQL 2008 R2 Cluster RRS feed

  • Question

  • 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
    http://blogs.msdn.com/b/jorgepc/archive/2008/02/19/enabling-certificates-for-ssl-connection-on-sql-server-2005-clustered-installation.aspx

    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?
    Thursday, December 2, 2010 1:29 AM

Answers

  • Hi,

    >>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?

    As stated in the blog post Enabling Certificate for SSL on a SQL Server 2005 Clustered Installation, you will find that the installed cerfiticate won't show up in the Certificate tab of the Protocols dialog box in the cluster environment. The detailed reason from the blog post, see below:

    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 mach 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.

    Please let me know if you have more quesions.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, December 3, 2010 9:25 AM

All replies

  • Hi,

    Thanks for your post!

    I will test it on my environment and response to you once I have update.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, December 2, 2010 7:52 AM
  • Hi,

    >>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?

    As stated in the blog post Enabling Certificate for SSL on a SQL Server 2005 Clustered Installation, you will find that the installed cerfiticate won't show up in the Certificate tab of the Protocols dialog box in the cluster environment. The detailed reason from the blog post, see below:

    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 mach 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.

    Please let me know if you have more quesions.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, December 3, 2010 9:25 AM
  • Thanks ChunSong,

    I will check this out and get back to you on Monday.  I appreciate your help.

     

    Friday, December 3, 2010 10:23 PM
  • Hi ChunSong,

    Sorry I did not get back to you sooner. 

    Your suggestion did in fact work around the "Certificate not showing up" issue.  However, I have other problems preventing my task from completing successfully. 

    We determined that the reason the certificate template was not showing up in the CertSrv website was because it was a Windows 2008-compatible template.  Apparently Microsoft is deprecating the CertSrv web enrollment method.  We recreated the template as Windows 2003-compatible and then I was able to request and obtain 4 certificates: one for each instance in the cluster.

    I requested the certs while logged in as the service account for SQL Server.  The certificates were automatically installed in this account's "Current User--> Personal" store.

     Of the four instances, I was only able to successfully configure one of them to use a certificate on all three nodes.  All of the other ones encountered errors and refused to start. 

     

    Log Name:      Application

    Source:        <SQL Server Instance>

    Date:          12/7/2010 8:43:47 AM

    Event ID:      17182

    Task Category: Server

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      <Hostname>

    Description:

    TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.

     

    Log Name:      Application

    Source:        <SQL Server Instance>

    Date:          12/7/2010 8:43:47 AM

    Event ID:      17182

    Task Category: Server

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      <Hostname>

    Description:

    TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

    I re-attempted this using a variety of methods, including a full reboot of each node.  But actually the effects were immediate each time I installed (or removed) the certificate.  (Also, I noticed in some cases that modifying the registry on one node automatically updated the registry on the other two nodes). 

    In the end the customer ran out of patience and decided we would proceed without encryption for the time being; we might have to open a support ticket when we revisit this in a month or so.  Unless, of course, these errors mean anything to you. 

    I am curious why this only affected SOME of the instances.  For one of them, the SSL cert was installed and picked up successfully by the SQL instance:

    Log Name:      Application

    Source:        <SQL Server Instance>

    Date:          12/6/2010 3:11:48 PM

    Event ID:      26013

    Task Category: Server

    Level:         Information

    Keywords:      Classic

    User:          N/A

    Computer:      <Hostname>

    Description:

    The certificate [Cert Hash(sha1) "<Certificate Thumbprint>"] was successfully loaded for encryption.

     

    ...But not the other instances.  Have you ever seen anything like that?

    Monday, December 13, 2010 3:33 PM
  • Hi,

    Thanks for your update!

    For the issue you encountered, I think you'd better open a support case with Microsoft CSS for further research (http://support.microsoft.com).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 14, 2010 2:24 AM
  • Hi

    I am running into this same issue.  Has anyone come up with a fix/workaround?

    I have two instances in a two-node cluster.  I set up the Instance1 for  SSL encryption and once I updated the registry Certificate value in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib with the Thumbprint from the certificate, Instance1 is working fine. 

    Unfortuantely I cannot start the 2nd instance. It fails with:

    TDSSNIClient initialization failed with error 0xd, status code 0x38.

    I checked the Certificate registry value for the second instance and it contains the Thumbprint from Instance1's certificate.  I really do not want this second instance to be configured with SSL encryption.  I clear out the value in the registry and try to restart, once again the Certificate field in Instance2's registry entry has been updated with the Certificate thumbprint for Instance1 and Instance2 will not start.

    I even tried setting up a second certificate that is specific to Instance2.  I update the registry value for Instance2 with the new certficate's Thumbprint.   I try to start Instance2.  Once again the registry value is overwriten with the first instance's certificate thumbprintit and then Instance2 fails to start.

    Please let me now if anyone has run into this scenario and can suggest a solution.

    Thanks!

     

    Tuesday, September 13, 2011 5:41 PM
    • Edited by RBRussell Thursday, October 19, 2017 2:42 AM
    Thursday, October 19, 2017 2:42 AM