Automatically Updating SSL Certificates on SQL Server

In a previous post (How Secure Are Your Client Connections?) I covered using SSL certificates to encrypt client connections to SQL Server. One part of the process that has always been annoyingly repetitive is the need to regularly renew/update the SSL certificate(s) and reconfigure SQL Server to use them. In the past our SSL certs were good for 3 years. Then in 2020 Google, Microsoft, Apple & others announced that they were reducing their certificate validity period to a maximum of 398 days. Recently, there have been proposals to further reduce the validity period to 90 days. When this happens (and I’m certain it is a when, and not an if situation), manually replacing SSL certs on multiple SQL servers will not be a viable option.

Earlier this year we started looking at options for automatically replacing SSL certificates on our Windows servers. Creating our own Certificate Authority, while technically possible, wasn’t our preferred option. Instead, we opted to use win-acme (a Windows-based ACMEv2 client). After implementing it on our IIS servers, I began working on how to use win-acme with our SQL servers. The basic process is:

1. Install win-acme on the SQL server. (In this post I’m using a manual install process. It can also be accomplished with an unattended install/script.)
2. After win-acme has installed the new SSL certificate:
   • Update the certificate’s ACL to allow read permission to the SQL Server Service account.
   • Assign the new certificate to the SQL Server Protocols properties.
   • Restart the SQL Server Service.

Note: A Key ID and Key (ie. username/password) are required to allow win-acme to authenticate with our cert provider (InCommon) and obtain the new certificate.

To allow win-acme to automatically update & restart the SQL Server, I created a PowerShell script (below). So far, we haven’t had any issues with the new process. The only negative aspect is that the SQL Server service is automatically restarted when a new certificate is successfully downloaded (no outage notification or scheduling). This necessitated using a narrow cert renewal window to minimize the service outage (restart). For our Availability Group replicas, each server in the cluster has a different renewal window, so they are not renewed/restarted at the same time.

Win-Acme Install and Configuration In Detail

Win-acme installer steps:

1. Download the win-acme installer files and extract them to an appropriate location on the server. (eg. c:\Program Files\win-acme)
2. Rename the settings_default.json file to be settings.json and update the parameters:
   • Acme (certificate provider details)
   • Scheduled Task (certificate renewal window)
   • Notification (email alerts for failed renewals)
3. Run the wacs.exe as an Administrator
   • Select M – Create certificate (full options)
   • Select 2 (Manual Input domain names)
   • Enter the host name, SANs & Availability Group listener names
   • Accept or modify friendly name as desired
   • Select 4 – Single certificate
   • Select 2 – Serve verification files from memory
   • Select 2 – RSA key
   • Select 4 – Store the certificate in the Windows Certificate Store (Local Computer)
   • Select 2 – Use the global default [My] store
   • Select 5 – No additional store steps
   • Select 2 – Start external script or program. (This will run the PowerShell script to update the SQL Server config after the cert is renewed)
   • Enter the path to the PowerShell file.
     • eg. c:\Program Files\win-acme\Update-SQL-SSL-Config.ps1)
   • Enter {CertThumbprint} in the parameters field
   • Enter 3 – No additional steps
   • You don’t need to open the pdf
   • Accept the license terms.
   • Key identifier provided by the cert issuer (InCommon)
   • Key (base64url encoded) also provided by the cert issuer.
   • Enter N – do not specify a user to run the task as
   • Select q to quit.

Powershell Script

For convenience, I saved this PowerShell script (Update-SQL-SSL-Config.ps1) to the same folder as the win-acme files.

Param( $CertThumbprint)
$SQLSvcAcct = (Get-WmiObject win32_service -Filter "Name='MSSQLSERVER'").StartName
$certobj = dir Cert:\LocalMachine\My | Where {$_.Thumbprint -eq $CertThumbprint};
$keyName=(((Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Thumbprint -like $certObj.Thumbprint}).PrivateKey).CspKeyContainerInfo).UniqueKeyContainerName;
$keyPath = "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\";
$fullPath=$keyPath+$keyName;
$acl=Get-Acl -Path $fullPath;
$newPermission= $SQLSvcAcct,"Read","Allow";
$accessRule=new-object System.Security.AccessControl.FileSystemAccessRule $newPermission;
$acl.AddAccessRule($accessRule);
Try {
    Set-Acl $fullPath $acl;
    write-output "ACL updated";
    }
Catch {
    "Error updating ACL";
    Exit;
    }
$regkey = Get-ChildItem -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server" -Recurse | Where-Object {$_.Name -like "*SuperSocketNetLib"}
Set-ItemProperty -path $regKey.PSPath -Name Certificate -value $certObj.Thumbprint ;
Restart-Service MSSQLSERVER -Force;

After using PowerShell to update the certificates information, the SQL Server Configuration Manager UI may not display the new cert information.

However, the updated certificate’s thumbprint will appear in the registry key: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<SQL Version>.<Instance Name>\MSSQLSERVER\SuperSocketNetLib\Certificate