(Tcl) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat Tcl Extension Downloads Chilkat Tcl Extension Downloads

load ./chilkat.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.

set password "tEst1234"
set encryptedHex_v1 "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

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

set sbEncHex [new_CkStringBuilder]

CkStringBuilder_Append $sbEncHex $encryptedHex_v1

# If present, we don't want the leading "0x"
if {[CkStringBuilder_StartsWith $sbEncHex "0x" 0] == 1} then {
    CkStringBuilder_RemoveCharsAt $sbEncHex 0 2
}

set crypt [new_CkCrypt2]

CkCrypt2_put_EncodingMode $crypt "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.
set v1 [CkStringBuilder_StartsWith $sbEncHex "01" 0]

set ivLen 0

if {$v1 == 1} then {
    CkCrypt2_put_CryptAlgorithm $crypt "3des"
    CkCrypt2_put_CipherMode $crypt "cbc"
    CkCrypt2_put_KeyLength $crypt 168
    set ivLen 8
    set hashAlg "sha1"
} else {
    CkCrypt2_put_CryptAlgorithm $crypt "aes"
    CkCrypt2_put_CipherMode $crypt "cbc"
    CkCrypt2_put_KeyLength $crypt 256
    set ivLen 16
    set hashAlg "sha256"
}

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

# Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
set ivHex [CkStringBuilder_getRange $sbEncHex 0 [expr $ivLen * 2] 1]
puts "IV = $ivHex"
CkCrypt2_SetEncodedIV $crypt $ivHex "hex"

set sbPassword [new_CkStringBuilder]

CkStringBuilder_Append $sbPassword $password
set pwd_hash [CkStringBuilder_getHash $sbPassword $hashAlg "hex" "utf-16"]
set sbKey [new_CkStringBuilder]

CkStringBuilder_Append $sbKey $pwd_hash
if {$v1 == 1} then {
    # 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)
    CkStringBuilder_Shorten $sbKey 8
}

puts "crypt key: [CkStringBuilder_getAsString $sbKey]"

CkCrypt2_SetEncodedKey $crypt [CkStringBuilder_getAsString $sbKey] "hex"

# Decrypt
set bd [new_CkBinData]

CkBinData_AppendEncoded $bd [CkStringBuilder_getAsString $sbEncHex] "hex"
CkCrypt2_DecryptBd $crypt $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...
CkBinData_RemoveChunk $bd 0 8
set plainText [CkBinData_getString $bd "utf-8"]
puts "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

set encryptor [new_CkCrypt2]

CkCrypt2_put_EncodingMode $encryptor "hex"

CkCrypt2_put_CryptAlgorithm $encryptor "3des"
CkCrypt2_put_CipherMode $encryptor "cbc"
CkCrypt2_put_KeyLength $encryptor 168

# Generate a random 8-byte IV
set prng [new_CkPrng]

set ivHex [CkPrng_genRandom $prng 8 "hex"]
CkCrypt2_SetEncodedIV $encryptor $ivHex "hex"

# The binary password is generated the same as above.
# We'll use the same password (and same binary password)
CkCrypt2_SetEncodedKey $encryptor [CkStringBuilder_getAsString $sbKey] "hex"

set plainTextLen 8
set plainText "ABCD1234"

# Encrypt the header + the plain-text.
set bdData [new_CkBinData]

CkBinData_AppendEncoded $bdData "0DF0ADBA" "hex"
CkBinData_AppendEncoded $bdData "0000" "hex"
CkBinData_AppendInt2 $bdData $plainTextLen 1
puts "header: [CkBinData_getEncoded $bdData hex]"
CkBinData_AppendString $bdData $plainText "utf-8"
CkCrypt2_EncryptBd $encryptor $bdData

# Compose the result..
set sbEnc [new_CkStringBuilder]

CkStringBuilder_Append $sbEnc "0x01000000"
CkStringBuilder_Append $sbEnc $ivHex
CkStringBuilder_Append $sbEnc [CkBinData_getEncoded $bdData "hex"]

puts "result: [CkStringBuilder_getAsString $sbEnc]"

delete_CkStringBuilder $sbEncHex
delete_CkCrypt2 $crypt
delete_CkStringBuilder $sbPassword
delete_CkStringBuilder $sbKey
delete_CkBinData $bd
delete_CkCrypt2 $encryptor
delete_CkPrng $prng
delete_CkBinData $bdData
delete_CkStringBuilder $sbEnc