SQL Server Requires Chilkat v11.0.0+
SQL Server
RFC3161 Timestamp Client - Fetch from Timestamp Authority (TSA) and Verify
See more HTTP Examples
Sends an RFC 3161 timestamp request to a TSA (Timestamp Authority) server and validates the timestamp token response.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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First sha-256 hash the data that is to be timestamped.
-- In this example, the data is the string "Hello World"
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
DECLARE @base64Hash nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashStringENC', @base64Hash OUT, 'Hello World'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @requestToken int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @requestToken OUT
DECLARE @optionalPolicyOid nvarchar(4000)
SELECT @optionalPolicyOid = ''
DECLARE @addNonce int
SELECT @addNonce = 0
DECLARE @requestTsaCert int
SELECT @requestTsaCert = 1
-- Create a time-stamp request token
EXEC sp_OAMethod @http, 'CreateTimestampRequest', @success OUT, 'sha256', @base64Hash, @optionalPolicyOid, @addNonce, @requestTsaCert, @requestToken
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
RETURN
END
-- Send the time-stamp request token to the TSA.
-- This is the equivalent of the following CURL command:
-- curl -H "Content-Type: application/timestamp-query" --data-binary '@file.tsq' https://freetsa.org/tsr > file.tsr
DECLARE @tsaUrl nvarchar(4000)
SELECT @tsaUrl = 'https://freetsa.org/tsr'
-- Another timestamp server you could try is: http://timestamp.digicert.com
SELECT @tsaUrl = 'http://timestamp.digicert.com'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpBd', @success OUT, 'POST', @tsaUrl, @requestToken, 'application/timestamp-query', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Get the timestamp reply from the HTTP response object.
DECLARE @timestampReply int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @timestampReply OUT
EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @timestampReply
-- Show the base64 encoded timestamp reply.
EXEC sp_OAMethod @timestampReply, 'GetEncoded', @sTmp0 OUT, 'base64'
PRINT @sTmp0
-- Let's verify the timestamp reply against the TSA's cert, which we've previously downloaded.
-- See https://freetsa.org/index_en.php
DECLARE @tsaCert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @tsaCert OUT
EXEC sp_OAMethod @tsaCert, 'LoadFromFile', @success OUT, 'qa_data/certs/freetsa.org.cer'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tsaCert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @tsaCert
RETURN
END
-- The VerifyTimestampReply method will return one of the following values:
-- -1: The timestampReply does not contain a valid timestamp reply.
-- -2: The timestampReply is a valid timestamp reply, but failed verification using the public key of the tsaCert.
-- 0: Granted and verified.
-- 1: Granted and verified, with mods (see RFC 3161)
-- 2: Rejected.
-- 3: Waiting.
-- 4: Revocation Warning
-- 5: Revocation Notification
DECLARE @pkiStatus int
EXEC sp_OAMethod @http, 'VerifyTimestampReply', @pkiStatus OUT, @timestampReply, @tsaCert
IF @pkiStatus < 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @tsaCert
RETURN
END
PRINT 'pkiStatus = ' + @pkiStatus
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @http, 'GetLastJsonData', NULL, @json
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- The JSON looks like the following.
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "timestampReply": {
-- "pkiStatus": {
-- "value": 0,
-- "meaning": "granted"
-- }
-- },
-- "pkcs7": {
-- "verify": {
-- "digestAlgorithms": [
-- "sha256"
-- ],
-- "signerInfo": [
-- {
-- "cert": {
-- "serialNumber": "04CD3F8568AE76C61BB0FE7160CCA76D",
-- "issuerCN": "DigiCert SHA2 Assured ID Timestamping CA",
-- "digestAlgOid": "2.16.840.1.101.3.4.2.1",
-- "digestAlgName": "SHA256"
-- },
-- "contentType": "1.2.840.113549.1.9.16.1.4",
-- "signingTime": "200405023019Z",
-- "messageDigest": "f14zOsdnN9vyyV3HjjBiLzNDi1PF28hAFMODxNkNRZs=",
-- "signingAlgOid": "1.2.840.113549.1.1.1",
-- "signingAlgName": "RSA-PKCSV-1_5",
-- "authAttr": {
-- "1.2.840.113549.1.9.3": {
-- "name": "contentType",
-- "oid": "1.2.840.113549.1.9.16.1.4"
-- },
-- "1.2.840.113549.1.9.5": {
-- "name": "signingTime",
-- "utctime": "200405023019Z"
-- },
-- "1.2.840.113549.1.9.16.2.12": {
-- "name": "signingCertificate",
-- "der": "MBowGDAWBBQDJb1QXtqWMC3CL0+gHkwovig0xQ=="
-- },
-- "1.2.840.113549.1.9.4": {
-- "name": "messageDigest",
-- "digest": "f14zOsdnN9vyyV3HjjBiLzNDi1PF28hAFMODxNkNRZs="
-- }
-- }
-- }
-- ]
-- }
-- }
-- }
DECLARE @signingTime int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @signingTime OUT
DECLARE @authAttrSigningTimeUtctime int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @authAttrSigningTimeUtctime OUT
DECLARE @strVal nvarchar(4000)
DECLARE @certSerialNumber nvarchar(4000)
DECLARE @certIssuerCN nvarchar(4000)
DECLARE @certDigestAlgOid nvarchar(4000)
DECLARE @certDigestAlgName nvarchar(4000)
DECLARE @contentType nvarchar(4000)
DECLARE @messageDigest nvarchar(4000)
DECLARE @signingAlgOid nvarchar(4000)
DECLARE @signingAlgName nvarchar(4000)
DECLARE @authAttrContentTypeName nvarchar(4000)
DECLARE @authAttrContentTypeOid nvarchar(4000)
DECLARE @authAttrSigningTimeName nvarchar(4000)
DECLARE @authAttrSigningCertificateName nvarchar(4000)
DECLARE @authAttrSigningCertificateDer nvarchar(4000)
DECLARE @authAttrMessageDigestName nvarchar(4000)
DECLARE @authAttrMessageDigestDigest nvarchar(4000)
DECLARE @timestampReplyPkiStatusValue int
EXEC sp_OAMethod @json, 'IntOf', @timestampReplyPkiStatusValue OUT, 'timestampReply.pkiStatus.value'
DECLARE @timestampReplyPkiStatusMeaning nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @timestampReplyPkiStatusMeaning OUT, 'timestampReply.pkiStatus.meaning'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'pkcs7.verify.digestAlgorithms'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'pkcs7.verify.digestAlgorithms[i]'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'pkcs7.verify.signerInfo'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @certSerialNumber OUT, 'pkcs7.verify.signerInfo[i].cert.serialNumber'
EXEC sp_OAMethod @json, 'StringOf', @certIssuerCN OUT, 'pkcs7.verify.signerInfo[i].cert.issuerCN'
EXEC sp_OAMethod @json, 'StringOf', @certDigestAlgOid OUT, 'pkcs7.verify.signerInfo[i].cert.digestAlgOid'
EXEC sp_OAMethod @json, 'StringOf', @certDigestAlgName OUT, 'pkcs7.verify.signerInfo[i].cert.digestAlgName'
EXEC sp_OAMethod @json, 'StringOf', @contentType OUT, 'pkcs7.verify.signerInfo[i].contentType'
EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'pkcs7.verify.signerInfo[i].signingTime', 0, @signingTime
EXEC sp_OAMethod @json, 'StringOf', @messageDigest OUT, 'pkcs7.verify.signerInfo[i].messageDigest'
EXEC sp_OAMethod @json, 'StringOf', @signingAlgOid OUT, 'pkcs7.verify.signerInfo[i].signingAlgOid'
EXEC sp_OAMethod @json, 'StringOf', @signingAlgName OUT, 'pkcs7.verify.signerInfo[i].signingAlgName'
EXEC sp_OAMethod @json, 'StringOf', @authAttrContentTypeName OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.3".name'
EXEC sp_OAMethod @json, 'StringOf', @authAttrContentTypeOid OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.3".oid'
EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningTimeName OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.5".name'
EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.5".utctime', 0, @authAttrSigningTimeUtctime
EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateName OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.16.2.12".name'
EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateDer OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.16.2.12".der'
EXEC sp_OAMethod @json, 'StringOf', @authAttrMessageDigestName OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.4".name'
EXEC sp_OAMethod @json, 'StringOf', @authAttrMessageDigestDigest OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.4".digest'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @tsaCert
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @signingTime
EXEC @hr = sp_OADestroy @authAttrSigningTimeUtctime
END
GO