SQL Server Requires Chilkat v11.0.0+

Create EBICS SignaturePubKeyOrderData XML

See more EBICS Examples

Demonstrates how to create the EBICS SignaturePubKeyOrderData XML. (EBICS is the Electronic Banking Internet Communication Standard) SQL Server

--
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.

    --  The goal of this example is to create the XML shown below from the certificate to be used for signing.

    --  <?xml version="1.0" encoding="UTF-8"?>
    --  <SignaturePubKeyOrderData xmlns="http://www.ebics.org/S001" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ebics.org/S002">
    --    <SignaturePubKeyInfo>
    --      <ds:X509Data>
    --        <X509IssuerSerial>
    --          <ds:X509IssuerName>C=FR, O=Example, OU=1234, CN=Example eID User, OrganizationID=SI:FR-1234</ds:X509IssuerName>
    --          <ds:X509SerialNumber>73FFFFB881F1629982F787DF161EFFFF</ds:X509SerialNumber>
    --        </X509IssuerSerial>
    --        <ds:X509Certificate>
    --          MIIJT...kE=
    --        </ds:X509Certificate>
    --      </ds:X509Data>
    --      <PubKeyValue>
    --        <ds:RSAPublicKey>
    --         <ds:Modulus>wedQ...22Kw==</ds:Modulus>
    --          <ds:Exponent>AQAB</ds:Exponent>
    --        </ds:RSAPublicKey>
    --      </PubKeyValue>
    --      <SignatureVersion>A005</SignatureVersion>
    --    </SignaturePubKeyInfo>
    --    <PartnerID/>
    --    <UserID/>
    --  </SignaturePubKeyOrderData>

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    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 @cert
        RETURN
      END

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'SignaturePubKeyOrderData'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://www.ebics.org/S001'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:ds', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xsi:schemaLocation', 'http://www.ebics.org/S002'
    EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|ds:X509Data|X509IssuerSerial|ds:X509IssuerName', @sTmp0
    EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|ds:X509Data|X509IssuerSerial|ds:X509SerialNumber', @sTmp0
    EXEC sp_OAMethod @cert, 'GetEncoded', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|ds:X509Data|ds:X509Certificate', @sTmp0

    DECLARE @pubkey int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubkey OUT

    EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubkey

    DECLARE @xmlPubKey int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlPubKey OUT

    EXEC sp_OAMethod @pubkey, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @xmlPubKey, 'LoadXml', @success OUT, @sTmp0

    --  The public key XML will look like this:
    --  
    --  <RSAPublicKey>
    --    <Modulus>...</Modulus>
    --    <Exponent>...</Exponent>
    --  </RSAPublicKey>

    EXEC sp_OAMethod @xmlPubKey, 'GetChildContent', @sTmp0 OUT, 'Modulus'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|PubKeyValue|ds:RSAPublicKey|ds:Modulus', @sTmp0
    EXEC sp_OAMethod @xmlPubKey, 'GetChildContent', @sTmp0 OUT, 'Exponent'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|PubKeyValue|ds:RSAPublicKey|ds:Exponent', @sTmp0
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SignaturePubKeyInfo|SignatureVersion', 'A005'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'PartnerID', ''
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'UserID', ''

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @pubkey
    EXEC @hr = sp_OADestroy @xmlPubKey


END
GO