public/Install-SQLCertificate.ps1

function Install-SQLCertificate {

    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true,Position=0)]
        [string[]]
        $ComputerName,
        [Parameter(Mandatory=$true,Position=1)]
        [string]
        $SQLInstance,
        [Parameter(Mandatory=$true,Position=2)]
        [string]
        $PfxLocalPath,
        [Parameter(Mandatory=$true,Position=3)]
        [securestring]
        $PfxPassword,
        [string]
        $RemoteFolder = "C:\Windows\Temp",
        [pscredential]
        $Credential
    )
    
    $scriptblock = {
        $filename = $using:filename
        $pfxremotepath = Join-path $using:RemoteFolder $filename
        $certstatus = $null
            # 1. Place Cert in store
        ## Add to the store, allow export of cert also get thumbprint value to variable
        $thumbprint = (Import-PfxCertificate -CertStoreLocation Cert:\LocalMachine\My -Exportable -Password $using:PfxPassword -FilePath $pfxremotepath).Thumbprint

        
        ## Get Service Account base on $SQLInstance name
        $serviceaccount = (Get-CimInstance win32_service | Where-Object {$_.Name -like "MSSQL`$$using:SQLInstance"}).StartName

        ## give permissions to the SQL Service account
            ## parts stolen from https://xenappblog.com/2017/automatically-install-sql-server-and-enable-ssl-encryption-with-domain-ca/
        $permissionType = 'Read'
        try {
            #Clear Existing Variables
            $cert = ''
            $keyFullPath = ''
            Write-Host "[ $env:ComputerName ] - Finding Certificate with thumbprint: $thumbprint" -ForegroundColor Green
            #Get Certificate
            $cert = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq ($thumbprint -replace '\s','')}
            If ($cert -and $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName) 
            {
                
                # Get Location of the machine related keys
                $keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\"; 
                $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName;
                $keyFullPath = $keyPath + $keyName;
                Write-Host "[ $env:ComputerName ] - Found Certificate..." -ForegroundColor Green
                Write-Host "[ $env:ComputerName ] - Granting access to $serviceAccount..." -ForegroundColor Green
                #Grant Full Control to account listed in $serviceAccount
                $acl = (Get-Item $keyFullPath).GetAccessControl('Access') #Get Current Access
                $buildAcl = New-Object  System.Security.AccessControl.FileSystemAccessRule($serviceAccount,$permissionType,"Allow") #Build Access Rule
                $acl.SetAccessRule($buildAcl) #Add Access Rule
                Set-Acl $keyFullPath $acl #Save Access Rules
                Write-Host "[ $env:ComputerName ] - Access granted to $serviceAccount..." -ForegroundColor Green
                $certstatus = "Installed"
            }
            Else {
                Write-Host "[ $env:ComputerName ] - Unable to find Certificate that matches thumbprint $thumbprint or the private key is missing..." -ForegroundColor Red
            }
        }
        catch {
            Write-Host "[ $env:ComputerName ] - Unable to grant access to $serviceAccount..." -ForegroundColor Yellow
            throw $_;
        }
        if ($certstatus -eq "Installed"){
            # 2. Apply new cert to SQL
            ## Get Root SQL Path
            try {
                $root = (Get-ChildItem -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MS*$using:SQLInstance" -ErrorAction Stop).name -replace "HKEY_LOCAL_MACHINE","HKLM:"
                if ($root){
                    $keypath = Join-Path $root "\MSSQLServer\SuperSocketNetLib" -ErrorAction Stop
                    Write-Host "[ $env:ComputerName ] - Setting Registry $keypath Name: Certificate Value: $thumbprint"
                    Set-ItemProperty -Path $keyPath -Name "Certificate" -Type String -Value "$thumbprint" -ErrorAction Stop -Force
                    
                    Write-Host "[ $env:ComputerName ] - Setting Registry $keypath Name: ForceEncryption Value: 1"
                    Set-ItemProperty -Path $keyPath -Name "ForceEncryption" -Type DWord -Value "1" -ErrorAction Stop -Force
                }
                else {
                    Write-Host "[ $env:ComputerName ] - Unable to make registry changes - Couldn't determine path to update thumbprint." -ForegroundColor Red
                }
            }
            catch {
                Write-Host "[ $env:ComputerName ] - Unable to make registry changes..." -ForegroundColor Red
                throw $_;
            }

        }
        else {
            Write-Host "[ $env:ComputerName ] - Can't apply registry settings until certificate is installed." -ForegroundColor Yellow
        }
        

    }#end scriptblock

    foreach ($Computer in $ComputerName){
        Write-Host "[ $computer ] - Connecting to remote server."
        $session = New-PSSession $Computer -Credential $Credential
        
        $filename = (Get-Item $PfxLocalPath).Name
        Write-Host "[ $computer ] - Copying $file name to $RemoteFolder"
        Copy-Item -Path $PfxLocalPath -Destination $RemoteFolder -ToSession $session

        Invoke-Command -Session $session -ScriptBlock $scriptblock

        $session | Remove-PSSession
    }

}