Sign a File to Create a .p7s (Detached Signature)

SQL Server example to create a detached signature file (.p7s) for any type file. The signature can be verified by calling VerifyP7S and passing the original filename and the .p7s filename.

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 .p7s as output:
    DECLARE @inFile nvarchar(4000)

    SELECT @inFile = '/Users/chilkat/testData/pdf/sample.pdf'
    DECLARE @sigFile nvarchar(4000)

    SELECT @sigFile = '/Users/chilkat/testData/p7s/sample.p7s'

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

        PRINT @sTmp0

        RETURN
      END

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

        PRINT @sTmp0

        RETURN
      END

    PRINT 'Success!'
END
GO