(SQL Server) Alliance Access LAU Sign Message (XML Signature using HMAC-SHA-256)

Demonstrates how to sign XML according to the requirements for Alliance Access LAU (Local Authentication) using HMAC-SHA-256.

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.

    -- We begin with this message:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <Saa:DataPDU xmlns:Saa="urn:swift:saa:xsd:saa.2.0" xmlns:Sw="urn:swift:snl:ns.Sw"
    --   xmlns:SwGbl="urn:swift:snl:ns.SwGbl" xmlns:SwInt="urn:swift:snl:ns:SwInt" xmlns:SwSec="url:swift:snl:ns.SwSec">
    --     <Saa:Revision>2.0.7</Saa:Revision>
    --     <Saa:Header>
    --         <Saa:Message>
    -- 			<test>blah blah</test>
    --         </Saa:Message>
    --     </Saa:Header>
    --     <Saa:Body>...</Saa:Body>
    --     <Saa:LAU>
    --     </Saa:LAU>
    -- </Saa:DataPDU>

    -- And we want so sign to create this as the result:
    -- The signed XML we'll create will not be indented and pretty-printed like this.
    -- Instead, we'll use the "CompactSignedXml" behavior to produce compact single-line XML.

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <Saa:DataPDU xmlns:Saa="urn:swift:saa:xsd:saa.2.0" xmlns:Sw="urn:swift:snl:ns.Sw"
    --   xmlns:SwGbl="urn:swift:snl:ns.SwGbl" xmlns:SwInt="urn:swift:snl:ns:SwInt" xmlns:SwSec="url:swift:snl:ns.SwSec">
    --     <Saa:Revision>2.0.7</Saa:Revision>
    --     <Saa:Header>
    --         <Saa:Message>
    -- 			<test>blah blah</test>
    --         </Saa:Message>
    --     </Saa:Header>
    --     <Saa:Body>...</Saa:Body>
    --     <Saa:LAU>
    --         <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    --             <ds:SignedInfo>
    --                 <ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
    --                 <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#hmac-sha256"/>
    --                 <ds:Reference URI="">
    --                     <ds:Transforms>
    --                         <ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
    --                         <ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
    --                     </ds:Transforms>
    --                     <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --                     <ds:DigestValue>Y7oScHnYOUQvni/TSzZbDec+HR+mWIFH149GXpwj1Ws=</ds:DigestValue>
    --                 </ds:Reference>
    --             </ds:SignedInfo>
    --             <ds:SignatureValue>6ynF/FcwbPsHrtlj3h2agJigdnvpbO6hOzKSRGzqkw0=</ds:SignatureValue>
    --         </ds:Signature>
    --     </Saa:LAU>
    -- </Saa:DataPDU>

    DECLARE @success int
    SELECT @success = 1

    -- Create the XML to be signed...

    -- (The XML does not need to be created this way.  It can be loaded from a file or a string.)
    -- Also, use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    DECLARE @xmlToSign int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xmlToSign, 'Tag', 'Saa:DataPDU'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:Saa', 'urn:swift:saa:xsd:saa.2.0'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:Sw', 'urn:swift:snl:ns.Sw'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:SwGbl', 'urn:swift:snl:ns.SwGbl'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:SwInt', 'urn:swift:snl:ns:SwInt'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:SwSec', 'url:swift:snl:ns.SwSec'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Saa:Revision', '2.0.7'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Saa:Header|Saa:Message|test', 'blah blah'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Saa:Body', '...'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Saa:LAU', ''

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

    EXEC sp_OASetProperty @gen, 'SigLocation', 'Saa:DataPDU|Saa:LAU'
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
    EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
    EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'EXCL_C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- You may alternatively choose "IndentedSignature" instead of "CompactSignedXml"
    EXEC sp_OASetProperty @gen, 'Behaviors', 'CompactSignedXml'

    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, '', 'sha256', 'EXCL_C14N', '', ''

    -- Specify the HMAC key.
    -- For example, if the HMAC key is to be the us-ascii bytes of the string "secret",
    -- the HMAC key can be set in any of the following ways (and also more ways not shown here..)
    EXEC sp_OAMethod @gen, 'SetHmacKey', @success OUT, 'secret', 'ascii'
    -- or
    EXEC sp_OAMethod @gen, 'SetHmacKey', @success OUT, 'c2VjcmV0', 'base64'
    -- or
    EXEC sp_OAMethod @gen, 'SetHmacKey', @success OUT, '736563726574', 'hex'

    -- Sign the XML..
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Save the signed XML to a file.
    EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, 'qa_output/signedXml.xml', 'utf-8', 0

    -- Show the signed XML.
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xmlToSign
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @sbXml


END
GO