(DataFlex) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

Use ChilkatAx-9.5.0-win32.pkg

Procedure Test
    String sPassword
    String sEncryptedHex_v1
    String sEncryptedHex_v2
    Handle hoSbEncHex
    Boolean iSuccess
    Handle hoCrypt
    Boolean iV1
    Integer iIvLen
    String sHashAlg
    String sIvHex
    Handle hoSbPassword
    String sPwd_hash
    Handle hoSbKey
    Variant vBd
    Handle hoBd
    String sPlainText
    Handle hoEncryptor
    Handle hoPrng
    Integer iPlainTextLen
    Variant vBd
Data    Handle hoBdData
    Handle hoSbEnc
    String sTemp1
    Boolean bTemp1

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

    Move "tEst1234" To sPassword
    Move "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB" To sEncryptedHex_v1

    // Here's an encrypted string using AES256/SHA256
    Move "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429" To sEncryptedHex_v2

    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbEncHex
    If (Not(IsComObjectCreated(hoSbEncHex))) Begin
        Send CreateComObject of hoSbEncHex
    End
    Get ComAppend Of hoSbEncHex sEncryptedHex_v1 To iSuccess

    // If present, we don't want the leading "0x"
    Get ComStartsWith Of hoSbEncHex "0x" False To bTemp1
    If (bTemp1 = True) Begin
        Get ComRemoveCharsAt Of hoSbEncHex 0 2 To iSuccess
    End

    Get Create (RefClass(cComChilkatCrypt2)) To hoCrypt
    If (Not(IsComObjectCreated(hoCrypt))) Begin
        Send CreateComObject of hoCrypt
    End
    Set ComEncodingMode Of hoCrypt To "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.
    Get ComStartsWith Of hoSbEncHex "01" False To iV1

    Move 0 To iIvLen

    If (iV1 = True) Begin
        Set ComCryptAlgorithm Of hoCrypt To "3des"
        Set ComCipherMode Of hoCrypt To "cbc"
        Set ComKeyLength Of hoCrypt To 168
        Move 8 To iIvLen
        Move "sha1" To sHashAlg
    End
    Else Begin
        Set ComCryptAlgorithm Of hoCrypt To "aes"
        Set ComCipherMode Of hoCrypt To "cbc"
        Set ComKeyLength Of hoCrypt To 256
        Move 16 To iIvLen
        Move "sha256" To sHashAlg
    End

    // Remove the SQL Server version info (i.e. the "01000000")
    Get ComRemoveCharsAt Of hoSbEncHex 0 8 To iSuccess

    // Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
    Get ComGetRange Of hoSbEncHex 0 (iIvLen * 2) True To sIvHex
    Showln "IV = " sIvHex
    Send ComSetEncodedIV To hoCrypt sIvHex "hex"

    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbPassword
    If (Not(IsComObjectCreated(hoSbPassword))) Begin
        Send CreateComObject of hoSbPassword
    End
    Get ComAppend Of hoSbPassword sPassword To iSuccess
    Get ComGetHash Of hoSbPassword sHashAlg "hex" "utf-16" To sPwd_hash
    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbKey
    If (Not(IsComObjectCreated(hoSbKey))) Begin
        Send CreateComObject of hoSbKey
    End
    Get ComAppend Of hoSbKey sPwd_hash To iSuccess
    If (iV1 = True) Begin
        // 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)
        Get ComShorten Of hoSbKey 8 To iSuccess
    End

    Get ComGetAsString Of hoSbKey To sTemp1
    Showln "crypt key: " sTemp1

    Get ComGetAsString Of hoSbKey To sTemp1
    Send ComSetEncodedKey To hoCrypt sTemp1 "hex"

    // Decrypt
    Get Create (RefClass(cComChilkatBinData)) To hoBd
    If (Not(IsComObjectCreated(hoBd))) Begin
        Send CreateComObject of hoBd
    End
    Get ComGetAsString Of hoSbEncHex To sTemp1
    Get ComAppendEncoded Of hoBd sTemp1 "hex" To iSuccess
    Get pvComObject of hoBd to vBd
    Get ComDecryptBd Of hoCrypt vBd To iSuccess

    // 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...
    Get ComRemoveChunk Of hoBd 0 8 To iSuccess
    Get ComGetString Of hoBd "utf-8" To sPlainText
    Showln "decrypted plain text: " sPlainText

    // 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

    Get Create (RefClass(cComChilkatCrypt2)) To hoEncryptor
    If (Not(IsComObjectCreated(hoEncryptor))) Begin
        Send CreateComObject of hoEncryptor
    End
    Set ComEncodingMode Of hoEncryptor To "hex"

    Set ComCryptAlgorithm Of hoEncryptor To "3des"
    Set ComCipherMode Of hoEncryptor To "cbc"
    Set ComKeyLength Of hoEncryptor To 168

    // Generate a random 8-byte IV
    Get Create (RefClass(cComChilkatPrng)) To hoPrng
    If (Not(IsComObjectCreated(hoPrng))) Begin
        Send CreateComObject of hoPrng
    End
    Get ComGenRandom Of hoPrng 8 "hex" To sIvHex
    Send ComSetEncodedIV To hoEncryptor sIvHex "hex"

    // The binary password is generated the same as above.
    // We'll use the same password (and same binary password)
    Get ComGetAsString Of hoSbKey To sTemp1
    Send ComSetEncodedKey To hoEncryptor sTemp1 "hex"

    Move 8 To iPlainTextLen
    Move "ABCD1234" To sPlainText

    // Encrypt the header + the plain-text.
    Get Create (RefClass(cComChilkatBinData)) To hoBdData
    If (Not(IsComObjectCreated(hoBdData))) Begin
        Send CreateComObject of hoBdData
    End
    Get ComAppendEncoded Of hoBdData "0DF0ADBA" "hex" To iSuccess
    Get ComAppendEncoded Of hoBdData "0000" "hex" To iSuccess
    Get ComAppendInt2 Of hoBdData iPlainTextLen True To iSuccess
    Get ComGetEncoded Of hoBdData "hex" To sTemp1
    Showln "header: " sTemp1
    Get ComAppendString Of hoBdData sPlainText "utf-8" To iSuccess
    Get pvComObject of hoBdData to vBdData
    Get ComEncryptBd Of hoEncryptor vBdData To iSuccess

    // Compose the result..
    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbEnc
    If (Not(IsComObjectCreated(hoSbEnc))) Begin
        Send CreateComObject of hoSbEnc
    End
    Get ComAppend Of hoSbEnc "0x01000000" To iSuccess
    Get ComAppend Of hoSbEnc sIvHex To iSuccess
    Get ComGetEncoded Of hoBdData "hex" To sTemp1
    Get ComAppend Of hoSbEnc sTemp1 To iSuccess

    Get ComGetAsString Of hoSbEnc To sTemp1
    Showln "result: " sTemp1


End_Procedure