(SQL Server) Create EBICS Signature (XMLDSIG)

See more EBICS Examples

Demonstrates how to create an EBICS signature. (EBICS is the Electronic Banking Internet Communication Standard)

Note: This example requires Chilkat v9.5.0.88 or above.

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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This is the sample XML to be signed:

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <ebicsRequest
    --   xmlns="urn:org:ebics:H005"
    --   xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
    --   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    --   xsi:schemaLocation="urn:org:ebics:H005 ebics_request_H005.xsd"
    --   Version="H005" Revision="1">
    --   <header authenticate="true">
    --     <static>
    --       <HostID>EBIXHOST</HostID>
    --       <Nonce>BDA2312973890654FAC9879A89794E65</Nonce>
    --       <Timestamp>2005-01-30T15:30:45.123Z</Timestamp>
    --       <PartnerID>CUSTM001</PartnerID>
    --       <UserID>USR100</UserID>
    --       <Product Language="en" InstituteID="Institute ID">Product Identifier</Product>
    --       <OrderDetails>
    --         <AdminOrderType>BTU</AdminOrderType>
    --         <BTUOrderParams>
    --           <Service>
    --             <ServiceName>SCT</ServiceName>
    --             <MsgName>pain.001</MsgName>
    --           </Service>
    --         </BTUOrderParams>
    --       </OrderDetails>
    --       <BankPubKeyDigests>
    --         <Authentication Version="X002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">1H/rQr2Axe9hYTV2n/tCp+3UIQQ=</Authentication>
    --         <Encryption Version="E002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">2lwiueWOIER823jSoiOkjl+woeI=</Encryption>
    --       </BankPubKeyDigests>
    --       <SecurityMedium>0000</SecurityMedium>
    --       <NumSegments>2</NumSegments>
    --     </static>
    --     <mutable>
    --       <TransactionPhase>Initialisation</TransactionPhase>
    --     </mutable>
    --   </header>
    --   <body>
    --     <PreValidation authenticate="true">
    --       <DataDigest SignatureVersion="A006"> MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTI=</DataDigest>
    --     </PreValidation>
    --     <DataTransfer>
    --       <DataEncryptionInfo authenticate="true">
    --         <EncryptionPubKeyDigest Version="E002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">..here hash value of the public bank key for encryption..</EncryptionPubKeyDigest>
    --         <TransactionKey>EIGI4En6KEB6ArEzw+iq4N1wm6EptcyxXxStA...</TransactionKey>
    --         <HostID>EBIXHOST</HostID>
    --       </DataEncryptionInfo>
    --       <SignatureData authenticate="true">n6KEB6ArEzw+iq4N1wm6EptcyxXxStAO...</SignatureData>
    --       <DataDigest SignatureVersion="A006"> MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTI=</DataDigest>
    --     </DataTransfer>
    --   </body>
    -- </ebicsRequest>

    -- Load the above XML from a file.
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, 'qa_data/xml_dsig/ebics/fileToSign.xml', 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load XML input file.'
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

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

    -- We're going to insert the signature between the </header> and the <body>
    EXEC sp_OASetProperty @gen, 'SigLocation', 'ebicsRequest|header'

    -- Set the SigLocationMod = 1 to insert *after* the SigLocation
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 1

    -- We wish to use "ds" for the namespace..
    EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
    EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'

    -- Specify canonicalization and hash algorithms
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- Add the reference.
    -- For EBICS signatures, we pass the special keyword "EBICS" in the 1st argument.
    -- This tells Chilkat to create the reference using URI="#xpointer(//*[@authenticate='true'])"
    -- The "EBICS" keyword was introduced in Chilkat v9.5.0.88.
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'EBICS', 'sha256', 'C14N', '', ''

    -- Provide our certificate + private key. (PFX password is test123)
    -- (You'll use your own certificate, which can be loaded from many different sources by Chilkat, including smart cards.)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/cert_test123.pfx', 'test123'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- We don't want a KeyInfo to be included.
    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'None'

    -- Request an indented signature for readability.
    -- This can be removed after debugging (for a more compact signature).
    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'

    -- Sign the XML.
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- This is the XML with the EBICS signature added:

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <ebicsRequest
    -- xmlns="urn:org:ebics:H005"
    -- xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
    -- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    -- xsi:schemaLocation="urn:org:ebics:H005 ebics_request_H005.xsd"
    -- Version="H005" Revision="1">
    --   <header authenticate="true">
    --     <static>
    --       <HostID>EBIXHOST</HostID>
    --       <Nonce>BDA2312973890654FAC9879A89794E65</Nonce>
    --       <Timestamp>2005-01-30T15:30:45.123Z</Timestamp>
    --       <PartnerID>CUSTM001</PartnerID>
    --       <UserID>USR100</UserID>
    --       <Product Language="en" InstituteID="Institute ID">Product Identifier</Product>
    --       <OrderDetails>
    --         <AdminOrderType>BTU</AdminOrderType>
    --         <BTUOrderParams>
    --           <Service>
    --             <ServiceName>SCT</ServiceName>
    --             <MsgName>pain.001</MsgName>
    --           </Service>
    --         </BTUOrderParams>
    --       </OrderDetails>
    --       <BankPubKeyDigests>
    --         <Authentication Version="X002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">1H/rQr2Axe9hYTV2n/tCp+3UIQQ=</Authentication>
    --         <Encryption Version="E002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">2lwiueWOIER823jSoiOkjl+woeI=</Encryption>
    --       </BankPubKeyDigests>
    --       <SecurityMedium>0000</SecurityMedium>
    --       <NumSegments>2</NumSegments>
    --     </static>
    --     <mutable>
    --       <TransactionPhase>Initialisation</TransactionPhase>
    --     </mutable>
    --   </header><AuthSignature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    --   <ds:SignedInfo>
    --     <ds:CanonicalizationMethod Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/>
    --     <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/>
    --     <ds:Reference URI="#xpointer(//*[@authenticate='true'])">
    --       <ds:Transforms>
    --         <ds:Transform Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/>
    --       </ds:Transforms>
    --       <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --       <ds:DigestValue>jjLD90BedcIVxFENHse6pOnRubVUlHpKjXUF5BUd00k=</ds:DigestValue>
    --     </ds:Reference>
    --   </ds:SignedInfo>
    --   <ds:SignatureValue>TlVgCXGf+3kKZ4LLwqxKoMaDZSBdiDRcGpdKB+tFZ7MZse9jDqtCai7PxcvRLC7yRGRj3XWrAB6IVqXh6tXGqiAtRfa7XjezvJTmUdMEJ3hTEgKqm7cKjjZX5C+lN5XTJghOy0X1bZBl/NBJu/aqY9s8PKsD5Cpm8bFkl2ReBBTCTSF5CRK3XZr+fvWuUX2sFrFS5UDXG8/cmhaKHT15LBOJgYuLYr80dtL251Jy20rIJ5KK8xUz9gpexE61Y/ml6mUPLm8YgdACRdNvCOPRLjCqYwFbnfgaVO6MtSRG819rWyNtBhqVxdzbntiV1UobKbwFiJ1LMMHF0NCo2LGLCw==</ds:SignatureValue>
    -- </AuthSignature>
    --   <body>
    --     <PreValidation authenticate="true">
    --       <DataDigest SignatureVersion="A006"> MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTI=</DataDigest>
    --     </PreValidation>
    --     <DataTransfer>
    --       <DataEncryptionInfo authenticate="true">
    --         <EncryptionPubKeyDigest Version="E002" Algorithm="http://www.w3.org/2001/04/xmlenc#sha256">..here hash value of the public bank key for encryption..</EncryptionPubKeyDigest>
    --         <TransactionKey>EIGI4En6KEB6ArEzw+iq4N1wm6EptcyxXxStA...</TransactionKey>
    --         <HostID>EBIXHOST</HostID>
    --       </DataEncryptionInfo>
    --       <SignatureData authenticate="true">n6KEB6ArEzw+iq4N1wm6EptcyxXxStAO...</SignatureData>
    --       <DataDigest SignatureVersion="A006"> MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTI=</DataDigest>
    --     </DataTransfer>
    --   </body>
    -- </ebicsRequest>


    PRINT 'Here''s the EBICS signed XML:'
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- Verify the signature we just produced...
    DECLARE @verifier int
    -- Use "Chilkat_9_5_0.XmlDSig" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @verifier OUT

    EXEC sp_OAMethod @verifier, 'LoadSignatureSb', @success OUT, @sbXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END

    -- The signature has no KeyInfo, so we must externally provide the key.
    DECLARE @pubKey int
    EXEC sp_OAMethod @cert, 'ExportPublicKey', @pubKey OUT
    EXEC sp_OAMethod @verifier, 'SetPublicKey', @success OUT, @pubKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pubKey

        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END
    EXEC @hr = sp_OADestroy @pubKey

    EXEC sp_OAMethod @verifier, 'VerifySignature', @success OUT, 1
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END


    PRINT 'EBICS signature verified.'

    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @verifier


END
GO