(SQL Server) Create Enveloping XML Digital Signature

This example creates an enveloping digital signature.
An enveloping signature is where the signed data is contained within the Signature within an Object element.
This example signs non-XML text data where the XML signature constitutes the entire output.

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.

    -- Let's use the ECDSA private key at https://www.chilkatsoft.com/exampleData/secp256r1-key.zip
    -- for signing.
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @keyUrl nvarchar(4000)
    SELECT @keyUrl = 'https://www.chilkatsoft.com/exampleData/secp256r1-key.zip'

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, @keyUrl, @zipFile
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zipFile
        RETURN
      END
    DECLARE @zip int
    -- Use "Chilkat_9_5_0.Zip" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT

    EXEC sp_OAMethod @zip, 'OpenBd', @success OUT, @zipFile
    DECLARE @zipEntry int
    EXEC sp_OAMethod @zip, 'FirstMatchingEntry', @zipEntry OUT, '*.pem'
    DECLARE @ecKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @ecKey OUT

    EXEC sp_OAMethod @zipEntry, 'UnzipToString', @sTmp0 OUT, 0, 'utf-8'
    EXEC sp_OAMethod @ecKey, 'LoadPem', @success OUT, @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ecKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zipEntry

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zipFile
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @ecKey
        RETURN
      END

    EXEC @hr = sp_OADestroy @zipEntry

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

    -- Provide the ECDSA key to the XML Digital Signature generator
    EXEC sp_OAMethod @gen, 'SetPrivateKey', @success OUT, @ecKey

    -- Add an enveloped reference to the content to be signed.
    DECLARE @sbContent int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContent OUT

    EXEC sp_OAMethod @sbContent, 'Append', @success OUT, 'This is the content that is signed.'
    EXEC sp_OAMethod @gen, 'AddEnvelopedRef', @success OUT, 'abc123', @sbContent, 'sha256', 'C14N', ''

    -- Generate the XML digital signature.
    -- Notice that in other examples, the sbXml passed to CreateXmlDSigSb
    -- already contains XML, and the XML signature is inserted at the location
    -- specified by the SigLocation property.  In this case, both SigLocation
    -- and sbXml are empty.  The result is that sbXml will contain just the Signature.
    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 @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zipFile
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @ecKey
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @sbContent
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Examine the enveloped signature, where the data is contained within the XML Signature
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- The Signature returned is compact and in a single line, like this:
    -- <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#ecdsa-sha256"/><ds:Reference URI="#abc123"><ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/><ds:DigestValue>tEVrbXXjeTXjF3tIojul4/sgeEGN49E1dxr/GMs8GNE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>/pILUshwrzgdGc4bPgp85TDfbUiM9pn8EIPNRVWKuoVEtPsv4XRthUrv9aDDvajmyl2okLwTakANgtaxO1ULMw==</ds:SignatureValue><ds:KeyInfo><ds:KeyValue><ds:ECKeyValue xmlns="http://www.w3.org/2009/xmldsig11#"><ds:NamedCurve URI="urn:oid:1.2.840.10045.3.1.7" /><ds:PublicKey>BOVKaiLPKEDChhkA64UEBOXTv/VFHnhrUPN+bXqCvEl7rroAYpH5tKzbiGTtMSlp4JO9Pxg44zeX7EoWDvOrpD0=</ds:PublicKey></ds:ECKeyValue></ds:KeyValue></ds:KeyInfo><ds:Object Id="abc123">This is the content that is signed.</ds:Object></ds:Signature>

    -- XML pretty-printed, the signature is as follows, but pretty-printing introductes whitespace that breaks the signature..

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <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#ecdsa-sha256" />
    --         <ds:Reference URI="#abc123">
    --             <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256" />
    --             <ds:DigestValue>tEVrbXXjeTXjF3tIojul4/sgeEGN49E1dxr/GMs8GNE=</ds:DigestValue>
    --         </ds:Reference>
    --     </ds:SignedInfo>
    --     <ds:SignatureValue>/pILUshwrzgdGc4bPgp85TDfbUiM9pn8EIPNRVWKuoVEtPsv4XRthUrv9aDDvajmyl2okLwTakANgtaxO1ULMw==</ds:SignatureValue>
    --     <ds:KeyInfo>
    --         <ds:KeyValue>
    --             <ds:ECKeyValue xmlns="http://www.w3.org/2009/xmldsig11#">
    --                 <ds:NamedCurve URI="urn:oid:1.2.840.10045.3.1.7" />
    --                 <ds:PublicKey>BOVKaiLPKEDChhkA64UEBOXTv/VFHnhrUPN+bXqCvEl7rroAYpH5tKzbiGTtMSlp4JO9Pxg44zeX7EoWDvOrpD0=</ds:PublicKey>
    --             </ds:ECKeyValue>
    --         </ds:KeyValue>
    --     </ds:KeyInfo>
    --     <ds:Object Id="abc123">This is the content that is signed.</ds:Object>
    -- </ds:Signature>
    -- 

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @zipFile
    EXEC @hr = sp_OADestroy @zip
    EXEC @hr = sp_OADestroy @ecKey
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @sbContent
    EXEC @hr = sp_OADestroy @sbXml


END
GO