Configure SSL on a SQL Failover Cluster

Pre-Requisites

  • Have an SSL cert where the Common Name (CN) of the cert matches the name of the SQL instance that needs to have SSL enabled. A wildcard cert will also work.
  • Ability to modify the registry on each of the cluster nodes

Step 1 of 2 (Import the Certificate into Windows)

  • Hold down the Windows key and push R to open a run window
  • Type mmc.exe and hit enter
  • Click the File menu and select Add/Remove Snap-in…
  • Select Certificates from the left column and click Add >
  • Select Computer Account and click Next when prompted

Screenshot1

  • Click Finish
  • Click Ok

  • On the left expand Certificates (Local Computer) and select Personal

Screenshot2

  • Right-click the white-space in the middle pane and click All Tasks and then select Import…

Screenshot3

  • Click Next
  • Browse to the location of your SSL certificate
  • Click Next
  • Ensure that the Personal Certificate store is chosen and click Next
    • If prompted, make sure to mark the private key as exportable
  • Click Finish
  • The cert should now appear in the list
  • Right-click the cert and click All Tasks and then select Manage Private Keys…

Screenshot4

  • Click Add… and find the account that is running the SQL instance
    • If this account is not known then open services.msc and locate the SQL Server service. The Log On As account is that account that needs to specified at this step.
  • Click Ok
  • Click the account that was just added
  • Un-check Full Control. For this function the account only requires Read access.

Screenshot5

  • Click Ok
  • Leave the certificates MMC open as it will be used in the next step

Step 2 of 2 (Configure the SQL Instance to use the Certificate)

As of SQL 2008 Microsoft has a built-in method for handling certificates in SQL Configuration Manager. Unfortunately SCM wasn’t built to handle certificates for SQL when using Failover Clustering. Many admins have wasted a lot of time attempting to get their certificates to populate in SCM only to find out that it will never work. Due to this limitation the certificate needs to be associated with the instance via the instances registry configuration.

  • Open the certificates MMC if needed
  • Double-click the certificate that will be used for SQL
  • Click the Details tab
  • Scroll down until the Thumbprint field is visible
  • Highlight the text in the Tumbprint field and copy/paste it into notepad

Screenshot6

  • Remove all spaces in the notepad document
  • Save the notepad document
    • Click Yes to the Unicode prompt
  • Re-open the notepad document
  • Notice that a “?” has been appended to the beginning of the file, delete the question mark.
  • Copy the contents of the notepad document
  • Hold down the Windows key and push R to open a run window
  • Type regedit and hit enter
  • Browse to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib
  • Double-click the certificate value and paste the contents of the notepad document into the window

Screenshot7

  • Click Ok
  • At this point, 1 node in the cluster is configured to use the SSL certificate. Feel free to start the instance on this node to test before repeating the steps on all other cluster nodes. Once testing has been successfully completed all of the steps on each of the other cluster nodes. Each node requires the certificate to be installed and the registry to be configured with the installed certificate.

Testing

  •  To test SSL open SQL Management Studio and click the Options button
  • Click the Connection Properties tab
  • Check the box that says Encrypt Connection

Screenshot8

  • Click Connect
  • If it connects then SSL is configured properly! Congrats!
    • If an error regarding the login process is received then reference the troubleshooting section of this article.

Troubleshooting

  • TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL.
    • This error means that the data copied from the notepad document contained hidden formatting. Ensure that each and everyone of the steps was followed properly. This includes, and requires, saving the notepad document and re-opening the document. Also, don’t use notepad++ for this step.
  • A connection was established with the server, but then an error occurred during the login process. Provider: SSL Provider, error:0 – The target principal name is incorrect.
    • This errors typically means that the name that is being specified in SQL Management Studio doesn’t match the CN on the certificate. If the certificate specifies FQDN then FQDN will be required to connect.

One thought to “Configure SSL on a SQL Failover Cluster”

  1. Thank you for this, just wasted 3 hours of my live trying to workout why the hell the registry key wasn’t working, your comment about the “?” has resolved it. Thank you ever so much!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.