Get Certificate Details for SQL Encryption In-Transit Using PowerShell – UPDATED

In a previous blog post, I showed you how to use PowerShell to retrieve the details of the certificate being used for encryption-in-transit, aka TLS encryption.

I have received a lot of feedback about that script and one of the most common requests was how to run it remotely. I have finally modified the script to do just that.

The previous version required you to run it locally on a server. It would return the certificate information for each SQL instance that it detected. The new version can be run remotely, but it will expect you to pass in a list of SQL Servers to scan. For example, from my workstation I can run the following command to get the certificate details for the remote server.

.\Get-SqlCertificateDetails.ps1 -SqlInstance 'DEATHSTAR\SQL2019'

You can also pipe a list of SQL Servers to the function. For example, you have a text file that contains a list of SQL Servers, then just use Get-Content to read the file and pipe that to Get-SqlCertificateDetails.

Get-Content -Path Servers.txt | .\Get-CertificateDetails.ps1

I also modified the section that collects the permission on the private key. Previously, the script only retrieved the permission for the SQL service account, but it would miss permissions granted to the Service SID (i.e. NT SERVICE\MSSQL$MSSQLSERVER). The updated script now accounts for both the actual service account name and the Service SID.

In the example below, both the SQL Server service account and the Service SID have been granted access to the private key.

Looking at the output of Get-CertificateDetails, you will see the permission for each account. The service account GOVLAB\ALDERAANEN1$ has been granted FULL CONTROL and the Service SID (NT SERVICE\MSSQL$SQL2019) has been granted READ.

.\Get-CertificateDetails.ps1 -SqlInstance 'ALDERAAN\SQL2019' | Select-Object -ExpandProperty PrivateKeyPermission

The updated script is below.


process {
  foreach($instance in $SqlInstance){
    $certificate = $null

    # Split the server and instance names
    if (($instance).Split('\')[1]) {
      [string]$serverName   = ($instance).Split('\')[0]
      [string]$instanceName = ($instance).Split('\')[1]
      [string]$regPath      = "MSSQL`$$($instanceName)"
    else {
      [string]$serverName   = $instance
      [string]$instanceName = 'MSSQLSERVER'
      [string]$regPath      = 'MSSQLSERVER'
    # Open a PowerShell remote session
    $psSession = New-PSSession -ComputerName $serverName -ErrorAction Continue

    # If the remote session failed to connect then continue to the next instance
    if (-not $psSession) {

    # Run remote PowerShell to get the certificate details
    try {
      $certificate = Invoke-Command -Session $psSession -ScriptBlock {
        [string]$instanceId = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL" -Name $using:instanceName).$using:instanceName
        [string]$thumbprint = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib" -Name Certificate).Certificate

        if($thumbprint.Length -ne 0){
          [bool]$isEncrypted = $true
          [bool]$isForceEncrypted = (Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib" -Name ForceEncryption).ForceEncryption

          # Use CERT provider to get the details of the certificate
          $certificate = Get-ChildItem CERT:\LocalMachine\My | Where-Object Thumbprint -eq $thumbprint
        else {
          [bool]$isEncrypted = $false
        # Get the ACLs on the Private Key (This requires PowerShell to be run as Administrator)
        try {
          $serviceAccount = (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\$($using:regPath)").ObjectName
          $acl = (Get-Acl "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\$(([System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Certificate)).Key.UniqueName)").Access

          $pvkAcl = @()
          $pvkAcl += $acl | Where-Object { $_.IdentityReference -eq "$($serviceAccount)" } | Select-Object IdentityReference, AccessControlType, FileSystemRights | ForEach-Object { "$($_.IdentityReference) | $($_.AccessControlType) | $($_.FileSystemRights)" }
          $pvkAcl += $acl | Where-Object { $_.IdentityReference -eq "$(Join-Path -Path "NT SERVICE\" -ChildPath $using:regPath)" } | Select-Object IdentityReference, AccessControlType, FileSystemRights | ForEach-Object { "$($_.IdentityReference) | $($_.AccessControlType) | $($_.FileSystemRights)" }
        catch {
          # If PowerShell is not running as Administrator, then we need to catch the error but then continue on
          $pvkAcl = $Null

        # Output the results
          Hostname             = $using:serverName
          SqlInstance          = $using:instance
          IsEncrypted          = $isEncrypted
          IsForceEncrypted     = $isForceEncrypted
          Subject              = $certificate.Subject
          FriendlyName         = $certificate.FriendlyName
          Thumbprint           = $thumbprint
          IssueDate            = $certificate.NotBefore
          ExpirationDate       = $certificate.NotAfter
          ServiceAccount       = $serviceAccount
          PrivateKeyPermission = $pvkAcl
      } -ErrorAction Stop
    catch {
      Write-Error -Message $_.Exception.Message -TargetObject $instance

    # Output the results to the caller
    $certificate | Select-Object Hostname, SqlInstance, IsEncrypted, IsForceEncrypted, Subject, FriendlyName, Thumbprint, IssueDate, ExpirationDate, ServiceAccount, PrivateKeyPermission
end {