SQL Server
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 SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
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
-- 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
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
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
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
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
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
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