(SQL Server) 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

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- 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.

    DECLARE @password nvarchar(4000)
    SELECT @password = 'tEst1234'
    DECLARE @encryptedHex_v1 nvarchar(4000)
    SELECT @encryptedHex_v1 = '0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB'

    -- Here's an encrypted string using AES256/SHA256
    DECLARE @encryptedHex_v2 nvarchar(4000)
    SELECT @encryptedHex_v2 = '0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429'

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

    DECLARE @success int
    EXEC sp_OAMethod @sbEncHex, 'Append', @success OUT, @encryptedHex_v1

    -- If present, we don't want the leading "0x"
    EXEC sp_OAMethod @sbEncHex, 'StartsWith', @iTmp0 OUT, '0x', 0
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @sbEncHex, 'RemoveCharsAt', @success OUT, 0, 2
      END

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @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.
    DECLARE @v1 int
    EXEC sp_OAMethod @sbEncHex, 'StartsWith', @v1 OUT, '01', 0

    DECLARE @ivLen int
    SELECT @ivLen = 0
    DECLARE @hashAlg nvarchar(4000)

    IF @v1 = 1
      BEGIN
        EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', '3des'
        EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
        EXEC sp_OASetProperty @crypt, 'KeyLength', 168
        SELECT @ivLen = 8
        SELECT @hashAlg = 'sha1'
      END
    ELSE
      BEGIN
        EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
        EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
        EXEC sp_OASetProperty @crypt, 'KeyLength', 256
        SELECT @ivLen = 16
        SELECT @hashAlg = 'sha256'
      END

    -- Remove the SQL Server version info (i.e. the "01000000")
    EXEC sp_OAMethod @sbEncHex, 'RemoveCharsAt', @success OUT, 0, 8

    -- Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
    DECLARE @ivHex nvarchar(4000)
    EXEC sp_OAMethod @sbEncHex, 'GetRange', @ivHex OUT, 0, @ivLen * 2, 1

    PRINT 'IV = ' + @ivHex
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex, 'hex'

    DECLARE @sbPassword int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPassword OUT

    EXEC sp_OAMethod @sbPassword, 'Append', @success OUT, @password
    DECLARE @pwd_hash nvarchar(4000)
    EXEC sp_OAMethod @sbPassword, 'GetHash', @pwd_hash OUT, @hashAlg, 'hex', 'utf-16'
    DECLARE @sbKey int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbKey OUT

    EXEC sp_OAMethod @sbKey, 'Append', @success OUT, @pwd_hash
    IF @v1 = 1
      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)
        EXEC sp_OAMethod @sbKey, 'Shorten', @success OUT, 8
      END


    EXEC sp_OAMethod @sbKey, 'GetAsString', @sTmp0 OUT
    PRINT 'crypt key: ' + @sTmp0

    EXEC sp_OAMethod @sbKey, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'hex'

    -- Decrypt
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @sbEncHex, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @sTmp0, 'hex'
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @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...
    EXEC sp_OAMethod @bd, 'RemoveChunk', @success OUT, 0, 8
    DECLARE @plainText nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetString', @plainText OUT, '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

    DECLARE @encryptor int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @encryptor OUT

    EXEC sp_OASetProperty @encryptor, 'EncodingMode', 'hex'

    EXEC sp_OASetProperty @encryptor, 'CryptAlgorithm', '3des'
    EXEC sp_OASetProperty @encryptor, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @encryptor, 'KeyLength', 168

    -- Generate a random 8-byte IV
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    EXEC sp_OAMethod @prng, 'GenRandom', @ivHex OUT, 8, 'hex'
    EXEC sp_OAMethod @encryptor, 'SetEncodedIV', NULL, @ivHex, 'hex'

    -- The binary password is generated the same as above.
    -- We'll use the same password (and same binary password)
    EXEC sp_OAMethod @sbKey, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @encryptor, 'SetEncodedKey', NULL, @sTmp0, 'hex'

    DECLARE @plainTextLen int
    SELECT @plainTextLen = 8
    SELECT @plainText = 'ABCD1234'

    -- Encrypt the header + the plain-text.
    DECLARE @bdData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdData OUT

    EXEC sp_OAMethod @bdData, 'AppendEncoded', @success OUT, '0DF0ADBA', 'hex'
    EXEC sp_OAMethod @bdData, 'AppendEncoded', @success OUT, '0000', 'hex'
    EXEC sp_OAMethod @bdData, 'AppendInt2', @success OUT, @plainTextLen, 1

    EXEC sp_OAMethod @bdData, 'GetEncoded', @sTmp0 OUT, 'hex'
    PRINT 'header: ' + @sTmp0
    EXEC sp_OAMethod @bdData, 'AppendString', @success OUT, @plainText, 'utf-8'
    EXEC sp_OAMethod @encryptor, 'EncryptBd', @success OUT, @bdData

    -- Compose the result..
    DECLARE @sbEnc int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbEnc OUT

    EXEC sp_OAMethod @sbEnc, 'Append', @success OUT, '0x01000000'
    EXEC sp_OAMethod @sbEnc, 'Append', @success OUT, @ivHex
    EXEC sp_OAMethod @bdData, 'GetEncoded', @sTmp0 OUT, 'hex'
    EXEC sp_OAMethod @sbEnc, 'Append', @success OUT, @sTmp0


    EXEC sp_OAMethod @sbEnc, 'GetAsString', @sTmp0 OUT
    PRINT 'result: ' + @sTmp0

    EXEC @hr = sp_OADestroy @sbEncHex
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbPassword
    EXEC @hr = sp_OADestroy @sbKey
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @encryptor
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @bdData
    EXEC @hr = sp_OADestroy @sbEnc


END
GO