Programmatically set the TLS certificate to be used by a given SQL Server Instance

Set-SqlTlsCertificate.ps1

function Set-SqlTlsCertificate {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param (
        [Parameter()]
        [Alias("CN", "MachineName")]
        [String[]]$ComputerName = "localhost",

        [Parameter()]
        [String[]]$InstanceName,

        [Parameter()]
        [Alias("Thumbprint")]
        [ValidatePattern("^[A-F0-9]{40}$")]
        [ValidateLength(40,40)]
        [ValidateNotNullOrEmpty()]
        [String]$CertificateThumbprint
    )
    
    begin {
        if ($null -eq $InstanceName -or $InstanceName -eq "") {
            $setAllInstances = $true;
        } else {
            $setAllInstances = $false;
        }

        $anyMatchingInstanceFound = $false;

        #Thumbprint MUST be in upper case!
        $CertificateThumbprint = $CertificateThumbprint.ToUpper(); 

        $sqlnamespaceRoot = "root\Microsoft\SqlServer";
        $serverSettingsQuery = "SELECT * FROM ServerSettings";
        $findCertificatesQuery = "SELECT * FROM SecurityCertificate";
    }
    
    process {
        foreach ($computer in $ComputerName) {
            #Namespaces are the different installed SQL support versions
            $namespaces = Get-WmiObject -ComputerName $computer -Namespace $sqlnamespaceRoot -Class __Namespace | Where-Object Name -like "ComputerManagement*"

            #Prior to SQL 2017, management components required exact version match
            #Starting from SQL 2017, a given instance may appear in more than one management component, so we need to
            $foundSqlInstances = @();
            $foundInstanceNames = @();

            $foundMatchingCertificate = $false;

            $namespaces | ForEach-Object {
                Get-WmiObject -ComputerName $computer -Query $serverSettingsQuery -Namespace "$($sqlnamespaceRoot)\$($_.Name)" | ForEach-Object {
                    $foundSqlInstances += $_;
                }

                Get-WmiObject -ComputerName $computer -Query $findCertificatesQuery -Namespace "$($sqlnamespaceRoot)\$($_.Name)" | ForEach-Object {
                    if ($_.SHA -eq $CertificateThumbprint) {
                        Write-Debug -Message "Found matching certificate with thumbprint $($CertificateThumbprint) on computer: $($computer)";
                        $foundMatchingCertificate = $true;
                    }
                }
            }

            if ($foundMatchingCertificate -eq $false) {
                Write-Warning -Message "Could not find matching certificate with thumbprint $($CertificateThumbprint) on computer: $($computer). Verify that the certificate is installed, is valid, and is not expired.";
            }

            foreach ($sqlInstance in $foundSqlInstances) {
                if (($setAllInstances -eq $true -or $sqlInstance.InstanceName -eq $InstanceName) -and $foundInstanceNames -notcontains $sqlInstance.InstanceName) {
                    $anyMatchingInstanceFound = $true;
                    $foundInstanceNames += $sqlInstance.InstanceName;
                    if ($PSCmdlet.ShouldProcess("Install certificate on $($computer)\$($sqlInstance.InstanceName)")) {
                        $result = $sqlInstance.SetCurrentCertificate($CertificateThumbprint);
                        if ($result.ReturnValue -ne 0) {
                            throw "Failed to set TLS certificate. Return value: $($result.ReturnValue)";
                        }
                    }
                }
            }
        }
    }
    
    end {
        if ($anyMatchingInstanceFound -eq $false) {
            throw "No SQL instance found!";
        }
    }
}