(SQL Server) Verify Authenticode Signature of EXE or DLL

See more Code Signing Examples

Demonstrates how to verify an Authenticode signed EXE or DLL.

Note: Chilkat's code signing class was added in v9.5.0.97

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.

    -- You can verify a signed DLL or EXE.
    DECLARE @path nvarchar(4000)
    SELECT @path = 'c:/someDir/something.dll'

    -- The verify method returns an overall indicator of whether
    -- the EXE or DLL can be trusted or not.
    -- The details of the signature are emitted to the JSON object
    -- passed in the last argument.

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

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

    DECLARE @valid int
    EXEC sp_OAMethod @validator, 'VerifySignature', @valid OUT, @path, @json
    IF @valid = 0
      BEGIN
        -- Validation failed.
        EXEC sp_OAGetProperty @validator, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        -- You can also examine the details of the validation (see below)
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @validator
        RETURN
      END

    -- Examine the details of the Authenticode signature
    -- println json.Emit();

    -- An example of the JSON details of an authenticode signature, with selected parsing code, is shown below.
    -- 
    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "pkcs7": {
    --     "verify": {
    --       "peFile": {
    --         "hashOid": "2.16.840.1.101.3.4.2.1",
    --         "hash": "q9tzWEcea8f8kaMXG8LpWNPe9JIW7aKccYWuL3mrCBw="
    --       },
    --       "certs": [
    --         {
    --           "issuerCN": "AAA Certificate Services",
    --           "serial": "48FC93B46055948D36A7C98A89D69416"
    --         },
    --         {
    --           "issuerCN": "Sectigo Public Code Signing Root R46",
    --           "serial": "621D6D0C52019E3B9079152089211C0A"
    --         },
    --         {
    --           "issuerCN": "Sectigo Public Code Signing CA R36",
    --           "serial": "3FF5B69109BFD4046C92CC0D18EE23C2"
    --         }
    --       ],
    --       "digestAlgorithms": [
    --         "sha256"
    --       ],
    --       "signerInfo": [
    --         {
    --           "cert": {
    --             "serialNumber": "3FF5B69109BFD4046C92CC0D18EE23C2",
    --             "issuerCN": "Sectigo Public Code Signing CA R36",
    --             "digestAlgOid": "2.16.840.1.101.3.4.2.1",
    --             "digestAlgName": "SHA256"
    --           },
    --           "contentType": "1.3.6.1.4.1.311.2.1.4",
    --           "messageDigest": "4MkPVkY4qdwoVAj5JcCvn3ISSS5yqtf1+KmIs/Ckni4=",
    --           "signingAlgOid": "1.2.840.113549.1.1.1",
    --           "signingAlgName": "RSA-PKCSV-1_5",
    --           "authAttr": {
    --             "1.3.6.1.4.1.311.2.1.12": {
    --               "der": "MAA="
    --             },
    --             "1.2.840.113549.1.9.3": {
    --               "name": "contentType",
    --               "oid": "1.3.6.1.4.1.311.2.1.4"
    --             },
    --             "1.3.6.1.4.1.311.2.1.11": {
    --               "der": "MAwGCisGAQQBgjcCARU="
    --             },
    --             "1.2.840.113549.1.9.4": {
    --               "name": "messageDigest",
    --               "digest": "4MkPVkY4qdwoVAj5JcCvn3ISSS5yqtf1+KmIs/Ckni4="
    --             }
    --           },
    --           "unauthAttr": {
    --             "1.3.6.1.4.1.311.3.3.1": {
    --               "name": "timestampToken",
    --               "der": "MIIXJwY ... QZej",
    --               "verify": {
    --                 "digestAlgorithms": [
    --                   "sha256"
    --                 ],
    --                 "signerInfo": [
    --                   {
    --                     "cert": {
    --                       "serialNumber": "0544AFF3949D0839A6BFDB3F5FE56116",
    --                       "issuerCN": "DigiCert Trusted G4 RSA4096 SHA256 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": "240117124047Z",
    --                     "messageDigest": "y6cKjJoRfgJwW+Dj29w3tEfWqVybz7Sg+d8opKQxCjM=",
    --                     "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": "240117124047Z"
    --                       },
    --                       "1.2.840.113549.1.9.16.2.12": {
    --                         "name": "signingCertificate",
    --                         "der": "MBowGDAWBBRm8CsywsLJD4JdzqqKycZPGZzPQA=="
    --                       },
    --                       "1.2.840.113549.1.9.4": {
    --                         "name": "messageDigest",
    --                         "digest": "y6cKjJoRfgJwW+Dj29w3tEfWqVybz7Sg+d8opKQxCjM="
    --                       },
    --                       "1.2.840.113549.1.9.16.2.47": {
    --                         "name": "signingCertificateV2",
    --                         "der": "MCYwJDAiBCDS9uRt7XQizNHUQFdoQTZvgoraVZquMxavTRqa1Ax4KA=="
    --                       }
    --                     }
    --                   }
    --                 ],
    --                 "uncommonOptions": "NO_SIGCERTV2_OID,NoSigningCertV2IssuerSerial"
    --               },
    --               "timestampSignatureVerified": true,
    --               "tstInfo": {
    --                 "tsaPolicyId": "2.16.840.1.114412.7.1",
    --                 "messageImprint": {
    --                   "hashAlg": "sha256",
    --                   "digest": "JqY7U+30qScMnRQwnDfUYEikZwOLHMhKX0oo5zo4ils=",
    --                   "digestMatches": true
    --                 },
    --                 "serialNumber": "6E4597E574BC909213565DAEBC0E4888",
    --                 "genTime": "20240117124047Z"
    --               }
    --             }
    --           }
    --         }
    --       ],
    --       "pkcs7": {
    --         "verify": {
    --           "certs": [
    --             {
    --               "issuerCN": "DigiCert Trusted G4 RSA4096 SHA256 TimeStamping CA",
    --               "serial": "0544AFF3949D0839A6BFDB3F5FE56116"
    --             },
    --             {
    --               "issuerCN": "DigiCert Trusted Root G4",
    --               "serial": "073637B724547CD847ACFD28662A5E5B"
    --             },
    --             {
    --               "issuerCN": "DigiCert Assured ID Root CA",
    --               "serial": "0E9B188EF9D02DE7EFDB50E20840185A"
    --             }
    --           ]
    --         }
    --       }
    --     }
    --   }
    -- }

    DECLARE @issuerCN nvarchar(4000)

    DECLARE @serial nvarchar(4000)

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

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

    -- Show the certificates embedded in the PKCS7 signature.

    PRINT 'Certificates contained in the PKCS7 signature:'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'pkcs7.verify.certs'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @issuerCN OUT, 'pkcs7.verify.certs[i].issuerCN'
        EXEC sp_OAMethod @json, 'StringOf', @serial OUT, 'pkcs7.verify.certs[i].serial'


        PRINT @issuerCN + ', ' + @serial
        SELECT @i = @i + 1
      END

    -- Show details about the signing certificate(s)
    DECLARE @numSigners int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numSigners OUT, 'pkcs7.verify.signerInfo'
    SELECT @i = 0
    WHILE @i < @numSigners
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i

        PRINT '---- Signing Certificate ----'

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].cert.serialNumber'
        PRINT 'serial number: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].cert.issuerCN'
        PRINT 'issuerCN: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].cert.digestAlgName'
        PRINT 'hash algorithm: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].signingAlgName'
        PRINT 'signing algorithm: ' + @sTmp0

        -- If this signature includes a timestamp token, get information about it.
        EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1"'
        IF @iTmp0 = 1
          BEGIN
            -- We're going to assume the timestamp token had only 1 signer..

            PRINT '--- Timestamp Token ----'

            EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1".verify.digestAlgorithms[0]'
            PRINT 'TS hash algorithm: ' + @sTmp0

            EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1".verify.signerInfo[0].cert.serialNumber'
            PRINT 'TS certificate serial: ' + @sTmp0

            EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1".verify.signerInfo[0].cert.issuerCN'
            PRINT 'TS certificate issuerCN: ' + @sTmp0

            EXEC sp_OAMethod @json, 'BoolOf', @iTmp0 OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1".timestampSignatureVerified'
            PRINT 'timestamp signature verified: ' + @iTmp0
            DECLARE @success int
            EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'pkcs7.verify.signerInfo[i].unauthAttr."1.3.6.1.4.1.311.3.3.1".tstInfo.genTime', 0, @genTime
            EXEC sp_OAMethod @dt, 'SetFromDtObj', @success OUT, @genTime

            EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, 1
            PRINT 'timestamp date/time: ' + @sTmp0
          END

        SELECT @i = @i + 1
      END


    PRINT 'The Authenticode signature is valid.'

    -- Sample output:

    -- Certificates contained in the PKCS7 signature:
    -- AAA Certificate Services, 48FC93B46055948D36A7C98A89D69416
    -- Sectigo Public Code Signing Root R46, 621D6D0C52019E3B9079152089211C0A
    -- Sectigo Public Code Signing CA R36, 3FF5B69109BFD4046C92CC0D18EE23C2
    -- ---- Signing Certificate ----
    -- serial number: 3FF5B69109BFD4046C92CC0D18EE23C2
    -- issuerCN: Sectigo Public Code Signing CA R36
    -- hash algorithm: SHA256
    -- signing algorithm: RSA-PKCSV-1_5
    -- --- Timestamp Token ----
    -- TS hash algorithm: sha256
    -- TS certificate serial: 0544AFF3949D0839A6BFDB3F5FE56116
    -- TS certificate issuerCN: DigiCert Trusted G4 RSA4096 SHA256 TimeStamping CA
    -- timestamp signature verified: True
    -- timestamp date/time: Wed, 17 Jan 2024 06:40:47 -0600
    -- The Authenticode signature is valid.

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @validator
    EXEC @hr = sp_OADestroy @genTime
    EXEC @hr = sp_OADestroy @dt


END
GO