(SQL Server) PBES2 Password-Based Encryption

See more Encryption Examples

For more information, see https://www.chilkatsoft.com/explaining_pbes2_encryption.asp

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
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set properties for PBES2 encryption:

    -- When the encryption algorithm is set to "pbes2",
    -- Chilkat will automatically derive the actual secret key using PBKDF2 and
    -- the specified paramters: password, salt, iteration count, hash function, and desired key length.

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pbes2'
    EXEC sp_OASetProperty @crypt, 'PbesPassword', 'mySecretPassword'

    -- Set the underlying PBE algorithm (and key length):
    EXEC sp_OASetProperty @crypt, 'PbesAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256

    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

    -- Give it some salt:
    EXEC sp_OAMethod @crypt, 'SetEncodedSalt', NULL, '0102030405060708', 'hex'

    -- A higher iteration count makes the algorithm more
    -- computationally expensive and therefore exhaustive
    -- searches (for breaking the encryption) is more difficult:
    EXEC sp_OASetProperty @crypt, 'IterationCount', 1024

    -- A hash algorithm needs to be set for PBES2:
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    -- Indicate that the encrypted bytes should be returned
    -- as a hex string:
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    DECLARE @plainText nvarchar(4000)
    SELECT @plainText = 'To be encrypted.'

    DECLARE @encryptedText nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @encryptedText OUT, @plainText


    PRINT @encryptedText

    -- Now decrypt:
    DECLARE @decryptedText nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @decryptedText OUT, @encryptedText


    PRINT @decryptedText

    EXEC @hr = sp_OADestroy @crypt


END
GO