(SQL Server) XAdES using TSA Requiring Client Certificate

See more XML Digital Signatures Examples

Demonstrates how to create an XMLDSig (XAdES) signed document which includes an EncapsulatedTimestamp using a TSA (TimeStamp Authority) server requiring client certificate authentication. One such TSA is https://www3.postsignum.cz/TSS/TSS_crt/

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

    -- Load the XML to be signed.  For example, the XML to be signed might contain something like this:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <TransakcniLogSystemu xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://nsess.public.cz/erms_trans/v_01_01" Id="Signature1">
    --   <TransLogInfo>
    --     <Identifikator>XYZ ABC</Identifikator>
    --     <DatumVzniku>2022-12-20T14:39:02.3625922+01:00</DatumVzniku>
    --     <DatumCasOd>2022-12-20T14:26:26.88</DatumCasOd>
    --     <DatumCasDo>2022-12-20T14:39:02.287</DatumCasDo>
    --     <Software>XYZ</Software>
    --     <VerzeSoftware>2.0.19.32</VerzeSoftware>
    --   </TransLogInfo>
    --   <Udalosti>
    --     <Udalost>
    --       <Poradi>1</Poradi>
    -- ...

    -- Load the XML to be signed from a file.
    -- (XML can be loaded from other source, such as a string variable.)
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, 'xmlToSign.xml', 'utf-8'

    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', 'TransakcniLogSystemu'
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
    EXEC sp_OASetProperty @gen, 'SigId', 'SignatureID-Signature1'
    EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
    EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- Set the KeyInfoId before adding references..
    EXEC sp_OASetProperty @gen, 'KeyInfoId', 'KeyInfoId-Signature-Signature1'

    -- Create an Object to be added to the Signature.

    -- Note: Chilkat will automatically fill in the values marked as "TO BE GENERATED BY CHILKAT" at the time of signing.
    -- The EncapsulatedTimestamp will be automatically generated.

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

    EXEC sp_OASetProperty @object1, 'Tag', 'xades:QualifyingProperties'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'xmlns:xades', 'http://uri.etsi.org/01903/v1.3.2#'
    EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'Target', '#Signature1'

    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties', 1, 'Id', 'SignedProperties-Signature-Signature1'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningTime', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestMethod', 1, 'Algorithm', 'http://www.w3.org/2001/04/xmlenc#sha256'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestValue', 'TO BE GENERATED BY CHILKAT'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:IssuerSerialV2', 'TO BE GENERATED BY CHILKAT'

    -- The EncapsulatedTimestamp will be included in the unsigned properties.
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:UnsignedProperties|xades:UnsignedSignatureProperties|xades:SignatureTimeStamp', 1, 'Id', 'signature-timestamp-5561-8212-3316-5191'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:UnsignedProperties|xades:UnsignedSignatureProperties|xades:SignatureTimeStamp|ds:CanonicalizationMethod', 1, 'Algorithm', 'http://www.w3.org/2001/10/xml-exc-c14n#'
    EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:UnsignedProperties|xades:UnsignedSignatureProperties|xades:SignatureTimeStamp|xades:EncapsulatedTimeStamp', 1, 'Encoding', 'http://uri.etsi.org/01903/v1.2.2#DER'
    EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:UnsignedProperties|xades:UnsignedSignatureProperties|xades:SignatureTimeStamp|xades:EncapsulatedTimeStamp', 'TO BE GENERATED BY CHILKAT'

    EXEC sp_OAMethod @object1, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @gen, 'AddObject', @success OUT, 'XadesObjectId-Signature1', @sTmp0, '', ''

    -- -------- Reference 1 --------
    EXEC sp_OAMethod @gen, 'AddObjectRef', @success OUT, 'SignedProperties-Signature-Signature1', 'sha256', 'EXCL_C14N', '', 'http://uri.etsi.org/01903#SignedProperties'

    -- -------- Reference 2 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'KeyInfoId-Signature-Signature1', 'sha256', 'EXCL_C14N', '', ''
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'KeyInfoId-Signature-Signature1', 'ReferenceKeyInfo'

    -- -------- Reference 3 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, '', 'sha256', 'EXCL_C14N', '', ''
    EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, '', 'Reference-Signature1'

    -- 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 @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @object1
        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'

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

    -- -------------------------------------------------------------------------------------------
    -- To have the EncapsulatedTimeStamp automatically added... 
    -- 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.  (This was accomplished in the above code.)
    -- 2) Specify the TSA URL (Timestamping Authority URL).
    --    Here we specify the TSA URL:
    -- -------------------------------------------------------------------------------------------

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

    EXEC sp_OAMethod @jsonTsa, 'UpdateString', @success OUT, 'timestampToken.tsaUrl', 'https://www3.postsignum.cz/TSS/TSS_crt/'
    EXEC sp_OAMethod @jsonTsa, 'UpdateBool', @success OUT, 'timestampToken.requestTsaCert', 1
    EXEC sp_OAMethod @gen, 'SetTsa', @success OUT, @jsonTsa

    -- -------------------------------------------------------------------------------------------
    -- In this case, the TSA requires client certificate authentication.
    -- To provide your client certificate, the application will instantiate a Chilkat HTTP object,
    -- then set it up with a SSL/TLS client certificate, and then tell the XmlDSigGen object
    -- to use the HTTP object for connections to the TSA server.
    -- -------------------------------------------------------------------------------------------
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OAMethod @http, 'SetSslClientCertPfx', @success OUT, '/home/bob/pfxFiles/myClientSideCertWithPrivateKey.pfx', 'pfxPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @object1
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonTsa
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Tell the XmlDSigGen object to use the above HTTP object for TSA communications.
    EXEC sp_OAMethod @gen, 'SetHttpObj', NULL, @http

    -- 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 @sbXml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @object1
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonTsa
        EXEC @hr = sp_OADestroy @http
        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 @sbXml
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @object1
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @jsonTsa
    EXEC @hr = sp_OADestroy @http


END
GO