(SQL Server) Encrypt File in Chunks using AES CBC

Demonstrates how to use the FirstChunk/LastChunk properties to encrypt a file chunk-by-chunk.

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
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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'

    DECLARE @fileToEncrypt nvarchar(4000)
    SELECT @fileToEncrypt = 'qa_data/hamlet.xml'
    DECLARE @facIn int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @facIn OUT

    DECLARE @success int
    EXEC sp_OAMethod @facIn, 'OpenForRead', @success OUT, @fileToEncrypt
    IF @success <> 1
      BEGIN

        PRINT 'Failed to open file that is to be encrytped.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @facIn
        RETURN
      END

    DECLARE @outputEncryptedFile nvarchar(4000)
    SELECT @outputEncryptedFile = 'qa_output/hamlet.enc'
    DECLARE @facOutEnc int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @facOutEnc OUT

    EXEC sp_OAMethod @facOutEnc, 'OpenForWrite', @success OUT, @outputEncryptedFile
    IF @success <> 1
      BEGIN

        PRINT 'Failed to encrypted output file.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @facIn
        EXEC @hr = sp_OADestroy @facOutEnc
        RETURN
      END

    -- Let's encrypt in 10000 byte chunks.
    DECLARE @chunkSize int
    SELECT @chunkSize = 10000
    DECLARE @numChunks int
    EXEC sp_OAMethod @facIn, 'GetNumBlocks', @numChunks OUT, @chunkSize

    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0

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

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numChunks
      BEGIN
        SELECT @i = @i + 1
        IF @i = @numChunks
          BEGIN
            EXEC sp_OASetProperty @crypt, 'LastChunk', 1
          END

        -- Read the next chunk from the file.
        -- The last chunk will be whatever amount remains in the file..
        EXEC sp_OAMethod @bd, 'Clear', @success OUT
        EXEC sp_OAMethod @facIn, 'FileReadBd', @success OUT, @chunkSize, @bd

        -- Encrypt.
        EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bd

        -- Write the encrypted chunk to the output file.
        EXEC sp_OAMethod @facOutEnc, 'FileWriteBd', @success OUT, @bd, 0, 0

        EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
      END

    -- Make sure both FirstChunk and LastChunk are restored to 1 after
    -- encrypting or decrypting in chunks.  Otherwise subsequent encryptions/decryptions
    -- will produce unexpected results.
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 1

    EXEC sp_OAMethod @facIn, 'FileClose', NULL
    EXEC sp_OAMethod @facOutEnc, 'FileClose', NULL

    -- Decrypt the encrypted output file in a single call using CBC mode:
    DECLARE @decryptedFile nvarchar(4000)
    SELECT @decryptedFile = 'qa_output/hamlet_dec.xml'
    EXEC sp_OAMethod @crypt, 'CkDecryptFile', @success OUT, @outputEncryptedFile, @decryptedFile
    -- Assume success for the example..

    -- Compare the contents of the decrypted file with the original file:
    DECLARE @bSame int
    EXEC sp_OAMethod @facIn, 'FileContentsEqual', @bSame OUT, @fileToEncrypt, @decryptedFile

    PRINT 'bSame = ' + @bSame

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @facIn
    EXEC @hr = sp_OADestroy @facOutEnc
    EXEC @hr = sp_OADestroy @bd


END
GO