SQL Server
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 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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
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.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.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.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 Minősí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.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.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.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.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.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