SQL Server
SQL Server
Sign SOAP XML for New Zealand Customs Service
See more XAdES Examples
Demonstrates how to create an XAdES signed SOAP XML pertaining to the New Zealand Customs Service.Note: This example requires Chilkat v9.5.0.96 or later.
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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
SELECT @success = 1
-- Create the following XML to be signed:
-- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
-- xmlns:v1="http://customs.govt.nz/jbms/msggate/reqresp/v1">
-- <soapenv:Header>
-- <wsse:Security soapenv:mustUnderstand="1"
-- xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
-- xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
-- <wsu:Timestamp wsu:Id="TS-037E78514E9B9132CB16817563559151">
-- <wsu:Created>2023-04-17T18:32:35.913Z</wsu:Created>
-- <wsu:Expires>2023-04-17T19:32:35.913Z</wsu:Expires>
-- </wsu:Timestamp>
-- </wsse:Security>
-- </soapenv:Header>
-- <soapenv:Body wsu:Id="id-8"
-- xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
-- <v1:RequestResponse>
-- <v1:Submitter>TEST1234</v1:Submitter>
-- <v1:MailboxMsgId>999999</v1:MailboxMsgId>
-- </v1:RequestResponse>
-- </soapenv:Body>
-- </soapenv:Envelope>
-- Create a random ID like this: TS-037E78514E9B9132CB16817563559151
DECLARE @tsId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @tsId OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @tsId, 'Append', @success OUT, 'TS-'
EXEC sp_OAMethod @tsId, 'AppendRandom', @success OUT, 16, 'hex'
-- STR-037E78514E9B9132CB16817563559614
DECLARE @strId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @strId OUT
EXEC sp_OAMethod @strId, 'Append', @success OUT, 'STR-'
EXEC sp_OAMethod @strId, 'AppendRandom', @success OUT, 16, 'hex'
-- KI-037E78514E9B9132CB16817563559583
DECLARE @keyInfoId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @keyInfoId OUT
EXEC sp_OAMethod @keyInfoId, 'Append', @success OUT, 'KI-'
EXEC sp_OAMethod @keyInfoId, 'AppendRandom', @success OUT, 16, 'hex'
-- Create a date/time for the current time with this format: 2023-04-17T18:32:35.913Z
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
DECLARE @sbNow int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbNow OUT
EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @sbNow, 'Append', @success OUT, @sTmp0
-- If we really need the milliseconds, we can replace the "Z" with ".000Z"
-- The server will also likely accept a timestamp without milliseconds, such as 2023-04-17T18:32:35Z
DECLARE @n int
EXEC sp_OAMethod @sbNow, 'Replace', @n OUT, 'Z', '.000Z'
DECLARE @sbNowPlusOneHour int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbNowPlusOneHour OUT
EXEC sp_OAMethod @dt, 'AddSeconds', @success OUT, 3600
EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @sbNowPlusOneHour, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbNowPlusOneHour, 'Replace', @n OUT, 'Z', '.000Z'
DECLARE @xmlToSign int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT
EXEC sp_OASetProperty @xmlToSign, 'Tag', 'soapenv:Envelope'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:v1', 'http://customs.govt.nz/jbms/msggate/reqresp/v1'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'soapenv:mustUnderstand', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsse', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
EXEC sp_OAMethod @tsId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsu:Timestamp', 1, 'wsu:Id', @sTmp0
EXEC sp_OAMethod @sbNow, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Created', @sTmp0
EXEC sp_OAMethod @sbNowPlusOneHour, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Expires', @sTmp0
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body', 1, 'wsu:Id', 'id-8'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|v1:RequestResponse|v1:Submitter', 'TEST1234'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|v1:RequestResponse|v1:MailboxMsgId', '999999'
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
EXEC sp_OASetProperty @gen, 'SigLocation', 'soapenv:Envelope|soapenv:Header|wsse:Security'
EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
EXEC sp_OASetProperty @gen, 'SigId', 'SIG-037E78514E9B9132CB16817563559695'
EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'soapenv v1'
EXEC sp_OASetProperty @gen, 'IncNamespacePrefix', 'ec'
EXEC sp_OASetProperty @gen, 'IncNamespaceUri', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'EXCL_C14N'
EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'
-- Set the KeyInfoId before adding references..
EXEC sp_OAMethod @keyInfoId, 'GetAsString', @sTmp0 OUT
EXEC sp_OASetProperty @gen, 'KeyInfoId', @sTmp0
-- -------- Reference 1 --------
DECLARE @xml1 int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml1 OUT
EXEC sp_OASetProperty @xml1, 'Tag', 'ds:Transforms'
EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'ds:Transform', 1, 'Algorithm', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'ds:Transform|ec:InclusiveNamespaces', 1, 'PrefixList', 'wsse soapenv v1'
EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'ds:Transform|ec:InclusiveNamespaces', 1, 'xmlns:ec', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OAMethod @tsId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @gen, 'AddSameDocRef2', @success OUT, @sTmp0, 'sha256', @xml1, ''
-- -------- Reference 2 --------
DECLARE @xml2 int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml2 OUT
EXEC sp_OASetProperty @xml2, 'Tag', 'ds:Transforms'
EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'ds:Transform', 1, 'Algorithm', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'ds:Transform|ec:InclusiveNamespaces', 1, 'PrefixList', 'v1'
EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'ds:Transform|ec:InclusiveNamespaces', 1, 'xmlns:ec', 'http://www.w3.org/2001/10/xml-exc-c14n#'
EXEC sp_OAMethod @gen, 'AddSameDocRef2', @success OUT, 'id-8', 'sha256', @xml2, ''
-- Provide a certificate + private key. (PFX password is test123)
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 <> 1
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tsId
EXEC @hr = sp_OADestroy @strId
EXEC @hr = sp_OADestroy @keyInfoId
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbNow
EXEC @hr = sp_OADestroy @sbNowPlusOneHour
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xml1
EXEC @hr = sp_OADestroy @xml2
EXEC @hr = sp_OADestroy @cert
RETURN
END
EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1
EXEC sp_OASetProperty @gen, 'KeyInfoType', 'Custom'
-- Create the custom KeyInfo XML..
DECLARE @xmlCustomKeyInfo int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlCustomKeyInfo OUT
EXEC sp_OASetProperty @xmlCustomKeyInfo, 'Tag', 'wsse:SecurityTokenReference'
EXEC sp_OAMethod @strId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @xmlCustomKeyInfo, 'AddAttribute', @success OUT, 'wsu:Id', @sTmp0
EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:KeyIdentifier', 1, 'EncodingType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary'
EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:KeyIdentifier', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3'
-- Insert the single-line base64 of the signing certificate's DER
EXEC sp_OASetProperty @cert, 'UncommonOptions', 'Base64CertNoCRLF'
EXEC sp_OAMethod @cert, 'GetEncoded', @sTmp0 OUT
EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateChildContent', NULL, 'wsse:KeyIdentifier', @sTmp0
EXEC sp_OASetProperty @xmlCustomKeyInfo, 'EmitXmlDecl', 0
EXEC sp_OAMethod @xmlCustomKeyInfo, 'GetXml', @sTmp0 OUT
EXEC sp_OASetProperty @gen, 'CustomKeyInfoXml', @sTmp0
-- Load XML to be signed...
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml
EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'
-- Sign the XML...
EXEC sp_OASetProperty @gen, 'VerboseLogging', 1
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 @tsId
EXEC @hr = sp_OADestroy @strId
EXEC @hr = sp_OADestroy @keyInfoId
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbNow
EXEC @hr = sp_OADestroy @sbNowPlusOneHour
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xml1
EXEC @hr = sp_OADestroy @xml2
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- Save the signed XML to a file.
EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, 'c:/temp/qa_output/signedXml.xml', 'utf-8', 0
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tsId
EXEC @hr = sp_OADestroy @strId
EXEC @hr = sp_OADestroy @keyInfoId
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbNow
EXEC @hr = sp_OADestroy @sbNowPlusOneHour
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @xml1
EXEC @hr = sp_OADestroy @xml2
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
EXEC @hr = sp_OADestroy @sbXml
END
GO