(SQL Server) Create XML Signature with External XML Reference

Demonstrates how to create an XML digital signature where the referenced data is external. In this case, the data is an XML file located at the following URL: https://www.chilkatsoft.com/data/helloWorld.xml

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example inserts an XML signature in the following XML:

    -- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    -- <abc>
    --   <xyz>
    -- 	<name>helloWorld.xml</name>
    -- 	<url>https://www.chilkatsoft.com/data/helloWorld.xml</url>
    --   </xyz>
    -- </abc>

    -- Build the above XML to be signed.
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'abc'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'xyz|name', 'helloWorld.xml'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'xyz|url', 'https://www.chilkatsoft.com/data/helloWorld.xml'

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/test_secret.pfx', 'secret'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @gen int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.XmlDSigGen', @gen OUT

    -- Indicate the location within the XML the Signature will be inserted.
    EXEC sp_OASetProperty @gen, 'SigLocation', 'abc|xyz'

    -- Get the content of the XML data to be referenced.
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    DECLARE @sbExternalXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbExternalXml OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/data/helloWorld.xml', @sbExternalXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        RETURN
      END

    EXEC sp_OAMethod @gen, 'AddExternalXmlRef', @success OUT, 'https://www.chilkatsoft.com/data/helloWorld.xml', @sbExternalXml, 'sha256', 'EXCL_C14N', ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        RETURN
      END

    -- Provide the private key for signing via the certificate, and indicate that
    -- we want the base64 of the certificate embedded in the KeyInfo.
    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'Certificate'

    DECLARE @bUsePrivateKey int
    SELECT @bUsePrivateKey = 1
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, @bUsePrivateKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        RETURN
      END

    -- Indicate we want an indented signature for readability.
    -- This can be removed after debugging is finished..
    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'

    -- Now create and insert the Signature
    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @xml, '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 @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Examine the XML with the digital signature inserted
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Here is the output:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <abc>
    --     <xyz>
    --         <name>helloWorld.xml</name>
    --         <url>https://www.chilkatsoft.com/data/helloWorld.xml</url>
    --     <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#rsa-sha256"/>
    --     <ds:Reference URI="https://www.chilkatsoft.com/data/helloWorld.xml">
    --       <ds:Transforms>
    --         <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>H/s775OZhel6wdBhe02l4LK6m0lkplOisKxAbVwWxp0=</ds:DigestValue>
    --     </ds:Reference>
    --   </ds:SignedInfo>
    --   <ds:SignatureValue>UruwnBqh..Ll4IzUA==</ds:SignatureValue>
    --   <ds:KeyInfo>
    --     <ds:X509Data>
    --       <ds:X509Certificate>MIIHAz...nUZWCaDE=</ds:X509Certificate>
    --     </ds:X509Data>
    --   </ds:KeyInfo>
    -- </ds:Signature></xyz>
    -- </abc>

    -- Let's verify the signature...
    DECLARE @verifier int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.XmlDSig', @verifier OUT

    EXEC sp_OAMethod @verifier, 'LoadSignatureSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END

    -- Let's examine the external references, and fetch the data for each..
    DECLARE @sbExternalData int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbExternalData OUT

    DECLARE @numRefs int
    EXEC sp_OAGetProperty @verifier, 'NumReferences', @numRefs OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numRefs
      BEGIN
        DECLARE @bExternal int
        EXEC sp_OAMethod @verifier, 'IsReferenceExternal', @bExternal OUT, @i
        IF @bExternal = 1
          BEGIN
            DECLARE @uri nvarchar(4000)
            EXEC sp_OAMethod @verifier, 'ReferenceUri', @uri OUT, @i

            -- We're assuming the URI is an https:// or http:// URL...
            -- Let's also assume we know that the referenced data is text and we want the utf-8 byte representation.
            EXEC sp_OAMethod @sbExternalData, 'Clear', NULL
            EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @uri, @sbExternalData
            IF @success = 0
              BEGIN
                EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @xml
                EXEC @hr = sp_OADestroy @cert
                EXEC @hr = sp_OADestroy @gen
                EXEC @hr = sp_OADestroy @http
                EXEC @hr = sp_OADestroy @sbExternalXml
                EXEC @hr = sp_OADestroy @sbXml
                EXEC @hr = sp_OADestroy @verifier
                EXEC @hr = sp_OADestroy @sbExternalData
                RETURN
              END

            -- Because the referenced external data was explicitly XML, it was the canonicalized XML that was digested.
            -- We need to canonicalize the XML in the same way as when signed.
            DECLARE @canonXml nvarchar(4000)
            EXEC sp_OAMethod @sbExternalData, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @verifier, 'CanonicalizeXml', @canonXml OUT, @sTmp0, 'EXCL_C14N', 0
            EXEC sp_OAMethod @sbExternalData, 'SetString', @success OUT, @canonXml

            EXEC sp_OAMethod @verifier, 'SetRefDataSb', @success OUT, @i, @sbExternalData, 'utf-8'
            IF @success = 0
              BEGIN
                EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @xml
                EXEC @hr = sp_OADestroy @cert
                EXEC @hr = sp_OADestroy @gen
                EXEC @hr = sp_OADestroy @http
                EXEC @hr = sp_OADestroy @sbExternalXml
                EXEC @hr = sp_OADestroy @sbXml
                EXEC @hr = sp_OADestroy @verifier
                EXEC @hr = sp_OADestroy @sbExternalData
                RETURN
              END
          END
        SELECT @i = @i + 1
      END

    -- Now that we have the external data available, we can verify the reference digest(s) and the signature.
    DECLARE @bVerified int
    EXEC sp_OAMethod @verifier, 'VerifySignature', @bVerified OUT, 1
    IF @bVerified <> 1
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbExternalXml
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @verifier
        EXEC @hr = sp_OADestroy @sbExternalData
        RETURN
      END


    PRINT 'Signature verified!'

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbExternalXml
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @verifier
    EXEC @hr = sp_OADestroy @sbExternalData


END
GO