SQL Server

Encrypt File using X.509 Certificate using AES in CBC Mode

See more Encryption Examples

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.) SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    --  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
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    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