(SQL Server) Validate CAdES-T Signature (.p7m)

Validates a CAdES-T CMS signature and extracts the time-stamp token and gets information about it. Also validates the time-stamp token.

Note: This example requires Chilkat v9.5.0.78 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    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

    DECLARE @success int

    -- Indicate that the CAdES-T timestamp tokens must also pass validation for the signature to be validated.
    DECLARE @cmsOptions int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @cmsOptions OUT

    EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'ValidateTimestampTokens', 1
    EXEC sp_OAMethod @cmsOptions, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @crypt, 'CmsOptions', @sTmp0

    -- Validate the .p7m and extract the original signed data to an output file.
    -- Note: The timestampToken is an unauthenticated attribute.  See the code below that parses the LastJsonData
    -- for details about examining timestampToken.
    EXEC sp_OAMethod @crypt, 'VerifyP7M', @success OUT, 'qa_data/cades/CAdES-T/Signature-C-T-1.p7m', 'qa_output/out.dat'

    -- Get information about the CMS signature in the LastJsonData.
    -- The detailed results of the signature validation are available in LastJsonData.
    -- (If the non-success return status was caused by an error such as "file not found", then the
    -- LastJsonData would be empty.)
    DECLARE @json int
    EXEC sp_OAMethod @crypt, 'LastJsonData', @json OUT
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Here is a sample result:
    -- See the parsing code below..

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "pkcs7": {
    --     "verify": {
    --       "digestAlgorithms": [
    --         "sha256"
    --       ],
    --       "signerInfo": [
    --         {
    --           "cert": {
    --             "serialNumber": "00DCB814678CDB",
    --             "issuerCN": "LevelBCAOK",
    --             "issuerDN": "",
    --             "digestAlgOid": "2.16.840.1.101.3.4.2.1",
    --             "digestAlgName": "SHA256"
    --           },
    --           "contentType": "1.2.840.113549.1.7.1",
    --           "signingTime": "131203065741Z",
    --           "messageDigest": "JJZt41Nt8VsYahP+Xti4rR3vBDkUfRd6gquItl6R5Os=",
    --           "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.7.1"
    --             },
    --             "1.2.840.113549.1.9.5": {
    --               "name": "signingTime",
    --               "utctime": "131203065741Z"
    --             },
    --             "1.2.840.113549.1.9.4": {
    --               "name": "messageDigest",
    --               "digest": "JJZt41Nt8VsYahP+Xti4rR3vBDkUfRd6gquItl6R5Os="
    --             },
    --             "1.2.840.113549.1.9.16.2.47": {
    --               "name": "signingCertificateV2",
    --               "der": "MIGIMIGFMIGCBCBJrxOU0w0dWGsVovjLv9QDH3syB5mLVv3grSYA40x9IDBeMFOkUTBPMQswCQYDVQQGEwJGUjENMAsGA1UEChMERVRTSTEcMBoGA1UECwwTUGx1Z3Rlc3RzXzIwMTMtMjAxNDETMBEGA1UEAxMKTGV2ZWxCQ0FPSwIHANy4FGeM2w=="
    --             }
    --           },
    --           "unauthAttr": {
    --             "1.2.840.113549.1.9.16.2.14": {
    --               "name": "timestampToken",
    --               "der": "MIIL+AYJKoZI...u7CfcjURNTY=",
    --               "verify": {
    --                 "digestAlgorithms": [
    --                   "sha256"
    --                 ],
    --                 "signerInfo": [
    --                   {
    --                     "cert": {
    --                       "serialNumber": "01AA4592D36C61",
    --                       "issuerCN": "RootCAOK",
    --                       "issuerDN": "",
    --                       "digestAlgOid": "2.16.840.1.101.3.4.2.1",
    --                       "digestAlgName": "SHA256"
    --                     },
    --                     "contentType": "1.2.840.113549.1.9.16.1.4",
    --                     "messageDigest": "NSsMUrfoyCQ0OszPE1YLx1j3EyyCiBmnE5Sua6ghu/Q=",
    --                     "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.4": {
    --                         "name": "messageDigest",
    --                         "digest": "NSsMUrfoyCQ0OszPE1YLx1j3EyyCiBmnE5Sua6ghu/Q="
    --                       },
    --                       "1.2.840.113549.1.9.16.2.47": {
    --                         "name": "signingCertificateV2",
    --                         "der": "MIGGMIGDMIGABCDB/np5UxvhcPnSxD2Kme+C88uXGCMWLAvFPHNvTApTWDBcMFGkTzBNMQswCQYDVQQGEwJGUjENMAsGA1UEChMERVRTSTEcMBoGA1UECwwTUGx1Z3Rlc3RzXzIwMTMtMjAxNDERMA8GA1UEAxMIUm9vdENBT0sCBwGqRZLTbGE="
    --                       }
    --                     }
    --                   }
    --                 ]
    --               },
    --               "timestampSignatureVerified": true,
    --               "tstInfo": {
    --                 "tsaPolicyId": "1.3.6.1.4.1.2706.2.2.5.2.1.1.1",
    --                 "messageImprint": {
    --                   "hashAlg": "sha256",
    --                   "digest": "C8xEe9NA4X1cUyHGX9zG89ipmQ2byFs3aa+Xe4Fz2P0=",
    --                   "digestMatches": true
    --                 },
    --                 "serialNumber": "313E162121D922",
    --                 "genTime": "20131203065742Z"
    --               }
    --             }
    --           }
    --         }
    --       ]
    --     }
    --   }
    -- }
    -- 

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @strVal nvarchar(4000)

    DECLARE @certSerialNumber nvarchar(4000)

    DECLARE @certIssuerCN nvarchar(4000)

    DECLARE @certIssuerDN nvarchar(4000)

    DECLARE @certDigestAlgOid nvarchar(4000)

    DECLARE @certDigestAlgName nvarchar(4000)

    DECLARE @contentType nvarchar(4000)

    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 @messageDigest nvarchar(4000)

    DECLARE @signingAlgOid nvarchar(4000)

    DECLARE @signingAlgName nvarchar(4000)

    DECLARE @authAttrContentTypeName nvarchar(4000)

    DECLARE @authAttrContentTypeOid nvarchar(4000)

    DECLARE @authAttrSigningTimeName nvarchar(4000)

    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 @authAttrMessageDigestName nvarchar(4000)

    DECLARE @authAttrMessageDigestDigest nvarchar(4000)

    DECLARE @authAttrSigningCertificateV2Name nvarchar(4000)

    DECLARE @authAttrSigningCertificateV2Der nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenName nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenDer nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenTimestampSignatureVerified int

    DECLARE @unauthAttrTimestampTokenTstInfoTsaPolicyId nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenTstInfoMessageImprintHashAlg nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenTstInfoMessageImprintDigest nvarchar(4000)

    DECLARE @unauthAttrTimestampTokenTstInfoMessageImprintDigestMatches int

    DECLARE @unauthAttrTimestampTokenTstInfoSerialNumber nvarchar(4000)

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

    DECLARE @j int

    DECLARE @count_j int

    -- Iterate over the hash algorithms used in the signature.
    SELECT @i = 0
    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

    -- For each signer...
    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

        -- Get information about the certificate used by this signer.
        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', @certIssuerDN OUT, 'pkcs7.verify.signerInfo[i].cert.issuerDN'
        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'

        -- Get additional information for this signer, such as the signingTime, signature algorithm, etc.
        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'

        -- --------------------------------
        -- Examine authenticated attributes.
        -- --------------------------------

        -- contentType
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.3"'
        IF @iTmp0 = 1
          BEGIN
            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'
          END

        -- signingTime
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.5"'
        IF @iTmp0 = 1
          BEGIN
            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
          END

        -- messageDigest
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.4"'
        IF @iTmp0 = 1
          BEGIN
            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'
          END

        -- signingCertificateV2
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.16.2.47"'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateV2Name OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.16.2.47".name'
            EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateV2Der OUT, 'pkcs7.verify.signerInfo[i].authAttr."1.2.840.113549.1.9.16.2.47".der'
          END

        -- --------------------------------
        -- Examine unauthenticated attributes.
        -- --------------------------------

        -- timestampToken  (the timestampToken is what makes this signature a CAdES-T)
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14"'
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenName OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".name'
            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenDer OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".der'

            -- This is where we find out if the timestampToken's signature is valid.
            EXEC sp_OAMethod @json, 'BoolOf', @unauthAttrTimestampTokenTimestampSignatureVerified OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".timestampSignatureVerified'

            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenTstInfoTsaPolicyId OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.tsaPolicyId'
            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenTstInfoMessageImprintHashAlg OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.messageImprint.hashAlg'
            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenTstInfoMessageImprintDigest OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.messageImprint.digest'

            -- Here is where we check to see if the digest in the timestampToken's messageImprint matches the digest of the signature of this signerInfo
            EXEC sp_OAMethod @json, 'BoolOf', @unauthAttrTimestampTokenTstInfoMessageImprintDigestMatches OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.messageImprint.digestMatches'

            EXEC sp_OAMethod @json, 'StringOf', @unauthAttrTimestampTokenTstInfoSerialNumber OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.serialNumber'

            -- Here is where we get the date/time of the timestampToken (i.e. when it was timestamped)
            EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".tstInfo.genTime', 0, @unauthAttrTimestampTokenTstInfoGenTime

            -- The following code gets details about the validity of the timestampToken's signature...
            SELECT @j = 0
            EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.digestAlgorithms'
            WHILE @j < @count_j
              BEGIN
                EXEC sp_OASetProperty @json, 'J', @j
                EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.digestAlgorithms[j]'
                SELECT @j = @j + 1
              END
            SELECT @j = 0
            EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo'
            WHILE @j < @count_j
              BEGIN
                EXEC sp_OASetProperty @json, 'J', @j
                EXEC sp_OAMethod @json, 'StringOf', @certSerialNumber OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].cert.serialNumber'
                EXEC sp_OAMethod @json, 'StringOf', @certIssuerCN OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].cert.issuerCN'
                EXEC sp_OAMethod @json, 'StringOf', @certIssuerDN OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].cert.issuerDN'
                EXEC sp_OAMethod @json, 'StringOf', @certDigestAlgOid OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].cert.digestAlgOid'
                EXEC sp_OAMethod @json, 'StringOf', @certDigestAlgName OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].cert.digestAlgName'
                EXEC sp_OAMethod @json, 'StringOf', @contentType OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].contentType'
                EXEC sp_OAMethod @json, 'StringOf', @messageDigest OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].messageDigest'
                EXEC sp_OAMethod @json, 'StringOf', @signingAlgOid OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].signingAlgOid'
                EXEC sp_OAMethod @json, 'StringOf', @signingAlgName OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].signingAlgName'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrContentTypeName OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.3".name'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrContentTypeOid OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.3".oid'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrMessageDigestName OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.4".name'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrMessageDigestDigest OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.4".digest'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateV2Name OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.16.2.47".name'
                EXEC sp_OAMethod @json, 'StringOf', @authAttrSigningCertificateV2Der OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.2.840.113549.1.9.16.2.14".verify.signerInfo[j].authAttr."1.2.840.113549.1.9.16.2.47".der'
                SELECT @j = @j + 1
              END

          END

        SELECT @i = @i + 1
      END

    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'CAdES-T verification failed.'
      END
    ELSE
      BEGIN

        PRINT 'CAdES-T signature is valid.'
      END

    EXEC @hr = sp_OADestroy @json


    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cmsOptions
    EXEC @hr = sp_OADestroy @signingTime
    EXEC @hr = sp_OADestroy @authAttrSigningTimeUtctime
    EXEC @hr = sp_OADestroy @unauthAttrTimestampTokenTstInfoGenTime


END
GO