SQL Server supports encryption of its data connections; in this article I explain which steps to take and how to configure this using a simple Powershell script.

Introduction

After configuring a new machine with the latest SqlServer Powershell module the other day, I ran into a warning I hadn’t seen before:

Invoke-Sqlcmd -ServerInstance $env:COMPUTERNAME -Query "select 4 + 1"
WARNING: Starting with v22 of the SQLServer module, connections are encrypted by default. 
Use the new parameter -Encrypt to control the encryption properties.

As it happens, the SqlServer module v22 is dependent on Microsoft.Data.SqlClient v4.0.0, which has a breaking change: “Changed Encrypt connection string property to be true by default.”. Apparently, Microsoft nudges us to use encryption on our SQL Server data connections.

Ok, I’ll bite: how difficult would it be to set this up, and can I do it from Powershell so I can easily apply this to multiple machines?

In short, I’ll cover:

1. Requesting a certificate
2. Configuring the SQL Server encryption settings
3. Granting READ access to the certificate’s private key
4. Troubleshooting
5. Querying which connections are encrypted

1. Requesting a certificate

First off, we need a certificate that SQL Server can use to encrypt the connection with. If you’re working in an Active Directory environment that has a Certificate Authority configured, the easiest way is to have it generate and enroll a certificate for you – in my experience, using a (derivative of the) Web Server template is enough. The easiest way to make it available to SQL Server is to place the certificate in the Local machine store. Obviously, the certificate’s subject should match the machine name that runs the SQL Server instance – and this is also the exact name you should use from now on in any connection string or -ServerInstance parameter.

# Use this machine's FQDN as the certificate subject
$fqdn = [System.Net.Dns]::GetHostByName(($Env:ComputerName)).HostName.ToLower()
$enrollmentResult = Get-Certificate -Template "Web Server" `
    -CertStoreLocation "Cert:\LocalMachine\My" -SubjectName "CN=$fqdn"

# The $enrollmentResult.Status contains if the certificate was Issued directly, or if it is Pending 
# because (probably a Domain Admin) first needs to approve this request.

If you can’t use an AD Certificate Authority, of course there are other ways to obtain certificates. The exact requirements for a certificate are listed here (if you are in doubt about the “KeySpec option of AT_KEYEXCHANGE”, check this article).

2. Configuring the SQL Server encryption settings

The manual way to configure the certificate for SQL Server is via the Configuration Manager, as discussed here. In the end, these settings all end up in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\, which is something we can do directly from Powershell as well.

Let’s create a cmdlet for it (the complete cmdlet without line breaks can be found here):

function Set-SqlConnectionEncryption
{
   [CmdletBinding()]
   param (
      # See https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements?view=sql-server-ver16
      [Parameter(Mandatory=$false, Position = 1, HelpMessage = "The certificate to use (or `$null " + 
         "to clear it), must be present in the cert:\Local Machine\My store.")]
      [System.Security.Cryptography.X509Certificates.X509Certificate2]$Certificate,
     
      [Parameter(Mandatory=$false, HelpMessage = "The SQL Server serviceaccount that should be " +
         "granted access to the certificate's private key. Optional, defaults to the MSSQLSERVER " + 
         "login account, usually ""NT Service\MSSQLSERVER"".")]
      [string]$ServiceAccount,
     
      # See https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16#step-2-configure-encryption-settings-in-sql-server
      [Parameter(HelpMessage = "Wether SQL Server should accept only encrypted connections. " +
         "Defaults to false.")]
      [switch]$ForceEncryption,
     
      # See https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8
      [Parameter(HelpMessage = "Wether SQL Server requires clients to specify HostNameInCertificate " + 
         "rather than TrustServerCertificate=true. Defaults to false.")]
      [switch]$ForceStrict
   )

   $ErrorActionPreference = "Stop"

   # 1. Configure the Certificate, ForceEncryption and ForceStrict values that SQL Server should use
   # Locate the "SuperSocketNetLib" registry key that contains the encryption settings; highest 
   # first in case there are multiple versions.
   $regKey = (ls "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" -Recurse | 
      Where-Object { $_.Name -like '*SuperSocketNetLib' } | Sort-Object -Property Name -Descending)
   if($regKey -is [Array])
   {
      $regKey = $regKey[0]
      Write-Warning "Multiple SQL instances found in the registry, using ""$($regKey.Name)""."
   }

   if($Certificate)
   {
      # The thumbprint must be in all lowercase, otherwise SQL server doesn't seem to accept it?!
      Set-ItemProperty $regKey.PSPath -Name "Certificate" -Value $Certificate.Thumbprint.ToLowerInvariant()
   }
   else
   {
      Set-ItemProperty $regKey.PSPath -Name "Certificate" -Value $null
   }
      
   # Note that ForceStrict is only available from SQL2022 onwards
   Set-ItemProperty $regKey.PSPath -Name "ForceEncryption" `
      -Value $(if($ForceEncryption) { 1 } else { 0 })
   Set-ItemProperty $regKey.PSPath -Name "ForceStrict" `
      -Value $(if($ForceStrict) { 1 } else { 0 })
    
   # 2. Grant READ access to the $Certificate's private key
   # <continued below>
}

