internal/functions/Get-DecryptedObject.ps1

function Get-DecryptedObject {
    <#
            .SYNOPSIS
                Internal function.
 
                This function is heavily based on Antti Rantasaari's script at http://goo.gl/wpqSib
                Antti Rantasaari 2014, NetSPI
                License: BSD 3-Clause http://opensource.org/licenses/BSD-3-Clause
            #>

    param (
        [Parameter(Mandatory)]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlInstance,
        [Parameter(Mandatory)]
        [ValidateSet("LinkedServer", "Credential")]
        [string]$Type,
        [switch]$EnableException
    )
    
    $server = $SqlInstance
    $sourceName = $server.Name
    
    # Query Service Master Key from the database - remove padding from the key
    # key_id 102 eq service master key, thumbprint 3 means encrypted with machinekey
    Write-Message -Level Verbose -Message "Querying service master key"
    $sql = "SELECT substring(crypt_property,9,len(crypt_property)-8) as smk FROM sys.key_encryptions WHERE key_id=102 and (thumbprint=0x03 or thumbprint=0x0300000001)"
    try {
        $smkbytes = $server.Query($sql).smk
    } catch {
        Stop-Function -Message "Can't execute query on $sourcename" -Target $server -ErrorRecord $_
        return
    }
    
    $sourceNetBios = Resolve-NetBiosName $server
    $instance = $server.InstanceName
    $serviceInstanceId = $server.ServiceInstanceId
    
    Write-Message -Level Verbose -Message "Get entropy from the registry - hopefully finds the right SQL server instance"
    
    try {
        [byte[]]$entropy = Invoke-Command2 -Raw -Credential $Credential -ComputerName $sourceNetBios -argumentlist $serviceInstanceId {
            $serviceInstanceId = $args[0]
            $entropy = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$serviceInstanceId\Security\" -ErrorAction Stop).Entropy
            return $entropy
        }
    } catch {
        Stop-Function -Message "Can't access registry keys on $sourceName. Do you have administrative access to the Windows registry on $SqlInstance Otherwise, we're out of ideas." -Target $source
        return
    }
    
    Write-Message -Level Verbose -Message "Decrypt the service master key"
    try {
        $serviceKey = Invoke-Command2 -Raw -Credential $Credential -ComputerName $sourceNetBios -ArgumentList $smkbytes, $Entropy {
            Add-Type -AssemblyName System.Security
            Add-Type -AssemblyName System.Core
            $smkbytes = $args[0]; $Entropy = $args[1]
            $serviceKey = [System.Security.Cryptography.ProtectedData]::Unprotect($smkbytes, $Entropy, 'LocalMachine')
            return $serviceKey
        }
    } catch {
        Stop-Function -Message "Can't unprotect registry data on $sourcename. Do you have administrative access to the Windows registry on $sourcename? Otherwise, we're out of ideas." -Target $source
        return
    }
    
    # Choose the encryption algorithm based on the SMK length - 3DES for 2008, AES for 2012
    # Choose IV length based on the algorithm
    Write-Message -Level Verbose -Message "Choose the encryption algorithm based on the SMK length - 3DES for 2008, AES for 2012"
    
    if (($serviceKey.Length -ne 16) -and ($serviceKey.Length -ne 32)) {
        Write-Message -Level Verbose -Message "ServiceKey found: $serviceKey.Length"
        Stop-Function -Message "Unknown key size. Do you have administrative access to the Windows registry on $sourcename? Otherwise, we're out of ideas." -Target $source
        return
    }
    
    if ($serviceKey.Length -eq 16) {
        $decryptor = New-Object System.Security.Cryptography.TripleDESCryptoServiceProvider
        $ivlen = 8
    } elseif ($serviceKey.Length -eq 32) {
        $decryptor = New-Object System.Security.Cryptography.AESCryptoServiceProvider
        $ivlen = 16
    }
    
    <#
                Query link server password information from the Db.
                Remove header from pwdhash, extract IV (as iv) and ciphertext (as pass)
                Ignore links with blank credentials (integrated auth ?)
            #>

    
    Write-Message -Level Verbose -Message "Query link server password information from the Db."
    
    try {
        if (-not $server.IsClustered) {
            $connString = "Server=ADMIN:$sourceNetBios\$instance;Trusted_Connection=True"
        } else {
            $dacEnabled = $server.Configuration.RemoteDacConnectionsEnabled.ConfigValue
            
            if ($dacEnabled -eq $false) {
                If ($Pscmdlet.ShouldProcess($server.Name, "Enabling DAC on clustered instance.")) {
                    Write-Message -Level Verbose -Message "DAC must be enabled for clusters, even when accessed from active node. Enabling."
                    $server.Configuration.RemoteDacConnectionsEnabled.ConfigValue = $true
                    $server.Configuration.Alter()
                }
            }
            
            $connString = "Server=ADMIN:$sourceName;Trusted_Connection=True"
        }
    } catch {
        Stop-Function -Message "Failure enabling DAC on $sourcename" -Target $source -ErrorRecord $_
    }
    
    <# NOTE: This query is accessing syslnklgns table. Can only be done via the DAC connection #>
    
    $sql = switch ($Type) {
        "LinkedServer" {
            "SELECT sysservers.srvname,
                    syslnklgns.Name,
                    substring(syslnklgns.pwdhash,5,$ivlen) iv,
                    substring(syslnklgns.pwdhash,$($ivlen + 5),
                    len(syslnklgns.pwdhash)-$($ivlen + 4)) pass
                FROM master.sys.syslnklgns
                    inner join master.sys.sysservers
                    on syslnklgns.srvid=sysservers.srvid
                WHERE len(pwdhash) > 0"

        }
        "Credential" {
            "SELECT QUOTENAME(name) AS name,credential_identity,substring(imageval,5,$ivlen) iv, substring(imageval,$($ivlen + 5),len(imageval)-$($ivlen + 4)) pass from sys.Credentials cred inner join sys.sysobjvalues obj on cred.credential_id = obj.objid where valclass=28 and valnum=2"
        }
    }
    
    Write-Message -Level Debug -Message $sql
    Write-Message -Level Verbose -Message "Get entropy from the registry"
    
    try {
        $results = Invoke-Command2 -ErrorAction Stop -Raw -Credential $Credential -ComputerName $sourceNetBios -ArgumentList $connString, $sql {
            $connString = $args[0]; $sql = $args[1]
            $conn = New-Object System.Data.SqlClient.SQLConnection($connString)
            $conn.open()
            $cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $conn);
            $dt = New-Object System.Data.DataTable
            $dt.Load($cmd.ExecuteReader())
            $conn.Close()
            $conn.Dispose()
            return $dt
        }
    } catch {
        Stop-Function -Message "Can't establish local DAC connection on $sourcename." -Target $server -ErrorRecord $_
        return
    }
    
    if ($server.IsClustered -and $dacEnabled -eq $false) {
        If ($Pscmdlet.ShouldProcess($server.Name, "Disabling DAC on clustered instance.")) {
            try {
                Write-Message -Level Verbose -Message "Setting DAC config back to 0."
                $server.Configuration.RemoteDacConnectionsEnabled.ConfigValue = $false
                $server.Configuration.Alter()
            } catch {
                Stop-Function -Message "Can't establish local DAC connection on $sourcename" -Target $server -ErrorRecord $_
                return
            }
        }
    }
    
    Write-Message -Level Verbose -Message "Go through each row in results"
    foreach ($result in $results) {
        # decrypt the password using the service master key and the extracted IV
        $decryptor.Padding = "None"
        $decrypt = $decryptor.Createdecryptor($serviceKey, $result.iv)
        $stream = New-Object System.IO.MemoryStream ( , $result.pass)
        $crypto = New-Object System.Security.Cryptography.CryptoStream $stream, $decrypt, "Write"
        
        $crypto.Write($result.pass, 0, $result.pass.Length)
        [byte[]]$decrypted = $stream.ToArray()
        
        # convert decrypted password to unicode
        $encode = New-Object System.Text.UnicodeEncoding
        
        # Print results - removing the weird padding (8 bytes in the front, some bytes at the end)...
        # Might cause problems but so far seems to work.. may be dependant on SQL server version...
        # If problems arise remove the next three lines..
        $i = 8; foreach ($b in $decrypted) { if ($decrypted[$i] -ne 0 -and $decrypted[$i + 1] -ne 0 -or $i -eq $decrypted.Length) { $i -= 1; break; }; $i += 1; }
        $decrypted = $decrypted[8 .. $i]
        
        if ($Type -eq "LinkedServer") {
            $name = $result.srvname
            $identity = $result.Name
        } else {
            $name = $result.name
            $identity = $result.credential_identity
        }
        [pscustomobject]@{
            Name     = $name
            Identity = $identity
            Password = $encode.GetString($decrypted)
        }
    }
}