(SQL Server) Get PDF DSS (Document Security Store)

See more PDF Signatures Examples

This example demonstrates how to extract the information from a PDF's DSS (Document Security Store), if a /DSS exists. (Just because a PDF is signed does not mean a /DSS will exists. In fact, the /DSS is typically created at the point of adding the 2nd or greater signature because the /DSS contains LTV (long term validation) information about the previous signature at the time of adding an additional signature.)

Note: This example requires Chilkat v9.5.0.85 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
    -- 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 @pdf int
    -- Use "Chilkat_9_5_0.Pdf" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/sign_testing_1/helloSigned2.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

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

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    EXEC sp_OAMethod @pdf, 'GetDss', @success OUT, @json
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The document security store contains certificates, OCSP responses, and CRLs.
    -- The following JSON is a sample of what the /DSS can contain.
    -- Unfortunately, our sample contains /Certs and /OCSPs, but no /CRLs.
    -- It's no problem because whatever JSON you get back, you can use the
    -- following online tool to generate code to parse.
    -- Generate Parsing Code from JSON

    -- The code generated by the online tool for this JSON is shown below..

    -- {
    --   "/VRI": {
    --     "/EC5BB34CC1F8A0C5FE674427E16E313A08C80808": {}
    --   },
    --   "/Certs": [
    --     {
    --       "serial": "02",
    --       "validFrom": "2011-08-07T19:00:00-05:00",
    --       "validTo": "2021-08-10T23:59:59-05:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "XYZ Corporation",
    --         "O": "XYZ"
    --       },
    --       "issuer": {
    --         "CN": "XYZ Corporation",
    --         "O": "XYZ"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "2048",
    --       "der": "MIIDz...Q4Zt"
    --     },
    --     {
    --       "serial": "01",
    --       "validFrom": "2011-08-07T19:00:00-05:00",
    --       "validTo": "2021-08-10T23:59:59-05:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "XYZ Corporation",
    --         "O": "XYZ"
    --       },
    --       "issuer": {
    --         "CN": "XYZ Corporation",
    --         "O": "XYZ"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "2048",
    --       "der": "MIID...jXYFc="
    --     },
    --     {
    --       "serial": "0AA125D6D6321B7E41E405DA3697C215",
    --       "validFrom": "2016-01-07T06:00:00-06:00",
    --       "validTo": "2031-01-07T12:00:00-06:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "DigiCert SHA2 Assured ID Timestamping CA",
    --         "OU": "www.digicert.com",
    --         "O": "DigiCert Inc",
    --         "C": "US"
    --       },
    --       "issuer": {
    --         "CN": "DigiCert Assured ID Root CA",
    --         "OU": "www.digicert.com",
    --         "O": "DigiCert Inc",
    --         "C": "US"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "2048",
    --       "der": "MIIF...OUg=="
    --     },
    --     {
    --       "serial": "04CD3F8568AE76C61BB0FE7160CCA76D",
    --       "validFrom": "2019-09-30T19:00:00-05:00",
    --       "validTo": "2030-10-17T00:00:00-05:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "TIMESTAMP-SHA256-2019-10-15",
    --         "O": "DigiCert, Inc.",
    --         "C": "US"
    --       },
    --       "issuer": {
    --         "CN": "DigiCert SHA2 Assured ID Timestamping CA",
    --         "OU": "www.digicert.com",
    --         "O": "DigiCert Inc",
    --         "C": "US"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "2048",
    --       "der": "MIIG...MJtPc="
    --     },
    --     {
    --       "serial": "0CE7E0E517D846FE8FE560FC1BF03039",
    --       "validFrom": "2006-11-09T18:00:00-06:00",
    --       "validTo": "2031-11-10T00:00:00-06:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "DigiCert Assured ID Root CA",
    --         "OU": "www.digicert.com",
    --         "O": "DigiCert Inc",
    --         "C": "US"
    --       },
    --       "issuer": {
    --         "CN": "DigiCert Assured ID Root CA",
    --         "OU": "www.digicert.com",
    --         "O": "DigiCert Inc",
    --         "C": "US"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "2048",
    --       "der": "MIIDt...FL6Lw8g=="
    --     },
    --     {
    --       "serial": "E4D34D01798BE686424AF7F6F0C3BF41",
    --       "validFrom": "2015-03-24T10:58:16-05:00",
    --       "validTo": "2039-12-31T23:59:59-06:00",
    --       "expired": false,
    --       "subject": {
    --         "CN": "www.xyz.com"
    --       },
    --       "issuer": {
    --         "CN": "www.xyz.com"
    --       },
    --       "keyType": "RSA",
    --       "keySize": "1024",
    --       "der": "MIIB9DCCAWG...UR10lz"
    --     }
    --   ],
    --   "/OCSPs": [
    --     {
    --       "responseStatus": 0,
    --       "responseTypeOid": "1.3.6.1.5.5.7.48.1.1",
    --       "responseTypeName": "ocspBasic",
    --       "response": {
    --         "responderIdChoice": "KeyHash",
    --         "responderKeyHash": "Reuir/SSy4IxLVGLp6chnfNtyA8=",
    --         "dateTime": "20201005173921Z",
    --         "cert": [
    --           {
    --             "hashOid": "1.3.14.3.2.26",
    --             "hashAlg": "SHA-1",
    --             "issuerNameHash": "98S+C0C1w0QzPT+uuU1uONr67FE=",
    --             "issuerKeyHash": "Reuir/SSy4IxLVGLp6chnfNtyA8=",
    --             "serialNumber": "0AA125D6D6321B7E41E405DA3697C215",
    --             "status": 0,
    --             "thisUpdate": "20201005173921Z",
    --             "nextUpdate": "20201012173921Z"
    --           }
    --         ]
    --       }
    --     },
    --     {
    --       "responseStatus": 0,
    --       "responseTypeOid": "1.3.6.1.5.5.7.48.1.1",
    --       "responseTypeName": "ocspBasic",
    --       "response": {
    --         "responderIdChoice": "KeyHash",
    --         "responderKeyHash": "9LbhIB3+Ka7S5GGlsqIlssgXNW4=",
    --         "dateTime": "20201006114501Z",
    --         "cert": [
    --           {
    --             "hashOid": "1.3.14.3.2.26",
    --             "hashAlg": "SHA-1",
    --             "issuerNameHash": "+YYA+KSr7NIxRSxCjUNQo25SyD0=",
    --             "issuerKeyHash": "9LbhIB3+Ka7S5GGlsqIlssgXNW4=",
    --             "serialNumber": "04CD3F8568AE76C61BB0FE7160CCA76D",
    --             "status": 0,
    --             "thisUpdate": "20201006114501Z",
    --             "nextUpdate": "20201013110001Z"
    --           }
    --         ]
    --       }
    --     }
    --   ]
    -- }

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

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

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

    DECLARE @serial nvarchar(4000)

    DECLARE @validFrom nvarchar(4000)

    DECLARE @validTo nvarchar(4000)

    DECLARE @expired int

    DECLARE @subjectCN nvarchar(4000)

    DECLARE @subjectO nvarchar(4000)

    DECLARE @issuerCN nvarchar(4000)

    DECLARE @issuerO nvarchar(4000)

    DECLARE @keyType nvarchar(4000)

    DECLARE @keySize nvarchar(4000)

    DECLARE @der nvarchar(4000)

    DECLARE @subjectOU nvarchar(4000)

    DECLARE @subjectC nvarchar(4000)

    DECLARE @issuerOU nvarchar(4000)

    DECLARE @issuerC nvarchar(4000)

    DECLARE @responseStatus int

    DECLARE @responseTypeOid nvarchar(4000)

    DECLARE @responseTypeName nvarchar(4000)

    DECLARE @responseResponderIdChoice nvarchar(4000)

    DECLARE @responseResponderKeyHash nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @hashOid nvarchar(4000)

    DECLARE @hashAlg nvarchar(4000)

    DECLARE @issuerNameHash nvarchar(4000)

    DECLARE @issuerKeyHash nvarchar(4000)

    DECLARE @serialNumber nvarchar(4000)

    DECLARE @status int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, '/Certs'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @serial OUT, '/Certs[i].serial'
        EXEC sp_OAMethod @json, 'StringOf', @validFrom OUT, '/Certs[i].validFrom'
        EXEC sp_OAMethod @json, 'StringOf', @validTo OUT, '/Certs[i].validTo'
        EXEC sp_OAMethod @json, 'BoolOf', @expired OUT, '/Certs[i].expired'
        EXEC sp_OAMethod @json, 'StringOf', @subjectCN OUT, '/Certs[i].subject.CN'
        EXEC sp_OAMethod @json, 'StringOf', @subjectO OUT, '/Certs[i].subject.O'
        EXEC sp_OAMethod @json, 'StringOf', @issuerCN OUT, '/Certs[i].issuer.CN'
        EXEC sp_OAMethod @json, 'StringOf', @issuerO OUT, '/Certs[i].issuer.O'
        EXEC sp_OAMethod @json, 'StringOf', @keyType OUT, '/Certs[i].keyType'
        EXEC sp_OAMethod @json, 'StringOf', @keySize OUT, '/Certs[i].keySize'
        EXEC sp_OAMethod @json, 'StringOf', @der OUT, '/Certs[i].der'
        EXEC sp_OAMethod @json, 'StringOf', @subjectOU OUT, '/Certs[i].subject.OU'
        EXEC sp_OAMethod @json, 'StringOf', @subjectC OUT, '/Certs[i].subject.C'
        EXEC sp_OAMethod @json, 'StringOf', @issuerOU OUT, '/Certs[i].issuer.OU'
        EXEC sp_OAMethod @json, 'StringOf', @issuerC OUT, '/Certs[i].issuer.C'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, '/OCSPs'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'IntOf', @responseStatus OUT, '/OCSPs[i].responseStatus'
        EXEC sp_OAMethod @json, 'StringOf', @responseTypeOid OUT, '/OCSPs[i].responseTypeOid'
        EXEC sp_OAMethod @json, 'StringOf', @responseTypeName OUT, '/OCSPs[i].responseTypeName'
        EXEC sp_OAMethod @json, 'StringOf', @responseResponderIdChoice OUT, '/OCSPs[i].response.responderIdChoice'
        EXEC sp_OAMethod @json, 'StringOf', @responseResponderKeyHash OUT, '/OCSPs[i].response.responderKeyHash'
        EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.dateTime', 0, @responseDateTime
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '/OCSPs[i].response.cert'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @hashOid OUT, '/OCSPs[i].response.cert[j].hashOid'
            EXEC sp_OAMethod @json, 'StringOf', @hashAlg OUT, '/OCSPs[i].response.cert[j].hashAlg'
            EXEC sp_OAMethod @json, 'StringOf', @issuerNameHash OUT, '/OCSPs[i].response.cert[j].issuerNameHash'
            EXEC sp_OAMethod @json, 'StringOf', @issuerKeyHash OUT, '/OCSPs[i].response.cert[j].issuerKeyHash'
            EXEC sp_OAMethod @json, 'StringOf', @serialNumber OUT, '/OCSPs[i].response.cert[j].serialNumber'
            EXEC sp_OAMethod @json, 'IntOf', @status OUT, '/OCSPs[i].response.cert[j].status'
            EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.cert[j].thisUpdate', 0, @thisUpdate
            EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.cert[j].nextUpdate', 0, @nextUpdate
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @responseDateTime
    EXEC @hr = sp_OADestroy @thisUpdate
    EXEC @hr = sp_OADestroy @nextUpdate


END
GO