PowerShell

Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE. PowerShell

Add-Type -Path "C:\chilkat\ChilkatDotNet47-x64\ChilkatDotNet47.dll"

# This example requires the Chilkat API to have been previously unlocked.
# See Global Unlock Sample for sample code.

# For SQL Server 2008 - SQL Server 2016 we must use TripleDES with SHA1
# For SQL Server 2017 and later, use AES256 / SHA256.

$password = "tEst1234"
$encryptedHex_v1 = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

# Here's an encrypted string using AES256/SHA256
$encryptedHex_v2 = "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429"

$sbEncHex = New-Object Chilkat.StringBuilder
$sbEncHex.Append($encryptedHex_v1)

# If present, we don't want the leading "0x"
if ($sbEncHex.StartsWith("0x",$false) -eq $true) {
    $sbEncHex.RemoveCharsAt(0,2)
}

$crypt = New-Object Chilkat.Crypt2
$crypt.EncodingMode = "hex"

# The encrypted hex string will begin with either 01000000 or 02000000
# version 1 is produced by SQL Server 2008 to SQL Server 2016, and we must use TripleDES with SHA1
# version 2 is for SQL Server 2017 and later, and uses AES256 / SHA256.
$v1 = $sbEncHex.StartsWith("01",$false)

$ivLen = 0

if ($v1 -eq $true) {
    $crypt.CryptAlgorithm = "3des"
    $crypt.CipherMode = "cbc"
    $crypt.KeyLength = 168
    $ivLen = 8
    $hashAlg = "sha1"
}
else {
    $crypt.CryptAlgorithm = "aes"
    $crypt.CipherMode = "cbc"
    $crypt.KeyLength = 256
    $ivLen = 16
    $hashAlg = "sha256"
}

# Remove the SQL Server version info (i.e. the "01000000")
$sbEncHex.RemoveCharsAt(0,8)

# Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
$ivHex = $sbEncHex.GetRange(0,$ivLen * 2,$true)
$("IV = " + $ivHex)
$crypt.SetEncodedIV($ivHex,"hex")

$sbPassword = New-Object Chilkat.StringBuilder
$sbPassword.Append($password)
$pwd_hash = $sbPassword.GetHash($hashAlg,"hex","utf-16")
$sbKey = New-Object Chilkat.StringBuilder
$sbKey.Append($pwd_hash)
if ($v1 -eq $true) {
    # For v1, we only want the 1st 16 bytes of the 20 byte hash.
    # (remember, the hex encoding uses 2 chars per byte, so we remove the last 8 chars)
    $sbKey.Shorten(8)
}

$("crypt key: " + $sbKey.GetAsString())

$crypt.SetEncodedKey($sbKey.GetAsString(),"hex")

# Decrypt
$bd = New-Object Chilkat.BinData
$bd.AppendEncoded($sbEncHex.GetAsString(),"hex")
$crypt.DecryptBd($bd)

# The result is composed of a header of 8 bytes which we can discard.
# The remainder is the decrypted text.

# The header we are discarding is composed of:
# Bytes 0-3: Magic number equal to 0DF0ADBA
# Bytes 4-5: Number of integrity bytes, which is 0 unless an authenticator is used. We're assuming no authenticator is used.
# Bytes 6-7: Number of plain-text bytes. We really don't need this because the CBC padding takes care of it.

# Therefore, just return the data after the 1st 8 bytes.
# Assuming the encrypted string was utf-8 text...
$bd.RemoveChunk(0,8)
$plainText = $bd.GetString("utf-8")
$("decrypted plain text: " + $plainText)

# The output:

# IV = 1E8E7DCDBD4061B9
# crypt key: 710B9C2E61ACCC9570D4112203BD9738
# decrypted plain text: Hello world.

# ------------------------------------------------------------------------------------------
# To encrypt, do the reverse...

# Let's do v1 with TripleDES with SHA1

$encryptor = New-Object Chilkat.Crypt2
$encryptor.EncodingMode = "hex"

$encryptor.CryptAlgorithm = "3des"
$encryptor.CipherMode = "cbc"
$encryptor.KeyLength = 168

# Generate a random 8-byte IV
$prng = New-Object Chilkat.Prng
$ivHex = $prng.GenRandom(8,"hex")
$encryptor.SetEncodedIV($ivHex,"hex")

# The binary password is generated the same as above.
# We'll use the same password (and same binary password)
$encryptor.SetEncodedKey($sbKey.GetAsString(),"hex")

$plainTextLen = 8
$plainText = "ABCD1234"

# Encrypt the header + the plain-text.
$bdData = New-Object Chilkat.BinData
$bdData.AppendEncoded("0DF0ADBA","hex")
$bdData.AppendEncoded("0000","hex")
$bdData.AppendInt2($plainTextLen,$true)
$("header: " + $bdData.GetEncoded("hex"))
$bdData.AppendString($plainText,"utf-8")
$encryptor.EncryptBd($bdData)

# Compose the result..
$sbEnc = New-Object Chilkat.StringBuilder
$sbEnc.Append("0x01000000")
$sbEnc.Append($ivHex)
$sbEnc.Append($bdData.GetEncoded("hex"))

$("result: " + $sbEnc.GetAsString())