(SQL Server) RFC3161 Timestamp Client - Fetch from Timestamp Authority (TSA) and Verify

Sends an RFC 3161 timestamp request to a TSA (Timestamp Authority) server and validates the timestamp token response.

Note: This example requires Chilkat v9.5.0.75 or greater.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Note: Requires Chilkat v9.5.0.75 or greater.

    -- 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 @success int

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    DECLARE @requestToken int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    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 <> 1
      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 sp_OAMethod @http, 'PBinaryBd', @resp OUT, 'POST', @tsaUrl, @requestToken, 'application/timestamp-query', 0, 0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      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

    -- Get the timestamp reply from the HTTP response object.
    DECLARE @timestampReply int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @timestampReply OUT

    EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @timestampReply
    EXEC @hr = sp_OADestroy @resp

    -- 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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @tsaCert OUT

    EXEC sp_OAMethod @tsaCert, 'LoadFromFile', @success OUT, 'qa_data/certs/freetsa.org.cer'
    IF @success <> 1
      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 @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 @timestampReply
        EXEC @hr = sp_OADestroy @tsaCert
        RETURN
      END


    PRINT 'pkiStatus = ' + @pkiStatus

    DECLARE @json int
    EXEC sp_OAMethod @http, 'LastJsonData', @json OUT
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The LastJsonData looks like the following.
    -- Note: The "timestampReply.pkiStatus" portion of the LastJsonData was added in Chilkat v9.5.0.83

    -- 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
    -- Use "Chilkat_9_5_0.DtObj" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.DtObj', @signingTime OUT

    DECLARE @authAttrSigningTimeUtctime int
    -- Use "Chilkat_9_5_0.DtObj" for versions of Chilkat < 10.0.0
    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 @json


    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @requestToken
    EXEC @hr = sp_OADestroy @timestampReply
    EXEC @hr = sp_OADestroy @tsaCert
    EXEC @hr = sp_OADestroy @signingTime
    EXEC @hr = sp_OADestroy @authAttrSigningTimeUtctime


END
GO