(SQL Server) Encrypt File to PKCS7 .p7m

SQL Server example to public-key encrypt a file creating a PKCS#7 .p7m file as output. Also demonstrates how to decrypt the .p7m to recover the original file.

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

    -- Indicate the public-key encryption is to be used.
    -- Do this by setting the encryption algorithm equal 
    -- to "PKI" (an acroynm for public-key infrastructure).
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'PKI'

    -- Indicate the inner symmetric encryption algorithm to be used.
    -- possible values are "aes", "des", "3des", and "rc2".
    -- For this example, we'll use 256-bit AES encryption.
    EXEC sp_OASetProperty @crypt, 'Pkcs7CryptAlg', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256

    -- To encrypt, only a certificate w/ public key is needed.
    -- (The certificate w/ private key is required for decryption.)

    -- The LoadFromFile method can load virtually any certificate format:
    -- 1. DER encoded binary X.509 (.CER)
    -- 2. Base-64 encoded X.509 (.CER)
    -- 3. Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)
    -- 4. PEM format
    DECLARE @encryptCert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @encryptCert OUT

    DECLARE @success int
    EXEC sp_OAMethod @encryptCert, 'LoadFromFile', @success OUT, '/Users/chilkat/testData/cer/acme.cer'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @encryptCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @encryptCert
        RETURN
      END

    -- Tell the crypt object to use the certificate for encrypting:
    EXEC sp_OAMethod @crypt, 'AddEncryptCert', NULL, @encryptCert

    -- Encrypt a file, producing a .p7m as output.
    -- The input file is unchanged, the output .p7m contains the encrypted
    -- contents of the input file.
    DECLARE @inFile nvarchar(4000)
    SELECT @inFile = '/Users/chilkat/testData/pdf/sample.pdf'
    DECLARE @outFile nvarchar(4000)
    SELECT @outFile = '/Users/chilkat/testData/p7m/sample.pdf.p7m'
    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
        EXEC @hr = sp_OADestroy @encryptCert
        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

    -- To decrypt, the certificate w/ private key is required.  A PFX (also known
    -- as PKCS#12) is a common secure container for certs and private keys.
    DECLARE @pfxFilename nvarchar(4000)
    SELECT @pfxFilename = '/Users/chilkat/testData/pfx/acme.pfx'
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'secret'

    -- Tell the component to look in the PFX file for certs and private keys.
    EXEC sp_OAMethod @decrypt, 'AddPfxSourceFile', @success OUT, @pfxFilename, @pfxPassword
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @decrypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @encryptCert
        EXEC @hr = sp_OADestroy @decrypt
        RETURN
      END

    -- Tell the decrypt object that PKI (public key encryption) is to be used
    -- for decryptiong.
    EXEC sp_OASetProperty @decrypt, 'CryptAlgorithm', 'PKI'
    -- There is no need to set the Pkcs7Alg or KeyLength because this information
    -- is contained within the .p7m

    -- Decrypt the .p7m
    SELECT @inFile = '/Users/chilkat/testData/p7m/sample.pdf.p7m'
    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 @encryptCert
        EXEC @hr = sp_OADestroy @decrypt
        RETURN
      END


    PRINT 'Success!'

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


END
GO