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.

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 


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

 

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 titled “SQL Server 2019: What’s New – New and Enhanced Features“.

SQL Server 2019: What's New? - Online Course
(Lifetime Access – Certificate of Completion)

Via this course, you will learn about all the major new features and enhancements in SQL Server 2019. Via a 3-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

 

 

See More

Check out DBA Security Advisor, a SQL Server security tool to assess your SQL Server instances against a rich set of security checks and get security best practice recommendations.

 

Featured Online Courses:

 

Other SQL Server Security-Related Articles:

 

Check Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

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

Loading... 

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

© SQLNetHub