How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster

Hi folks. Because enabling an SSL certificate on a SQL Server Failover Cluster, thus enabling encryption for your data in-motion, is a little bit tricky, I decided to write this article in order to help you out a bit, since in the past, I encountered the same difficulties.

So, please read on to learn more about how you can enable an SSL Certificate-Based encryption on a SQL Server Failover Cluster.

 

Step 0 – Preparation: Create a Proper SSL Certificate

The first step prior to start doing anything on your database servers, is to make sure that your CA has generated a proper SSL certificate for your SQL Server Failover Cluster. To this end, must ensure that the CN (common name) and SAN (subject alternative names) are correct.

To better undestand this whole situtation, we need an example.

So, consider that we have a Windows SQL Server Failover Cluster that consists of 2 nodes, and has the below details:

  • Cluster management name: cl-sql.example.com
  • Node 1 name: sql-node1.example.com
  • Node 2 name: sql-node2.example.com
  • SQL Server Instance virtual name: sqlinstance.example.com

 

A proper SSL certificate would have the below characteristics:

  • CN: cl-sql.example.com
  • SAN:
    • sql-node1
    • sql-node1.example.com
    • sql-node2
    • sql-node2.example.com
    • sqlinstance
    • sqlinstance.example.com

 

So, you generate a single SSL certificate that will be used on all nodes that participate in the Failover Cluster.

 

Step 1: Import the certificate in Windows for Local Computer (not user)

You can do this via Microsoft Management Console (mmc). The correct path to import the certificate is:
Console Root\Certificates (Local Computer)\Personal\Certificates

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

Step 2: Set Full-Control Permissions on the Certificate for the Right User

The next step is to give full control to your SQL Server instance’s service account on the certificate.

To do this, you right-click on the certificate, go to “All Tasks”, “Manage Private Keys…”, and then you add the SQL Server service account and give it Read Access.

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

Note: In this case, since I work on a VM, I’m not using a domain user as the service account for SQL Server which by the way, using a domain user with least privilege is the recommended security best practice.

 

Step 3: Get the Certificate’s Thumbprint

Double-click on the certificate, go to the “Details” tab, and click on the “Thumbprint” field on the certificate’s list of fields. Then, copy the value for that field displayed below.

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

Paste the value in a new text file, save the file and exit.

At some point you will get a warning and will be told that the file contains characters in Unicode format…

What you do: You ignore the warning, you click OK and the text file is created.

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

 

SQL Server 2019: What's New - Course on Udemy and Skillshare by Artemakis Artemiou

SQL Server 2019: What’s New

In this course, you will learn about all major new features and enhancements in SQL Server 2019. Via a 1-hour journey with a combination of lectures, live demonstrations, quizzes and a practice test, you will get up to date with SQL server 2019 in no time! (learn more…)

Enroll to this course on Udemy

 

Enroll to this course on Skillshare

 

 

Step 4: Get the Certificate’s “Clean” Thumbprint

You re-open the text file you created in step 3, you remove the first character in case it is a question mark (?) and also remove all empty spaces.

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

You then save the file for future reference and also copy the “clean” thumbprint value from the text file to the clipboard.

 

Step 5: Edit Windows Registry

You open Windows Registry (start – run: “regedit”) and navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[*Instance ID]\MSQLServer\SuperSocketNetLib,

Then, there you will find a key named “Certificate“. You double-click on it and paste in the “Value data” text field the certificate’s processed “thumbprint” value you copied earlier from the text file, after you removed the question mark and all empty spaces.

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

Note: [*Instance ID] consists of the SQL Server product build (i.e. MSSQL14 is for SQL Server 2017, MSSQL15 for 2019, etc.) and the SQL Server instance name. In my case, my instance ID is “MSSQL14.SQL2K17“.

 

That’s it! You now need to perform the exact same procedure to all nodes participating to the Failover Cluster, and in order for changes to take effect, you need to restart SQL Server by performing failovers between all participating nodes.

 

Step 6: SQL Server Configuration Manager

In SQL Server Configuration Manager, you navigate to “SQL Server Network Configuration”, and then, for the SQL Server instance you want to enable SSL encryption, you right click on “Protocols for [instance name]” and enter its Properties.

If you go to the “Certificate” tab, even though the certificate is loaded for encryption, you won’t see the certificate there due to the fact that this is a failover cluster. So, the only thing you can control from there, is in the “Flags” dialog, where you need to set the “Force Encryption” flag.

 

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

 

So this is the point, where you decide whether you set this flag to “Yes” which means that all connections will be forced to go through the encrypted channel with the SQL Server instance, or if you set the flag to “No”, it means that you will both allowing encrypted and unencrypted connections.

By the time you decided to use an SSL certificate, it is recommended to set this flag to “Yes” and tackle any issues with any applications. Of course, just with any changes, you will need to test the entire process on a Test Environment.

 

Step 7: The Verification

The last step, is to verify that the SSL certificate is successfully loaded for encryption, each time your SQL Server instance starts. You will need to test this on all Failover Cluster nodes. Therefore, you will need to failover the SQL Server instance one-by-one, on all cluster nodes and check the SQL Server log.

Right after you start the SSL-enabled SQL Server instance, if the SSL certificate is successfully loaded, you will see somewhere in the log, a message saying the below:

“The certificate [Cert Hash(… ‘[certificate thumbprint value]’ was successfully loaded for encryption”

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

Alternatively, you can check the encryption option for all current connections on your SQL Server instance, using the below T-SQL script:

SELECT session_id, encrypt_option
FROM sys.dm_exec_connections;
GO

Here’s the outcome of the above T-SQL script’s execution on my VM:

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster - Article on SQLNetHub

Cool! As you can see, all connections (sessions) on my SQL Server instance, are encrypted!

 

What about SQL Server 2019?

I have good news about SQL Server 2019 and SSL certificate-based encryption. SQL Server 2019 has its own Certificate Manager built-in SQL Server Configuration Manager 2019. Therefore, from SQL Server onward, you do not need to go through this whole process to enabled SSL certificate-based encryption for SQL Server failover clusters. You just import the SSL certificate on each node using SQL Server’s Certificate Manager!

If you want to learn more about all the cool new features in SQL Server 2019, then you should definitely check my course at Udemy or Skillshare, titled “SQL Server 2019: What’s New”.

 

SQL Server 2019: What's New - Course on Udemy and Skillshare by Artemakis Artemiou

SQL Server 2019: What’s New

In this course, you will learn about all major new features and enhancements in SQL Server 2019. Via a 1-hour journey with a combination of lectures, live demonstrations, quizzes and a practice test, you will get up to date with SQL server 2019 in no time! (learn more…)

Enroll to this course on Udemy

 

Enroll to this course on Skillshare

 

Other SQL Server Security-Related Articles:

 

Check Also:

 

Check our latest software releases!

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading... 

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

 

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.