SQL Server
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 SQL Server Downloads
-- 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
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
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
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @genTime OUT
DECLARE @dt int
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