(SQL Server) AES Encrypt and Decrypt a File

SQL Server demonstrates how to AES encrypt a file of any size, and then decrypt.

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

    -- CipherMode may be "ecb" or "cbc"
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'

    -- KeyLength may be 128, 192, 256
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256

    -- The padding scheme determines the contents of the bytes
    -- that are added to pad the result to a multiple of the
    -- encryption algorithm's block size.  AES has a block
    -- size of 16 bytes, so encrypted output is always
    -- a multiple of 16.
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0

    -- An initialization vector is required if using CBC mode.
    -- ECB mode does not use an IV.
    -- The length of the IV is equal to the algorithm's block size.
    -- It is NOT equal to the length of the key.
    DECLARE @ivHex nvarchar(4000)
    SELECT @ivHex = '000102030405060708090A0B0C0D0E0F'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex, 'hex'

    -- The secret key must equal the size of the key.  For
    -- 256-bit encryption, the binary secret key is 32 bytes.
    -- For 128-bit encryption, the binary secret key is 16 bytes.
    DECLARE @keyHex nvarchar(4000)
    SELECT @keyHex = '000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @keyHex, 'hex'

    -- Encrypt a file, producing the .aes as output.
    -- The input file is unchanged, the output .aes contains the encrypted
    -- contents of the input file.

    -- Note: The .aes output file has no file format.  It is simply a stream
    -- of bytes that resembles random binary data.
    DECLARE @inFile nvarchar(4000)
    SELECT @inFile = '/Users/chilkat/testData/pdf/sample.pdf'
    DECLARE @outFile nvarchar(4000)
    SELECT @outFile = '/Users/chilkat/testData/p7m/sample.pdf.aes'
    DECLARE @success int
    EXEC sp_OAMethod @crypt, 'CkEncryptFile', @success OUT, @inFile, @outFile
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- For demonstration purposes, a different instance of the object will be used
    -- for decryption.
    DECLARE @decrypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @decrypt OUT

    -- All settings must match to be able to decrypt:
    EXEC sp_OASetProperty @decrypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @decrypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @decrypt, 'KeyLength', 256
    EXEC sp_OASetProperty @decrypt, 'PaddingScheme', 0
    EXEC sp_OAMethod @decrypt, 'SetEncodedIV', NULL, @ivHex, 'hex'
    EXEC sp_OAMethod @decrypt, 'SetEncodedKey', NULL, @keyHex, 'hex'

    -- Decrypt the .aes
    SELECT @inFile = '/Users/chilkat/testData/p7m/sample.pdf.aes'
    SELECT @outFile = '/Users/chilkat/testData/pdf/recovered.pdf'
    EXEC sp_OAMethod @decrypt, 'CkDecryptFile', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @decrypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @decrypt
        RETURN
      END


    PRINT 'Success!'

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @decrypt


END
GO