(SQL Server) Encrypt / Decrypt Secure Strings

Demonstrates how to use the EncryptSecureENC and DecryptSecureENC methods to encrypt/decrypt secure strings. These methods were added in Chilkat v9.5.0.71 (released January 2018).

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 requires the Chilkat Crypt API to have been previously unlocked.
    -- See Unlock Chilkat Crypt for sample code.

    -- Load the secure string with some text.
    DECLARE @secStr1 int
    -- Use "Chilkat_9_5_0.SecureString" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @secStr1 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @secStr1, 'LoadFile', @success OUT, 'qa_data/txt/helloWorld.txt', 'utf-8'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load helloWorld.txt'
        EXEC @hr = sp_OADestroy @secStr1
        RETURN
      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, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    -- Return the base64 encoded encrypted contents of secStr1.
    DECLARE @encryptedStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptSecureENC', @encryptedStr OUT, @secStr1

    PRINT 'Encrypted string: ' + @encryptedStr

    -- Output:
    -- Encrypted string: qiq+IFhcjTkEIkZyf31V/g==

    -- Decrypt to secStr2:
    DECLARE @secStr2 int
    -- Use "Chilkat_9_5_0.SecureString" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @secStr2 OUT

    EXEC sp_OAMethod @crypt, 'DecryptSecureENC', @success OUT, @encryptedStr, @secStr2

    -- Access the contents of secStr2

    EXEC sp_OAMethod @secStr2, 'Access', @sTmp0 OUT
    PRINT 'Decrypted string: ' + @sTmp0

    -- Output:
    -- Decrypted string: Hello World!

    EXEC @hr = sp_OADestroy @secStr1
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @secStr2


END
GO