Step-by-Step Guide to Enabling SQL Server Encryption: Generating Certificate Requests

Edition: Saturday, April 20th, 2024

For a DBA, securing SQL Server is a critical aspect of the job.  Securing data at rest, data in motion, and data in use (in memory) are all areas a potential attacker could focus on.  This month, I've focused on "securing data in motion" using trusted certificates.  We've built our Certificate Authority and are almost ready to generate the certificate for use by SQL Server.  Here's what my sandbox environment looks like so far:

• DC1 - Windows Server 2022 Domain Controller
• CA1 - Certificate Authority
• SRV2 - Client machine with SQL Server Management Studio 20.1 installed.
• SRV3 - SQL Server 2022 Developer Edition with CU11 installed

All machines are a member of the HOMELAB.LOCAL domain.

This week, we'll cover certificate requirements, generating the certificate request, installing the certificate, and configuring SQL Server to force encryption.

Let's jump in.

SQL Server Certificate Requirements

 I'll highlight the requirements for a single instance of SQL Server.  However, if you're enabling encryption for a failover clustered instance or availability group there are additional requirements.  See Certificate requirements for SQL Server - SQL Server | Microsoft Learn for a complete reference.

Explanation:

• Trusted Certificate Authority: Ensures that the certificate is recognized as legitimate by both the server and client machines.
• Key Usage: Specifies the purposes for which the certificate's key can be used, crucial for establishing encrypted connections.
• Enhanced Key Usage (EKU): Further defines the specific applications for the certificate, with "Server Authentication" being necessary for TLS.
• Cryptographic Provider: Ensures the certificate uses a secure method and size for its cryptographic keys.
• Subject Name: This needs to align with the domain name of the server to avoid trust errors.
• Expiration Date: The certificate needs to remain valid for the duration of its use to maintain encrypted connections.
• Machine Accessibility: The certificate must be readily accessible by SQL Server without requiring user input.

Generating the Certificate Request (CR) Using PowerShell

There are a few different methods for generating the CR. 

• Open certlm.msc and request a new certificate directly.  As a DBA, you may encounter permission issues with this method. 
  • 
• Create a custom request.  This is the method you'd likely need to follow.  Once the CR file has been created, you'd then use the Web Enrollment site to create the certificate, or send it over to the Windows admin team to generate the certificate for you. 
  • 
• Script the request using PowerShell.  This is the method we'll follow because it's easy to repeat.  

Step 1:

I discovered there's already a script to perform the CR -Creating and Setting SQL Server Certificates with PowerShell (schottsql.com).  I won't repeat everything here, but if you'd like more details on the script, check out the post.

Using remote desktop, or the VM console, I'll connect to SRV3 (my target SQL Server machine).

Step 2:

Copy the script, located here into a PowerShell ISE window (open it as an administrator).  

Step 3:

Modify the Location, State, OU, and Company parameters.

Step 4: 

Run the script.  The certificate request file will be located in C:\CertificateRequest.

Step 5:

Open the .csr file using a text editor (notepad is fine).  Next, open the CA web enrollment site and select Request a certificate.

Step 6:

Choose submit and advanced request.

Copy and paste the contents of the .csr file into the Saved Request box.  Choose Web Server as the Certificate Template.

Click submit.

Step 7:

On the next page, select Base 64 encoded and then Download certificate.

The certificate will be in the Downloads folder.  Next, we'll install it.

Installing the Certificate

Now, we need to install the certifice into the local computer certificate store before SQL Server can use it.  

Step 1:

Navigate to the downloads folder and double-click the new certificate.  Click open.  Take a second to review the properties under the details tab.

Step 2:

Click Install Certificate.  Choose the Local Machine, under store location and click next.

Step 3:

Select place all certificates in the following store and then click Browse.  Choose the personal store.  Click next and then finish.  You should receive a message stating the import was successful.

Next, we need to give the SQL Server service account permissions on the private key.

Set Permissions

Step 1:

Open certlm.msc.  Navigate to the personal certificate store.  Locate the new certificate, right-click, and choose All Tasks, and then click Manage Private Keys.

Step 2:

Click Add.  Enter the SQL Server service account then click Ok.  The account I'm using is HOMELAB\svcsqlserver.

Step 3:

Click ok.

Next, we'll configure SQL Server to use the certificate and force encryption.

Configuring SQL Server Encryption

Step 1:

Open SQL Server Configuration Manager.  Right-click Protocols for MSSQLSERVER and then click properties.

Step 2:

Select the Certificate tab and then select the new certificate.

Step 3:

Click the Flags tab, choose Force Encryption, and select Yes from the menu.  Click OK and then restart the SQL Server service.

Next, verify encryption.  I'm using SRV2 as my client with SSMS installed.

Verify Encryption

Open SSMS from a client machine, connect to SQL Server and open a new query window.

Run the following query to ensure encrypt_option = true.

SELECT session_id, net_transport, encrypt_option,  auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid 

Conclusion:

Next week, we'll use a tool, Wireshark, to review network traffic.  We'll review and compare traffic using encryption vs what it looks like when data is unencrypted.  If you're following along, you'll need a machine with SQL Server 2022 Developer Edition installed and joined to the same domain as the CA. If you run into issues, no worries, revert to your snapshot and give it another try.

That's it for today.  Have a great week!