SQL Server
SQL Server
Duplicate SQL Server ENCRYPTBYPASSPHRASE
See more Encryption Examples
Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.Chilkat SQL Server Downloads
-- 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
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
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
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
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
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
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
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
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
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