(SQL Server) Create P7M for ISO20022 Message (Customer Credit Transfer)

Demonstrates how to create a .p7m (signed data) for an ISO20022 XML message using an HSM such as that provided by Swift 3SKey or by banks. Also shows how to validate and extract the XML message.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- What is a .p7m file?

    -- Load the signing certificate from the connected HSM.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    -- Tell the crypt class to use the cert on the ePass2003 token.
    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- The CadesEnabled property applies to all methods that create CMS/PKCS7 signatures. 
    -- To create a CAdES-BES signature, set this property equal to true. 
    EXEC sp_OASetProperty @crypt, 'CadesEnabled', 1

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    -- The XML file to be signed and encapsulated in the signature looks like this:

    -- <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02">
    --     <CstmrCdtTrfInitn>
    --         <GrpHdr>
    --             <MsgId>1234567890</MsgId>
    --             <CreDtTm>2024-10-02T12:00:00</CreDtTm>
    --             <NbOfTxs>1</NbOfTxs>
    --             <CtrlSum>1000.00</CtrlSum>
    --             <InitgPty>
    --                 <Nm>Example Company</Nm>
    --             </InitgPty>
    --         </GrpHdr>
    --         <PmtInf>
    --             <!-- Payment information goes here -->
    --         </PmtInf>
    --     </CstmrCdtTrfInitn>
    -- </Document>

    -- What is "pain.001.001.02"?
    -- 
    --     "pain.001": This is an ISO 20022 message type for Customer Credit Transfer
    --     Initiation. It is used to instruct a bank or financial institution to transfer
    --     funds from a customer's account to a beneficiary's account.
    --     "pain.001.001.02": This specifies version "02" of the "pain.001" message.
    --     The versioning indicates that there might be other versions like
    --     "pain.001.001.01", and this version "02" includes revisions or updates compared
    --     to version "01".
    -- 
    -- Usage:
    -- 
    --     This namespace is typically seen in XML files that follow the ISO 20022
    --     payment initiation standards. Financial institutions, payment service providers,
    --     and other entities use it to exchange structured payment data in a standardized
    --     XML format.
    --     A typical use case for "pain.001.001.02" is to send payment instructions for
    --     credit transfers, such as payments from businesses to suppliers or salary
    --     payments from employers to employees.

    -- We can sign any type of file, creating a .p7m as output.
    -- The .p7m contains the signature and also embeds the data of the file that is signed.
    DECLARE @inFile nvarchar(4000)
    SELECT @inFile = 'qa_data/xml/cust_credit_transfer.xml'
    DECLARE @p7mFile nvarchar(4000)
    SELECT @p7mFile = 'c:/temp/qa_output/cust_credit_transfer.p7m'

    -- -----------------------------------------------------------------------------------------
    -- Also see Chilkat's online tool to examine a .p7m and generate code to duplicate the .p7m
    -- -----------------------------------------------------------------------------------------

    -- Create the CAdES-BES attached signature, which contains the original data.
    EXEC sp_OAMethod @crypt, 'CreateP7M', @success OUT, @inFile, @p7mFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END


    PRINT 'Created the .p7m'

    -- --------------------------------------
    -- Now do the reverse and validate/extract
    -- --------------------------------------

    DECLARE @outFile nvarchar(4000)
    SELECT @outFile = 'c:/temp/qa_output/out.xml'
    SELECT @inFile = 'qa_data/p7m/cust_credit_transfer.p7m'

    -- Verify and extract the encapsulated file:
    EXEC sp_OAMethod @crypt, 'VerifyP7M', @success OUT, @inFile, @outFile
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END


    PRINT 'Success!'

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @crypt


END
GO