internal/functions/Get-DecryptedObject.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
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)
        }
    }
}