3. Granting READ access to the certificate’s private key

In order for SQL Server to actually use the configured certificate, it needs READ access to the certificate’s private key:

   # 2. Grant READ access to the $Certificate's private key
   if($Certificate)
   {
      # If no -ServiceAccount is specified, determine under which identity the MSSQLSERVER service 
      # runs...
      if(!$ServiceAccount)
      {
         $ServiceAccount = (Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'").StartName
      }
      
      # The private keys are stored on the file system, determine the path for $Certificate
      # See https://stackoverflow.com/questions/40046916/how-to-grant-permission-to-user-on-certificate-private-key-using-powershell
      if (!$Certificate.PrivateKey)
      {
          $privateKey = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Certificate)
          $privateKeyPath = "$env:ProgramData\Microsoft\Crypto\Keys\$($privateKey.Key.UniqueName)"
      }
      else
      {
          $containerName = $Certificate.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
          $privateKeyPath = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$containerName"
      }
      
      # Now grant the $ServiceAccount read permissions to the private key file
      $acl = Get-Acl -Path $privateKeyPath
      $accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule `
         @($serviceaccount, "Read", "Allow")
      [void]$acl.AddAccessRule($accessRule)
      Set-Acl -Path $privateKeyPath -AclObject $acl
   }
}

Using this cmdlet is then as simple as:

# Display available certificates and select one:
ls Cert:\LocalMachine\My | Format-Table Thumbprint, Subject, DnsNameList, NotBefore, NotAfter
$cert = (ls Cert:\LocalMachine\My)[0]

# Configure this certificate
Set-SqlConnectionEncryption $cert -ForceEncryption

# Have the settings become effective
Restart-Service MSSQLSERVER -Force -Verbose

The last line is especially important because the settings won’t become effective until the SQL Server instance is restarted.

4. Troubleshooting

If the SQL Service won’t start anymore after this, this probably means that there’s something wrong with the configured certificate. Fortunately, SQL Server’s messages in the Windows Eventlog are pretty self-explanatory:

5. Querying which connections are encrypted

Once SQL Server is running again, we want to know if the connections are actually encrypted. Microsoft Learn has a handy table that defines the results of the various client and server settings.

The other way is to ask SQL Server itself: The sys.dm_exec_connections view contains an encrypt_option that tells us exactly that, and combined with some other useful information you can tell if all applications and/or users are using encrypted connections:

select
   ses.session_id,
   con.net_transport,
   con.auth_scheme,
   con.encrypt_option,
   ses.database_id,
   db_name(ses.database_id) as database_name,
   ses.host_name,
   ses.login_name,
   ses.program_name,
   ses.client_interface_name,
   ses.client_version
from sys.dm_exec_sessions as ses
left outer join sys.dm_exec_connections as con on (ses.session_id = con.session_id)
where
   con.net_transport is not null
order by database_name, login_name

The results look like this:

Conclusion

With a few simple lines of Powershell you can have (some, or all of) your data connections to SQL Server be encrypted. Just don’t forget to monitor the certificate’s expiration date 🙂

Share this