SQL

Windows Server 2012 Failover Cluster SQL 2012 SSL configuration

A project recently had me working on Windows Server 2012 Failover Cluster SQL 2012 SSL configuration for an international company who had recently provisioned this new Windows cluster to replace an aging cluster already in place.  There are a lot of blog posts out there that gave me bits and pieces of what I needed to configure SSL in SQL on a set of clustered servers, however, I wanted to wrap everything up for you guys on the steps I used to configure the servers.

To my surprise, the supported means to setup SSL certificate configuration for SQL still has one hacking the registry to properly configure the certificate to be seen in the clustered set of servers, which I will show below.  Other than the registry hack, the most tedious part is simply provisioning a certificate.  In this environment, we didn’t care whether or not it was a certificate from a trusted certificate authority, so instead decided to generate SSL certs manually with SelfSSL.  The SelfSSL utility is part of the IIS 6.0 Resource kit and is a simple command line utility that enables one to create certificates from the command line without having to employ another utility or an entire PKI infrastructure simply to create a certificate.

Also as a reference note, there is another more recent and feature rich command line utility is makecert.exe which is contained in the Visual Studio products as well as SDKs for .Net framework.  However, for this process I already had access to SelfSSL and didn’t have the makecert utility available so I simply used SelfSSL to create the cert.  The certificate doesn’t have to be anything fancy, however, I created a 2048 bit cert with the FQDN of the cluster virtual server name which is necessary to make the SSL configuration work correctly.

SelfSSL

The command to create the required SSL cert for the SQL server instance is not difficult at all.  The most important part of this step is that we generate the certificate CN to be the virtual cluster name of the SQL instance. The syntax I used is below:

selfssl.exe /N:CN=TESTSQLCLSTR.TESTAD.Local /K:2048 /V:3650 /T

The command above will create a certificate in the Personal Certificates store and the /T switch also copies it to the Trusted Root Certification Authorities Certfiicates store on the Node of the cluster you are logged into. After generating the certificate on one node, you need to export the certificate to the other node. You can do this via the Certificates MMC snapin.

  • Export the certificate along with the Private key as well as the extended properties of the certificate
  • Create a password for the certificate
  • Copy the .PFX certificate to the other node to the desktop, etc.
  • Import the certificate into the Personal Certificate store of the local computer on the other node.

Once you have the certificate in place, on a cluster, there is a weird little step you have to do to get the certificate to work correctly.  Normally in SQL Configuration manager you would go to the properties of SQL Server Network Configuration >> Protocols for MSSQLSERVER.  However, as you see below, even after I uploaded the cert, I didn’t have a certificate in the dropdown menu of the Protocols properties which normally you would if it can read the certificate.  The problem is that the CN or common name on the certificate doesn’t match the hostname of the box since we need our cluster virtual name instead of the hostname.

certclstr1a

certclstr2

You have to get the Thumbprint from the certificate itself, take the spaces out of it and then enter this value into the Certificate string value found at the following registry location:

HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLServerSuperSocketNetLib

In the details of the certificate, you will see the Thumbprint value.  This is the value that goes in the registry key without spaces.

certclstr3

A quick and easy way I found to take these out besides using notepad is to visit the site:  https://www.miniwebtool.com/remove-spaces/   This site provides a quick and easy web form to paste your text in and remove the spaces.

After you have the certificate in place on both nodes and the registry value in place as well, you should be able to force SQL SSL authentication and the service should start correctly.  Also, test failing the nodes back and forth a few times to make sure everything fails over correctly.

 

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com and has over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, Brandon has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family.

Related Articles

4 Comments

  1. Hi, can you please confirm in .inf file what we may put as CN name . Is it going to be WINODWS CLUSTER name or SQL SERVER Virtual network name. Also please confirm do we need to give any DNS name or not .

    1. Maul,

      In creating the certificate, you need to create the certificate name based on the role server name you configured for the SQL Server role. To easily find what the cluster thinks the role name is, click on Roles >> highlight the SQL role, then on the bottom pane click the “Resources” tab and then expand server name and you should see the Name of the Role. This is the name that needs to be configured for the certificate. Also, DNS needs to match for correct FQDN resolution

  2. What should the certificate look like for a cluster hosting an availability group? Does it need subject alternative names for each cluster member? Will a wildcard certificate work?

    1. Bill,

      I have not tested this with wildcard cert, however, it should in theory work as expected as I believe 2012 failover clusters support wildcard certs. Also, again, I have not tested with availability groups, however, the cert name should match the role service name as assigned.

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.