Get Certificate Details for SQL Encryption In-Transit Using PowerShell

There is an updated version of this script here.

In my opinion, all customers should be encrypting connections to their SQL Servers to secure data in-transit, but that’s a discussion for another day. Today, I wanted to discuss how you can use PowerShell to lookup details on the certificate being used for that encryption.

For this example, we have three SQL Server instances each configured slightly different for encrypted communication. Using the SQL Server Configuration Manager, we can see the first instance is configured to use a certificate named “deathstar.govlab.corp (SHA256)”.

The FORCE ENCRYPTION option is set to YES to make it mandatory for all connections to use this certificate.

You’ll then need to use the Certificates manager (via MMC snap-in) to see important details about the certificate such as the expiration date.

Since the we are using encryption, we need to verify the SQL Server service account has been granted READ permission on the certificate’s private key. Without it, the service will fail to startup.

Now that’s just the details for the first instance. We would need to repeat those verification steps for the other two instances. This is where PowerShell can be extremely helpful; one script to grab those details for every instance on the server.

If we zoom into the SQL2019 instance, we can see all the details in one easy to read place. The top rows (outlined in red) show how SQL Server is configured. The middle rows (outlined in yellow) show the details of the certificate such as the name, thumbprint, and expiration date. The bottom rows (outlined in green) show the SQL Server service account and the permissions granted on the private key.

For the remaining instances, you can quickly see both are configured differently. The default instance does not have the FORCE ENCRYPTION option turned on, and the service account has been granted FULLCONTROL instead of the minimum requirement of READ.

The detail for the SQL2017 instance is blank which means encryption has not been configured yet.

To retrieve the permissions on the private key using PowerShell, you will need to open the PowerShell window as administrator. If not, the script will still run but the PrivateKeyPermmission field will be blank.

This is just another reason I think all DBAs should be using PowerShell for their everyday administration tasks. The entire PowerShell script is below. Feel free to modify it as you see fit for your environment.

#Define variables
[string]$hostname = $env:COMPUTERNAME
[bool]$isEncrypted = $false
[bool]$isForceEncryption = $false

#Get a list of all SQL instances
$instanceNames = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server").InstalledInstances

#Loop through each instance
foreach ($instance in $instanceNames) {
    if ($instance -eq "MSSQLSERVER") {
        $sqlServer = $hostname
        $regPath = $instance
    }
    else {
        $sqlServer = "$hostname`\$instance"
        $regPath = "MSSQL`$$($instance)"
    }

    #Reset variables before checking the next instance
    [bool]$isEncrypted = $false
    [bool]$isForceEncryption = $false
    [string]$thumbprint = $Null
    $certificate = $null
    $pvkAcl = $Null

    #Pull the SQL certificate info from the registry
    [string]$instanceId = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL" -Name $instance).$instance
    [string]$thumbprint = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib" -Name Certificate).Certificate

    #Only run this section if there is a certificate in use
    if($thumbprint){
        [bool]$isEncrypted = $true
        try {
            [bool]$isForceEncryption = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib" -Name ForceEncryption).ForceEncryption
            $certificate = Get-ChildItem CERT:\LocalMachine\My | Where-Object Thumbprint -eq $thumbprint
        }
        catch {
            throw $_
        }

        #Get the ACLs on the Private Key (This requires PowerShell to be run as Administrator)
        try {
            $serviceAccount = (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\$($regPath)").ObjectName
            $acl = (Get-Acl "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\$(([System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Certificate)).Key.UniqueName)").Access
            $pvkAcl = $acl | Where-Object {$_.IdentityReference -eq "$($serviceAccount)"}
        }
        catch {
            #If PowerShell is not running as Administrator, then we need to catch the error but then continue on
            $pvkAcl = $Null
        }
    }

    #Output the results
    [PSCustomObject]@{
        Hostname = $hostname
        SqlServer = $sqlServer
        IsEncrypted = $isEncrypted
        IsForceEncryption = $isForceEncryption
        Subject = $certificate.Subject
        FriendlyName = $certificate.FriendlyName
        Thumbprint = $thumbprint
        IssueDate = $certificate.NotBefore
        ExpirationDate = $certificate.NotAfter
        ServiceAccount = $serviceAccount
        PrivateKeyPermission = $(if($pvkAcl) {"$($pvkAcl.IdentityReference) | $($pvkAcl.AccessControlType) | $($pvkAcl.FileSystemRights)"} else { $Null })
    }
}

The link below outlines the steps needed to encrypt data across communication channels, including the requirements for creating a certificate.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15