(SQL Server) Create XAdES-T Signed XML

See more XAdES Examples

This example signs XML using the XAdES-T profile. XAdES-T is a profile within the XAdES standard that adds support for secure timestamping of signatures.

Secure timestamping involves adding a timestamp to the signature, indicating the exact time when the signature was applied.

Timestamping enhances the long-term validity of signatures by providing evidence that the signature existed at a specific point in time, even if the signer's certificate has expired or been revoked.

XAdES-T signatures include elements for embedding timestamp data within the XML signature, along with information about the timestamp authority and the timestamp verification process.

XAdES-T signatures are suitable for scenarios where long-term validity and integrity of signatures are essential, such as in legal and regulatory contexts where archived documents may need to be validated years or decades 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int
    SELECT @success = 1
    -- Create the XML to be signed...

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <es:Dossier xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns="http://uri.etsi.org/01903/v1.3.2#" xmlns:es="https://www.microsec.hu/ds/e-szigno30#" xsi:schemaLocation="https://www.microsec.hu/ds/e-szigno30# https://www.microsec.hu/ds/e-szigno30.xsd">
    -- 	<es:DossierProfile Id="PObject0" OBJREF="Object0">
    -- 	<es:Title>e-akta.es3</es:Title>
    -- 	<es:E-category>electronic dossier</es:E-category>
    -- 	<es:CreationDate>2022-12-02T07:55:16Z</es:CreationDate>
    -- 	</es:DossierProfile>
    -- 	<es:Documents Id="Object0"/>
    -- </es:Dossier>

    DECLARE @xmlToSign int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xmlToSign OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xmlToSign, 'Tag', 'es:Dossier'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:ds', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns', 'http://uri.etsi.org/01903/v1.3.2#'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:es', 'https://www.microsec.hu/ds/e-szigno30#'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xsi:schemaLocation', 'https://www.microsec.hu/ds/e-szigno30# https://www.microsec.hu/ds/e-szigno30.xsd'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'es:DossierProfile', 1, 'Id', 'PObject0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'es:DossierProfile', 1, 'OBJREF', 'Object0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'es:DossierProfile|es:Title', 'e-akta.es3'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'es:DossierProfile|es:E-category', 'electronic dossier'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'es:DossierProfile|es:CreationDate', '2022-12-02T07:55:16Z'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'es:Documents', 1, 'Id', 'Object0'

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

    EXEC sp_OASetProperty @gen, 'SigLocation', 'es:Dossier'
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
    EXEC sp_OASetProperty @gen, 'SigId', 'S9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OASetProperty @gen, 'SigValueId', 'VS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OASetProperty @gen, 'SignedInfoId', 'SIS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'EXCL_C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- Set the KeyInfoId before adding references..
    EXEC sp_OASetProperty @gen, 'KeyInfoId', 'KS9fe8096e-2cac-415d-9222-f6cf2ecb314b'

    -- Create an Object to be added to the Signature.
    DECLARE @object1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @object1 OUT

    EXEC sp_OASetProperty @object1, 'Tag', 'es:SignatureProfile'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'Id', 'PS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'OBJREF', 'Object0'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'SIGREF', 'S9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'SIGREFLIST', '#Object0 #PS9fe8096e-2cac-415d-9222-f6cf2ecb314b #PObject0 #XS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'es:SignerName', 'EC Minsített-Tesztel Péterke'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'es:SDPresented', 'false'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'es:Type', 'signature'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'es:Generator|es:Program', 1, 'name', 'e-Szigno'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'es:Generator|es:Program', 1, 'version', '3.3.6.8'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'es:Generator|es:Device', 1, 'name', 'OpenSSL 1.1.1n  15 Mar 2022'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'es:Generator|es:Device', 1, 'type', ''

    EXEC sp_OAMethod @object1, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @gen, 'AddObject', @success OUT, 'O1S9fe8096e-2cac-415d-9222-f6cf2ecb314b', @sTmp0, '', ''

    -- Create an Object to be added to the Signature.
    DECLARE @object2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @object2 OUT

    EXEC sp_OASetProperty @object2, 'Tag', 'QualifyingProperties'
    EXEC sp_OAMethod @object2, 'AddAttribute', @success OUT, 'Target', '#S9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object2, 'AddAttribute', @success OUT, 'Id', 'QPS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'SignedProperties', 1, 'Id', 'XS9fe8096e-2cac-415d-9222-f6cf2ecb314b'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'SignedProperties|SignedSignatureProperties|SigningTime', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'SignedProperties|SignedSignatureProperties|SigningCertificateV2|Cert|CertDigest|ds:DigestMethod', 1, 'Algorithm', 'http://www.w3.org/2001/04/xmlenc#sha256'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'SignedProperties|SignedSignatureProperties|SigningCertificateV2|Cert|CertDigest|ds:DigestValue', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'SignedProperties|SignedSignatureProperties|SigningCertificateV2|Cert|IssuerSerialV2', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'SignedProperties|SignedSignatureProperties|SignaturePolicyIdentifier|SignaturePolicyImplied', ''
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'SignedProperties|SignedSignatureProperties|SignerRoleV2|ClaimedRoles|ClaimedRole', 'tesztel'

    -- Here we have the EncapsulatedTimestamp found in the unsigned signature properties.
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|SignatureTimeStamp', 1, 'Id', 'T72cb4961-4326-4319-857a-7cf55e7ef899'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|SignatureTimeStamp|ds:CanonicalizationMethod', 1, 'Algorithm', 'http://www.w3.org/2001/10/xml-exc-c14n#'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|SignatureTimeStamp|EncapsulatedTimeStamp', 1, 'Id', 'ET72cb4961-4326-4319-857a-7cf55e7ef899'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'UnsignedProperties|UnsignedSignatureProperties|SignatureTimeStamp|EncapsulatedTimeStamp', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|TimeStampValidationData', 1, 'xmlns', 'http://uri.etsi.org/01903/v1.4.1#'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|CertificateValues', 1, 'Id', 'CV18c7702d-d45b-44bc-853a-a720f41053cd'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|CertificateValues|EncapsulatedX509Certificate', 1, 'Id', 'EC42db04c8-1422-407b-8c42-189353a55268'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'UnsignedProperties|UnsignedSignatureProperties|CertificateValues|EncapsulatedX509Certificate', 'BASE64_CONTENT'
    EXEC sp_OAMethod @object2, 'UpdateAttrAt', @success OUT, 'UnsignedProperties|UnsignedSignatureProperties|CertificateValues|EncapsulatedX509Certificate[1]', 1, 'Id', 'EC04728b44-a32c-46c1-b9bb-85b1f6b3c7d3'
    EXEC sp_OAMethod @object2, 'UpdateChildContent', NULL, 'UnsignedProperties|UnsignedSignatureProperties|CertificateValues|EncapsulatedX509Certificate[1]', 'BASE64_CONTENT'

    EXEC sp_OAMethod @object2, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @gen, 'AddObject', @success OUT, 'O2S9fe8096e-2cac-415d-9222-f6cf2ecb314b', @sTmp0, '', ''

    -- -------- Reference 1 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'Object0', 'sha256', 'EXCL_C14N', '', ''
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'Object0', 'Re1f816c4-7898-4544-9b41-f4156dc0c528'

    -- -------- Reference 2 --------
    EXEC sp_OAMethod @gen, 'AddObjectRef', @success OUT, 'PS9fe8096e-2cac-415d-9222-f6cf2ecb314b', 'sha256', 'EXCL_C14N', '', ''
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'PS9fe8096e-2cac-415d-9222-f6cf2ecb314b', 'Ra873b616-e568-4c38-ae94-27fbff67cc43'

    -- -------- Reference 3 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'PObject0', 'sha256', 'EXCL_C14N', '', ''
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'PObject0', 'Ra5d85948-5d6a-4914-8c32-242f5d6d9e81'

    -- -------- Reference 4 --------
    EXEC sp_OAMethod @gen, 'AddObjectRef', @success OUT, 'XS9fe8096e-2cac-415d-9222-f6cf2ecb314b', 'sha256', 'EXCL_C14N', '', 'http://uri.etsi.org/01903#SignedProperties'
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'XS9fe8096e-2cac-415d-9222-f6cf2ecb314b', 'Ra7412a43-dc05-4e0a-ac84-e9a070214757'

    -- Provide a certificate + private key. (PFX password is test123)
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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 @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @object1
        EXEC @hr = sp_OADestroy @object2
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1

    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'Certificate'

    -- -------------------------------------------------------------------------------------------
    -- To have the EncapsulatedTimeStamp automatically added, we only need to do 2 things.
    -- 1) Add the <xades:EncapsulatedTimeStamp Encoding="http://uri.etsi.org/01903/v1.2.2#DER">TO BE GENERATED BY CHILKAT</xades:EncapsulatedTimeStamp>
    --    to the unsigned properties.
    -- 2) Specify the TSA URL (Timestamping Authority URL).
    --    Here we specify the TSA URL:
    -- -------------------------------------------------------------------------------------------

    DECLARE @jsonTsa int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonTsa OUT

    EXEC sp_OAMethod @jsonTsa, 'UpdateString', @success OUT, 'timestampToken.tsaUrl', 'http://timestamp.digicert.com'
    EXEC sp_OAMethod @jsonTsa, 'UpdateBool', @success OUT, 'timestampToken.requestTsaCert', 1
    EXEC sp_OAMethod @gen, 'SetTsa', @success OUT, @jsonTsa

    -- Load XML to be signed...
    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml

    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature,OmitAlreadyDefinedSigNamespace'

    -- Sign the XML...
    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 @object1
        EXEC @hr = sp_OADestroy @object2
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonTsa
        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

    -- ----------------------------------------
    -- Verify the signatures we just produced...
    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 @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @object1
        EXEC @hr = sp_OADestroy @object2
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonTsa
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END

    DECLARE @numSigs int
    EXEC sp_OAGetProperty @verifier, 'NumSignatures', @numSigs OUT
    DECLARE @verifyIdx int
    SELECT @verifyIdx = 0
    WHILE @verifyIdx < @numSigs
      BEGIN
        EXEC sp_OASetProperty @verifier, 'Selector', @verifyIdx
        DECLARE @verified int
        EXEC sp_OAMethod @verifier, 'VerifySignature', @verified OUT, 1
        IF @verified <> 1
          BEGIN
            EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @xmlToSign
            EXEC @hr = sp_OADestroy @gen
            EXEC @hr = sp_OADestroy @object1
            EXEC @hr = sp_OADestroy @object2
            EXEC @hr = sp_OADestroy @cert
            EXEC @hr = sp_OADestroy @jsonTsa
            EXEC @hr = sp_OADestroy @sbXml
            EXEC @hr = sp_OADestroy @verifier
            RETURN
          END
        SELECT @verifyIdx = @verifyIdx + 1
      END

    PRINT 'All signatures were successfully verified.'

    EXEC @hr = sp_OADestroy @xmlToSign
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @object1
    EXEC @hr = sp_OADestroy @object2
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @jsonTsa
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @verifier


END
GO