SQL Server

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

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