(SQL Server) Tips on Matching Encryption with another System

This example provides tips on matching encryption results produced by another system.

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

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Let's examine 256-bit AES encryption in CBC mode.
    -- CBC mode is Cipher Block Chaining, and it uses an IV (initialization vector)
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
    DECLARE @ivHex1 nvarchar(4000)
    SELECT @ivHex1 = '000102030405060708090A0B0C0D0E0F'
    DECLARE @ivHex2 nvarchar(4000)
    SELECT @ivHex2 = 'FF0102030405060708090A0B0C0D0E0F'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex1, 'hex'
    DECLARE @keyHex nvarchar(4000)
    SELECT @keyHex = '000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @keyHex, 'hex'

    -- Matching encryption requires all of the above settings to be matched exactly.
    -- Let's get our output in hex format so we can easily see the values of the encrypted bytes.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    -- Encrypt something small:
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'Hello'
    PRINT @sTmp0
    -- The result is 5B827AB3B4F9F2292C2B74C8A6C99A3D
    -- This 16 bytes -- exactly one AES encryption block.

    -- Let's change only the padding scheme.
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 3

    -- Encrypt again:
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'Hello'
    PRINT @sTmp0
    -- The result is entirely different: 469C28CC576069F807891FEE2DE76D68

    -- The padding scheme only affects the very last block of output.  Therefore,
    -- if all settings match except for the padding scheme, we're unable to
    -- know if we encrypt a very small amount of data. However, if we encrypt
    -- a larger amount of data, the single difference becomes apparent:

    PRINT '-- Only the padding scheme differs --'
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 3
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0

    -- Now examine the outputs:
    -- F6A201F8E0B6595FA20E4A212A2AD9A5046DAF29E8B35AD15CEE56A1A69F2A3A7B347A7C15E26E7A6760533C7A8E0D44
    -- F6A201F8E0B6595FA20E4A212A2AD9A5046DAF29E8B35AD15CEE56A1A69F2A3A292CA61D03A85E1AC39B50D4DA71691E
    -- We can see the output matches except for the last block, which is affected by the padding scheme.

    -- If we are able to easily use ECB mode w/ the other system
    -- we are trying to match, then eliminate the IV from the picture.
    -- If the encryption matches in ECB mode, but not in CBC mode,
    -- then we know all correct except for the IV.
    -- For example, you can see how the IV changes everything with CBC mode,
    -- but it's not used in ECB mode:
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'

    PRINT '-- Only the IV differs, CBC mode produces different output. --'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex1, 'hex'
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex2, 'hex'
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0

    EXEC sp_OASetProperty @crypt, 'CipherMode', 'ecb'

    PRINT '-- Only the IV differs, ECB does not use the IV.  The outputs are the same. --'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex1, 'hex'
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex2, 'hex'
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, 'HelloHelloHelloHelloHelloHelloHello'
    PRINT @sTmp0

    -- If we can eliminate the padding scheme and IV from the degrees of freedom,
    -- then the only remaining likely differences are (1) the secret key,
    -- and (2) the input data itself.

    -- The secret key is composed of binary bytes of exactly KeyLength bits.
    -- For 256-bit AES encrytion, the key length is 256, and therefore the 
    -- secret key is exactly 32 bytes.  (32 * 8 bits/byte = 256 bits)
    -- If the secret key is derived from an arbitrary password string, then one must
    -- exactly duplicate the derivation scheme (such as PBKDF2, for example)
    -- The input bytes to the derivation scheme must also match.  For example,
    -- is it the utf-8 byte representation of the password string that is used
    -- as the starting point for the derivation, or perhaps utf-16, or ANSI (1 byte per char)?

    -- Likewise, if the data being encrypted is a string, what byte representation of
    -- the string is being encrypted?  If the bytes presented to the encryptor are different,
    -- then the output is different.

    EXEC @hr = sp_OADestroy @crypt


END
GO