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;Pooling=false" } 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;Pooling=false;" } } 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 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) $cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $conn) $dt = New-Object System.Data.DataTable $conn.open() $dt.Load($cmd.ExecuteReader()) $conn.Close() $conn.Dispose() return $dt } } catch { try { $conn.Close() $conn.Dispose() } catch { $null = 1 } 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) } } } |