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.
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[ValidateNOTNullOrEmpty()]
[String[]]$SqlInstance
)
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) {
continue
}
# 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
[PSCustomObject]@{
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
continue
}
# Output the results to the caller
$certificate | Select-Object Hostname, SqlInstance, IsEncrypted, IsForceEncrypted, Subject, FriendlyName, Thumbprint, IssueDate, ExpirationDate, ServiceAccount, PrivateKeyPermission
}
}
end {
}
Reference:
