(SQL Server) Encrypt File using X.509 Certificate using AES in CBC Mode

Demonstrates how to encrypt a file using an X.509 Certificate's public key, where the underlying bulk encryption algorithm is 128-bit AES in CBC mode. (The underlying bulk encryption algorithm can be specified to be other algorithms/strengths.)

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.

    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

    -- Use public-key cryptography for encryption.
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pki'

    -- Get an X.509 certificate to use for encrypting.
    -- Note: Chilkat provides many different ways of loading a certificate, from many different formats,
    -- or if on Windows, from the installed certificates on the system.
    -- This example simply loads a certificate from a file.
    -- Also, for encryption, only the public key is needed.  Digital certificates embed the public key
    -- within the X.509 format itself.  Therefore, if you have a certificate, you also have the public key.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/testCert.cer'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    EXEC sp_OAMethod @crypt, 'SetEncryptCert', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Indicate the underlying bulk encryption algorithm to be used:
    EXEC sp_OASetProperty @crypt, 'Pkcs7CryptAlg', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128

    -- Note: When doing public-key encryption the underlying bulk symmetric cipher mode will always be CBC. (at least with Chilkat...)

    -- There's one last option that could be set.  If is the RSA encryption encryption/padding scheme. 
    -- By default, RSAES_PKCS1-V1_5 is used.  If desired, the OaepPadding property could be set to 1 to
    -- use RSAES_OAEP.  (We'll leave it set at the default value of 0)
    EXEC sp_OASetProperty @crypt, 'OaepPadding', 0

    -- Everything is specified.  Now just encrypt...
    -- The output is PKCS7 in binary DER format.
    EXEC sp_OAMethod @crypt, 'CkEncryptFile', @success OUT, 'qa_data/hamlet.xml', 'qa_output/hamlet.p7'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'Finished.'

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cert


END
GO