(CkPython) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat Python Downloads install with pip pip3 install chilkat or download... Python Module for Windows, MacOS, Linux, Alpine Linux, Solaris

import chilkat

# 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 = chilkat.CkStringBuilder()
sbEncHex.Append(encryptedHex_v1)

# If present, we don't want the leading "0x"
if (sbEncHex.StartsWith("0x",False) == True):
    sbEncHex.RemoveCharsAt(0,2)

crypt = chilkat.CkCrypt2()
crypt.put_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 == True):
    crypt.put_CryptAlgorithm("3des")
    crypt.put_CipherMode("cbc")
    crypt.put_KeyLength(168)
    ivLen = 8
    hashAlg = "sha1"
else:
    crypt.put_CryptAlgorithm("aes")
    crypt.put_CipherMode("cbc")
    crypt.put_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)
print("IV = " + ivHex)
crypt.SetEncodedIV(ivHex,"hex")

sbPassword = chilkat.CkStringBuilder()
sbPassword.Append(password)
pwd_hash = sbPassword.getHash(hashAlg,"hex","utf-16")
sbKey = chilkat.CkStringBuilder()
sbKey.Append(pwd_hash)
if (v1 == 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)

print("crypt key: " + sbKey.getAsString())

crypt.SetEncodedKey(sbKey.getAsString(),"hex")

# Decrypt
bd = chilkat.CkBinData()
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")
print("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 = chilkat.CkCrypt2()
encryptor.put_EncodingMode("hex")

encryptor.put_CryptAlgorithm("3des")
encryptor.put_CipherMode("cbc")
encryptor.put_KeyLength(168)

# Generate a random 8-byte IV
prng = chilkat.CkPrng()
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 = chilkat.CkBinData()
bdData.AppendEncoded("0DF0ADBA","hex")
bdData.AppendEncoded("0000","hex")
bdData.AppendInt2(plainTextLen,True)
print("header: " + bdData.getEncoded("hex"))
bdData.AppendString(plainText,"utf-8")
encryptor.EncryptBd(bdData)

# Compose the result..
sbEnc = chilkat.CkStringBuilder()
sbEnc.Append("0x01000000")
sbEnc.Append(ivHex)
sbEnc.Append(bdData.getEncoded("hex"))

print("result: " + sbEnc.getAsString())