(SQL Server) Create .p7s Signature with HSM / Smartcard

SQL Server example showing how to create a .p7s Signature with HSM / Smartcard

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

    --  Find our digital certificate from the Current User certificate store.
    --  Note: There are several other ways to load your certificate
    --  into a Chilkat cert object.  You may load directly from a .cer file,
    --  PEM file, pfx, etc.
    DECLARE @ccs int
    EXEC @hr = sp_OACreate 'Chilkat.CreateCS', @ccs OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @certStore int

    EXEC sp_OAMethod @ccs, 'OpenCurrentUserStore', @certStore OUT
    DECLARE @cert int

    EXEC sp_OAMethod @certStore, 'FindCertBySubjectCN', @cert OUT, 'Chilkat Software, Inc.'
    IF @cert Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        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 detached signature as output:
    EXEC sp_OAMethod @crypt, 'CreateP7S', @success OUT, 'test.xml', 'test.p7s'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        RETURN
      END
    EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0
    --  Verify the signature...
    EXEC sp_OAMethod @crypt, 'SetVerifyCert', NULL, @cert

    EXEC sp_OAMethod @crypt, 'VerifyP7S', @success OUT, 'test.xml', 'test.p7s'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        RETURN
      END

    PRINT 'Success!'
END
GO