(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 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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT

    DECLARE @sbNow int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- 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', '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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- 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_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