SQL Server
SQL Server
Alliance Access LAU Sign Message (XML Signature using HMAC-SHA-256)
See more XML Digital Signatures Examples
Demonstrates how to sign XML according to the requirements for Alliance Access LAU (Local Authentication) using HMAC-SHA-256.Chilkat SQL Server Downloads
-- 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 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>
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
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
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
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