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.

Download Chilkat Crypt ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Any string argument automatically begins the 30-day trial.
    DECLARE @success int

    EXEC sp_OAMethod @crypt, 'UnlockComponent', @success OUT, '30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        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
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @encryptCert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    --  For demonstration purposes, a different instance of the object will be used
    --  for decryption.
    DECLARE @decrypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @decrypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    PRINT 'Success!'
END
GO