Encryption-in-transit is part of the essential security baseline for SQL Server.
Without TLS, TDS packets travel in clear text across the network, making them vulnerable to interception or modification by an attacker attempting to gain access to the system.

This is why TLS has become a standard in modern SQL Server architectures.
In this article, we will look at how to standardize and automate this deployment using PowerShell.

In our automation, we assume that the certificate is already present in Cert:\LocalMachine\My (Local Machine Personal). The certificate is ws202201.dbi.test in this output:

PS> ls cert:\LocalMachine\My


   PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\My

Thumbprint                                Subject
----------                                -------
E6DB2D082B41B36F08806EA82B25064150618E1F  CN=CLIUSR
D4EBB1260BECFD28254082A734FD4D5663199B16  CN=CLIUSR
30B20A2A54A3DAB60E6AC601B94F6E5A626D7C2D  CN=ws202201.dbi.test

The first step is to give the SQL Server service account read permissions on the certificate’s private key, otherwise the instance will not be able to use the certificate for TLS.
The script uses two input parameters : Instance Name of SQL Server and Friendly Name of the certificate.

#---- Variables -------------------------------------------------------------
$InstanceName = 'INSTANCE1';     # Instance name to adapt
$FriendlyName = 'Instance1 TLS'; # Certificate Friendly Name to adapt

#---- Begin Script ----------------------------------------------------------
# Construct Virtual Account for SQL Server instance
If ($InstanceName -eq 'MSSQLSERVER') {
    $user = 'NT SERVICE\MSSQLSERVER';
} else {
    $user = "NT SERVICE\$('MSSQL$'+$InstanceName)";
};

# Retrieve certificate based on Friendly Name
$cert = Get-ChildItem -Path 'Cert:\LocalMachine\My' ` 
| Where-Object {$_.FriendlyName -eq "$FriendlyName"};

# Give Virtual Account read permission
$permission = "Read"; 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $permission, "Allow"); 
$keyPath = [System.IO.Path]::Combine("C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys", $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName); 
$acl = Get-Acl -Path $keyPath;
$acl.SetAccessRule($rule); 
Set-Acl -Path $keyPath -AclObject $acl;

The next step is to reference the certificate’s thumbprint in the SQL Server registry configuration.
A key detail here: while PowerShell is case-insensitive, the thumbprint stored in the registry is case-sensitive. SQL Server expects the value to be written in lowercase; otherwise, the instance will not load the certificate at startup.

# Root registry entry of instance (Regex)
$Pattern = "MSSQL\d\d\.$($InstanceName)";

# Construct path where is stored Certificate property
$RegMSSQL = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\';
$RegRootInstance = Get-childItem -Path $RegMSSQL `
| Where-Object Name -match "$Pattern";

# Update Certificate property with certificate thrumprint in lower case
$RegTCPCertificate = 'MSSQLServer\SuperSocketNetLib\';
Set-ItemProperty ` 
-Path (Join-Path -Path $RegRootInstance.PSPath -ChildPath $RegTCPCertificate) `
-Name 'Certificate' -Value "$($cert.Thumbprint.ToLower())";

All that remains is to restart the SQL Server service to apply the changes.

To confirm encryption, connect SQL Server and run the following T-SQL command:

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


session_id encrypt_option
---------- --------------
        60 TRUE

Post Views: 37
Thumbnail [60x60]
by
Nathan Courtine