(SQL Server) Extract TSTInfo from RFC3161 Timestamp Reply

See more HTTP Examples

Sends an RFC 3161 timestamp request to a TSA (Timestamp Authority) server and converts the timestamp reply to XML, and then extracts the TSTInfo from the XML and converts it to XML.

For more information, see https://www.ietf.org/rfc/rfc3161.txt

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, 'HashFileENC', @base64Hash OUT, 'qa_data/hamlet.xml'

    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 = 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' http://timestamp.digicert.com > file.tsr
    DECLARE @tsaUrl nvarchar(4000)
    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 = 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

    -- 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

    -- Convert the binary timestamp reply to XML
    DECLARE @asn int
    -- Use "Chilkat_9_5_0.Asn" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Asn', @asn OUT

    EXEC sp_OAMethod @asn, 'LoadBd', @success OUT, @timestampReply
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @asn, '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 @asn
        RETURN
      END

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

    EXEC sp_OAMethod @asn, 'AsnToXml', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0

    -- Extract the TSTInfo from the XML.
    -- The TSTInfo is this base64 encoded ASN.1 

    -- TSTInfo ::= SEQUENCE  {
    --    version                      INTEGER  { v1(1) },
    --    policy                       TSAPolicyId,
    --    messageImprint               MessageImprint,
    --      -- MUST have the same value as the similar field in
    --      -- TimeStampReq
    --    serialNumber                 INTEGER,
    --     -- Time-Stamping users MUST be ready to accommodate integers
    --     -- up to 160 bits.
    --    genTime                      GeneralizedTime,
    --    accuracy                     Accuracy                 OPTIONAL,
    --    ordering                     BOOLEAN             DEFAULT FALSE,
    --    nonce                        INTEGER                  OPTIONAL,
    --      -- MUST be present if the similar field was present
    --      -- in TimeStampReq.  In that case it MUST have the same value.
    --    tsa                          [0] GeneralName          OPTIONAL,
    --    extensions                   [1] IMPLICIT Extensions   OPTIONAL  }

    DECLARE @tstInfoBase64 nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @tstInfoBase64 OUT, 'sequence[1]|contextSpecific|sequence|sequence|contextSpecific|octets'

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

    EXEC sp_OAMethod @bdTstInfo, 'AppendEncoded', @success OUT, @tstInfoBase64, 'base64'

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

    EXEC sp_OAMethod @asnTstInfo, 'LoadBd', @success OUT, @bdTstInfo
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @asnTstInfo, '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 @asn
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bdTstInfo
        EXEC @hr = sp_OADestroy @asnTstInfo
        RETURN
      END

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

    EXEC sp_OAMethod @asnTstInfo, 'AsnToXml', @sTmp0 OUT
    EXEC sp_OAMethod @xmlTstInfo, 'LoadXml', @success OUT, @sTmp0
    EXEC sp_OAMethod @xmlTstInfo, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Here's the TSTInfo XML:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <sequence>
    --     <int>01</int>
    --     <oid>2.16.840.1.114412.7.1</oid>
    --     <sequence>
    --         <sequence>
    --             <oid>2.16.840.1.101.3.4.2.1</oid>
    --             <null/>
    --         </sequence>
    --         <octets>4sRRyWOzC7EOic4fQ9+Op1pa10DbgoBGjBvkq09LZmE=</octets>
    --     </sequence>
    --     <int>00AD2C86E49872597B60F87D5C54BCFFAE</int>
    --     <universal tag="24" constructed="0">MjAyMzAzMTYxMTQ5NTJa</universal>
    -- </sequence>

    --    The genTime (GeneralizedTime) is contained in the final "universal" XML element and is 
    --    in base64. It is the time at which the time-stamp token has been created by
    --    the TSA. After decoding from base64, it is:
    -- 
    --    The syntax is: YYYYMMDDhhmmss[.s...]Z
    --    Example: 19990609001326.34352Z

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

    EXEC sp_OAMethod @xmlTstInfo, 'GetChildContent', @sTmp0 OUT, 'universal'
    EXEC sp_OAMethod @sbGenTime, 'DecodeAndAppend', @success OUT, @sTmp0, 'base64', 'utf-8'
    EXEC sp_OAMethod @sbGenTime, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Result:
    -- 20230316115718Z

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @requestToken
    EXEC @hr = sp_OADestroy @timestampReply
    EXEC @hr = sp_OADestroy @asn
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @bdTstInfo
    EXEC @hr = sp_OADestroy @asnTstInfo
    EXEC @hr = sp_OADestroy @xmlTstInfo
    EXEC @hr = sp_OADestroy @sbGenTime


END
GO