This technical guide provides detailed instructions on how to configure SSL/TLS encryption for SQL Server using a self-signed or CA-issued certificate. Secure your data transmissions and enhance your security posture with these essential steps.

Introduction

Configuring SSL/TLS Encryption for SQL Server with a Self-Signed Certificate

Step 1: Create a Self-Signed Certificate (If you already have a certificate, you can skip this step and import instead of creating a new cert.  Follow the directions at the bottom of this document.)

Use PowerShell to generate a self-signed certificate specifically tailored for your SQL Server to secure connections.

Open PowerShell as Administrator:

Right-click on the Start button and select “Windows PowerShell (Admin)”.

Generate the Self-Signed Certificate:

• • • Execute the following command, substituting “YourServerFQDN” with the Fully Qualified Domain Name of your SQL Server:
    • Run [System.Net.Dns]::GetHostByName($env:computerName).HostName in PowerShell to get your FQDN.

    $cert = New-SelfSignedCertificate -DnsName "YourServerFQDN" -CertStoreLocation cert:\LocalMachine\My -KeySpec KeyExchange -KeyUsage DigitalSignature, KeyEncipherment -Type SSLServer -KeyAlgorithm RSA -KeyLength 2048 -KeyExportPolicy Exportable -NotAfter (Get-Date).AddYears(10)

    $cert = New-SelfSignedCertificate -DnsName “YourServerFQDN” -CertStoreLocation cert:\LocalMachine\My -KeySpec KeyExchange -KeyUsage DigitalSignature, KeyEncipherment -Type SSLServer -KeyAlgorithm RSA -KeyLength 2048 -KeyExportPolicy Exportable -NotAfter (Get-Date).AddYears(10)

    This command creates a certificate valid for 10 years, suitable for server authentication and encrypted communication.  (Export/Backup this cert. Follow the direction at the bottom of this document.)

    Step 2: Install and Configure the Certificate in SQL Server

    Open SQL Server Configuration Manager:

    Navigate to SQL Server Network Configuration > Protocols for [Your SQL Server instance].

    Set the Certificate for SQL Server:

    Right-click “Protocols for [Your instance name]”, select “Properties”.

    In the “Certificate” tab, choose the certificate you created.

    In the “Flags” tab, set “Force Encryption” to “Yes”.

    Restart SQL Server:

    • • Go to SQL Server Services.
      • Right-click your SQL Server service, select “Restart”.

      Step 3: Set Permissions for SQL Server on the Certificate’s Private Key

      Adjust Private Key Permissions:

      Open the Microsoft Management Console (MMC).

      Add the Certificates snap-in for the Local Computer.

      Navigate to Personal > Certificates, find your certificate.

      Right-click, select “All Tasks” > “Manage Private Keys”.

      Give the SQL Server service account “Read” permission.

      Step 4: Configure Client Machines (This step is ONLY REQUIRED FOR Self-Signed Certificate)

      Export the Public Key of the Certificate:

      In MMC, right-click your certificate under Personal > Certificates.

      Select “All Tasks” > “Export”. Follow the wizard to export without the private key, save as .CER.

      Install the Public Key on Client Machines:

      Transfer the .CER file to each client machine.

      Double-click the file, choose “Install Certificate”.

      Select “Local Machine”, place the certificate in “Trusted Root Certification Authorities”.

      Configure Client Applications:

      Ensure client applications, such as SQL Server Management Studio, specify Encrypt=True; in their connection strings to enforce encrypted connections.

      Optionally, add TrustServerCertificate=False; to enforce certificate validation.

      Step 5: Verify the Encryption

      Check the Encryption in SQL Server:

      Connect to SQL Server with SSMS.

      Execute:

      SELECT session_id, encrypt_option FROM sys.dm_exec_connections WHERE session_id =@@SPID;

      SELECT session_id, encrypt_option FROM sys.dm_exec_connections WHERE session_id =@@SPID;

      Verify encrypt_option is TRUE, indicating an encrypted connection.

       

      Final Notes

      • Documentation and Updates: Keep records of the certificate issuance, SQL Server configuration, and client settings. Monitor the certificate’s expiry and plan for renewal.
      • Backup the Certificate: Securely back up both the certificate and its private key.
      • Security Best Practices: Regularly update security configurations and SQL Server patches.

      This comprehensive setup ensures that your SQL Server communications are securely encrypted, safeguarding data in transit between SQL Server and its clients.

      When dealing with a scenario where an application with thousands of users connects to a SQL Server, it’s impractical to manually configure each client machine to trust the self-signed certificate used for encrypting connections. There are a couple of approaches to manage the certificate deployment and ensure encrypted connections:

      Approach 1: Use a Certificate from a Trusted Certificate Authority (CA)

      Instead of using a self-signed certificate, consider obtaining a certificate from a well-known and trusted Certificate Authority (CA). This simplifies client configuration because most operating systems and applications, including SQL Server clients, inherently trust certificates issued by major CAs. Here’s how you can manage this:

      1. Obtain a Certificate from a CA:
         • Purchase and obtain a certificate for your SQL Server that includes the DNS name “YourServerFQDN”.
         • The CA will provide you with a certificate chain that includes your server certificate, any intermediate certificates, and the root certificate.
      2. Install the Certificate on Your SQL Server:
         • Install the certificate in the Personal store of your SQL Server as described in the previous steps.
      3. Configure SQL Server to Use the Certificate:
         • Set up SQL Server to use the new certificate from the CA, ensuring “Force Encryption” is enabled in SQL Server Configuration Manager.

      By using a CA-issued certificate, you bypass the need to manually configure trust for the certificate on each client, as the operating systems on client machines already trust most established CAs.

      Approach 2: Automate Deployment of the Self-Signed Certificate

      If a CA certificate isn’t an option and you need to continue with a self-signed certificate, you can automate the deployment of the certificate to client machines. This can be done using Group Policy in a domain environment:

      1. Export the Public Key of the Self-Signed Certificate:
         • Export the certificate without the private key in DER or Base64 format.
      2. Deploy the Certificate via Group Policy:
         • Open Group Policy Management Console (GPMC) on a machine that has administrative access to your Active Directory.
         • Create a new GPO or edit an existing one that applies to all users or computers that need access to the SQL Server.
         • Navigate to Computer Configuration > Policies > Windows Settings > Security Settings > Public Key Policies.
         • Right-click Trusted Root Certification Authorities and choose Import.
         • Follow the wizard to import the exported public key of your self-signed certificate.
      3. Apply and Update Group Policy:
         • Once the GPO is configured, force a Group Policy update across the domain or wait for the automatic Group Policy refresh cycle.
         • You can force an update by running gpupdate /force on client machines or remotely from a central location using scripts.

      Import/Export Certificate

      Export/Backup Certificate

      If you need to move the certificate to another machine or back it up:

      1. Open MMC (Microsoft Management Console):
         • Press Win + R, type mmc, and press Enter.
         • Click File > Add/Remove Snap-in.
         • Select “Certificates”, click “Add”, choose “Computer account”, then “Local computer”. Click “Finish” and “OK”.
      2. Export the Certificate:
         • Navigate to Certificates (Local Computer) > Personal > Certificates.
         • Right-click your new certificate, choose All Tasks > Export.
         • Follow the wizard, choose to export the private key, and protect it with a password. Save the certificate as a .pfx file.

      Import Certificate on the New Server

      1. Open the Microsoft Management Console (MMC):
         • Press Win + R, type mmc, and press Enter.
         • Click File > Add/Remove Snap-in.
      2. Add the Certificates Snap-in:
         • Select “Certificates” and click “Add”.
         • Choose “Computer account” and click “Next”.
         • Select “Local computer” and click “Finish”.
         • Click “OK” to load the snap-in.
      3. Import the Certificate:
         • Navigate to Certificates (Local Computer) > Personal > Certificates.
         • Right-click on Certificates, select All Tasks > Import to start the Certificate Import Wizard.
         • Click Next on the welcome screen.
         • Browse to the location of your .pfx file, select it, and click Next.
         • Enter the password for the private key that you set during the export process, and ensure that the option Mark this key as exportable is checked. This allows you to back up or transport the keys later.
         • Choose to automatically select the certificate store based on the type of certificate, or manually place it into the Personal store.
         • Complete the wizard by clicking Next and then Finish.

      Verify the Certificate Installation

      • After importing, the certificate should appear in the Personal > Certificates folder of the MMC for the Local Computer.
      • Double-check the certificate’s properties by double-clicking on it. Ensure it displays the correct details and reports that there is a private key corresponding to the certificate.