locked
Unable to set (ssl) certificate on a SQL Server 2012 clustered instance RRS feed

  • Question

  • Hello everyone!

    I'm trying to encrypt the SQL Server communication with SSL but I can't add the certificate in the configuration manager. I've found and tried a lot of different explaination but none of them worked. I'll described what I've done and hope someone will point out what I'm missing.

    Here is my situation:

    - SQL Server 2012 Enterprise Edition. Instance name = INSTANCE, FQDN =  SQINSTANCE.mydomain.com. The instance is running under a customized service account: mydomain\sql_sa

    - Two cluster nodes running Win Server 2008R2: NODE1.mydomain.com and NODE2.mydomain.com. Cluster itself is CLUSTER.mydomain.com

    What I've done:

    1) Asked the team in charge to generate a certificate issued to "SQINSTANCE.mydomain.com" with aliases to "NODE1.mydomain.com", "NODE2.mydomain.com" and "CLUSTER.mydomain.com". I get a certificate with "p7b" as extension

    2) Connect on "NODE2.mydomain.com" with account "mydomain\sql_sa". Opened MMC and added the certificate under "Personnal" folder. I tried to add it with "Current user" and "Local computer" settings. Saw both on internet since I use a specific service account

    3) Get the thumbprint of the certificate and add it under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.INSTANCE\MSSQLServer\SuperSocketNetLib\Certificate. (I triple checked to remove blanks or special characters)

    4) Reboot the node

    5) Open the SQL Server Configuration Manager, go to the network properties. Certificate does not appear in the list

    I tried to check with certutil and saw the certificate in the output. Some guys talked about some private key but I don't see this particularity in my situation. I tried to check if the certificate is valid and, according to the criterias, it is.

    Does anyone can help me with this?

    Tuesday, February 4, 2014 2:29 PM

Answers

All replies

  • Hi,

    Are you sure you've got the certificate correct?  http://msdn.microsoft.com/en-us/library/ms191192.aspx

    To use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.<your company>.com and test2.<your company>.com, and you have a virtual server named virtsql, you need to install a certificate for virtsql.<your company>.com on both nodes. You can set the value of the ForceEncryptionoption toYes

    In your case, shouldn't it be created for CLUSTER.mydomain.com?


    Thanks, Andrew
    My blog...

    Tuesday, February 4, 2014 3:38 PM
  • Hi Andrew,

    Thanks for your reply. I read this doc and tried it but without success.

    Concerning your remark, what is behind the "virtual server named virtsql"? For me, it's the "SQL Server Network name" mentionned during the installation of SQL Server. "virtSQL" suggested me this. For you, it's the cluster name instead?

    For the validity of the certificate itself, I found a list of criteria and they were all correct (except for the private key as I said in my first post)


    • Edited by el_grom Wednesday, February 5, 2014 12:16 PM
    Wednesday, February 5, 2014 9:17 AM
  • A little update:

    I tried the same scenario with a certificate delivered for "CLUSTER.mydomain.com" (only) and same behavior happened. Can't see it in the SSCM

    Thursday, February 6, 2014 12:30 PM
  • Hello,

    This behavior is a known issue in a clustered installation. You can verify whether your certificate loaded successfully by review the SQL Server Error log: The certificate was successfully loaded for encryption.

    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.

    Reference:http://blogs.msdn.com/b/jorgepc/archive/2008/02/19/enabling-certificates-for-ssl-connection-on-sql-server-2005-clustered-installation.aspx

    http://blogs.technet.com/b/mbaher/archive/2006/12/16/enabling-ssl-in-sql-2005-cluster.aspx

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Monday, February 17, 2014 9:13 AM
    Wednesday, February 12, 2014 7:30 AM