Sign with .pfx to create .p7s, Verify with .cer

SQL Server example to sign a file using a .pfx and creating a .p7s file as output. It then uses a .cer file to verify the signature.

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
        PRINT 'Crypt component unlock failed'
        RETURN
      END

    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, 'chilkat.pfx', 'myPfxPassword'
    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, 'Chilkat Software, Inc.'
    IF @cert Is NULL 
      BEGIN
        PRINT 'Failed to find certificate'
        RETURN
      END

    --  We can explicitly choose hash/encryption algorithms using a CSP.
    DECLARE @csp int
    EXEC @hr = sp_OACreate 'Chilkat.Csp', @csp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @csp, 'SetProviderMicrosoftEnhanced', NULL
    EXEC sp_OAMethod @csp, 'SetHashAlgorithm', NULL, 'sha-1'

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

    --  We can sign any type of file, creating a .p7s as output:
    EXEC sp_OAMethod @crypt, 'CreateP7S', @success OUT, 'hamlet.xml', 'hamlet.p7s'
    IF @success = 1
      BEGIN
        PRINT 'Signature created.'
      END
    ELSE
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END

    --  Verify and restore the original file:
    --  Use a cert from a .cer file (which only contains the public key)
    DECLARE @cert2 int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert2 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @cert2, 'LoadFromFile', @success OUT, 'chilkat.cer'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END
    EXEC sp_OAMethod @crypt, 'SetVerifyCert', NULL, @cert2

    EXEC sp_OAMethod @crypt, 'VerifyP7S', @success OUT, 'hamlet.xml', 'hamlet.p7s'
    IF @success = 1
      BEGIN
        PRINT 'Signature verified.'
      END
    ELSE
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END


END
GO