question

AnnWild54-1008 avatar image
0 Votes"
AnnWild54-1008 asked AmeliaGu-msft answered

SQL Server Failover Clustering cetificate

I have a 3 node cluster (active-passive) with a separate instance on each one with an assigned certificate assigned to each instance on that node. Here's my scenario: Inst1 is on node1 and if I have a failover on Inst1 to node 3 the certificate will not auto-update in the dropdown properties certificate box. I have to manually select the certificate when the inst1 failed over to node3. The certificate is not following the node if an failover occurs. Is this normal with SQL failover clustering or is there a way to auto-update the certificate where the instance fails over to another node. Any resource would help to explain this and possible solution would help. Thank you. Environment: SQL Server 2012

sql-server-generalwindows-server-clustering
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

According to your description, seems the issue is SQL services related, so I add the sql-server-general tag for you. Thanks for your understanding!

Best Regards,
Anne

0 Votes 0 ·

Thanks - hopefully I can get some feedback and resource on this. I was perplexed when this happen. The instance failed over but it did not come up because the certificate was on the other node where the failure occurred. Had to manually import the certificate on the passive node for the instance to come back up. So in other words - why can't the certificate follow where the instance fails over too without manually selecting the certificate again.

Thanks.

0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi AnnWild54-1008,

Welcome to Q&A.
Have you copied the thumbprint value of the certificate to a Notepad window to the key HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate on all the nodes?
The key contains a property of the certificate known as thumbprint that identifies each certificate in the server. In a clustered environment, this key will be set to Null even though the correct certificate exists in the store. To resolve this issue, you must take these additional steps on each of your cluster nodes after you installed the certificate to each node):

  1. Navigate to the certificate store where the FQDN certificate is stored. On the properties page for the certificate, go to the Details tab and copy the thumbprint value of the certificate to a Notepad window.

  2. Remove the spaces between the hex characters in the thumbprint value in Notepad.

  3. Start regedit, navigate to the following registry key, and copy the value from step 2:
    HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate

  4. If the SQL virtual server is currently on this node, failover to another node in your cluster, and then reboot the node where the registry change occurred.

  5. Repeat this procedure on all the nodes.

Please refer to this article for more details.
Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.