SQL Server Requires Chilkat v11.0.0+
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)Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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'])"
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
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
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 @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT
EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubKey
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 @sbXml
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @pubKey
RETURN
END
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
EXEC @hr = sp_OADestroy @pubKey
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
EXEC @hr = sp_OADestroy @pubKey
END
GO