(SQL Server) Encrypting a Data Stream

This example illustrates encrypting a large dataset using symmetric encryption algorithms like AES, ChaCha20, Blowfish, etc. The data is processed in chunks using the EncryptStringENC method. The FirstChunk and LastChunk properties identify whether a chunk is the first, intermediate, or last in the encrypted stream. By default, both properties are set to 1, indicating that the data provided represents the entire dataset.

You can provide data chunks smaller than the encryption algorithm's block size. For example, AES uses 16-byte blocks; 3DES, and Blowfish use 8-byte blocks; and ChaCha20, as a streaming algorithm, effectively has a block size of 1 byte.

Chilkat handles the buffering of data as needed. If a data chunk smaller than the block size is provided, an empty string (or 0 bytes) is returned, and the data is buffered to be combined with subsequent chunks. The final chunk is padded to the algorithm's block size (as determined by the PaddingScheme property) to complete the encryption process.

Chunk-wise decryption follows the same procedure as chunk-wise encryption.

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 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

    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', 'hex'
    DECLARE @txt1 nvarchar(4000)
    SELECT @txt1 = 'The quick brown fox jumped over the lazy dog.' + CHAR(13) + CHAR(10)
    DECLARE @txt2 nvarchar(4000)
    SELECT @txt2 = '-' + CHAR(13) + CHAR(10)
    DECLARE @txt3 nvarchar(4000)
    SELECT @txt3 = 'Done.' + CHAR(13) + CHAR(10)

    DECLARE @sbEncrypted int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbEncrypted OUT

    -- Encrypt the 1st chunk:
    -- (don't worry about feeding the data to the encryptor in 
    -- exact multiples of the encryption algorithm's block size.
    -- Chilkat will buffer the data.)
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    DECLARE @success int
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt1
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

    -- Encrypt the 2nd chunk
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt2
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

    -- Now encrypt N more chunks...
    -- Remember -- we're doing this in CBC mode, so each call
    -- to the encrypt method depends on the state from previous
    -- calls...
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= 4
      BEGIN
        EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt1
        EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0
        EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt2
        EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0
        SELECT @i = @i + 1
      END

    -- Now encrypt the last chunk:
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 1
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt3
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbEncrypted, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Now decrypt in one call.
    -- (The data we're decrypting is both the first AND last chunk.)  
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 1
    DECLARE @decryptedText nvarchar(4000)
    EXEC sp_OAMethod @sbEncrypted, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @decryptedText OUT, @sTmp0


    PRINT @decryptedText

    -- Note: You may decrypt in N chunks by setting the FirstChunk
    -- and LastChunk properties prior to calling the Decrypt* methods

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbEncrypted


END
GO