Sign a File to Create a .p7m File (using a PFX)

SQL Server example to sign a file creating a .p7m file as output. The .p7m contains the signed contents of the original file. It can be verified and restored by calling VerifyP7M.

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

    --  Use a digital certificate and private key from a PFX file (.pfx or .p12).
    DECLARE @signingCertSubject nvarchar(4000)

    SELECT @signingCertSubject = 'Acme Inc'
    DECLARE @pfxFilename nvarchar(4000)

    SELECT @pfxFilename = '/Users/chilkat/testData/pfx/acme.pfx'
    DECLARE @pfxPassword nvarchar(4000)

    SELECT @pfxPassword = 'test123'

    DECLARE @certStore int
    EXEC @hr = sp_OACreate 'Chilkat.CertStore', @certStore OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @certStore, 'LoadPfxFile', @success OUT, @pfxFilename, @pfxPassword
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @certStore, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    DECLARE @cert int

    EXEC sp_OAMethod @certStore, 'FindCertBySubjectCN', @cert OUT, @signingCertSubject
    IF @cert Is NULL 
      BEGIN

        PRINT 'Failed to find certificate by subject common name.'
        RETURN
      END

    --  Tell the crypt component to use this cert.
    EXEC sp_OAMethod @crypt, 'SetSigningCert', NULL, @cert

    --  We can sign any type of file, creating a .p7m as output:
    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, 'CreateP7M', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0

        RETURN
      END

    --  Verify and restore the original file:
    EXEC sp_OAMethod @crypt, 'SetVerifyCert', NULL, @cert

    SELECT @inFile = @outFile
    SELECT @outFile = '/Users/chilkat/testData/pdf/restored.pdf'

    EXEC sp_OAMethod @crypt, 'VerifyP7M', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0

        RETURN
      END

    PRINT 'Success!'
END
GO