(SQL Server) Validate Certificate using OCSP Protocol

Demonstrates how to validate a certificate (check the revoked status) using the OCSP protocol.

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.

    -- This example will check the revoked status of a certificate loaded from a file.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/google.crt'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Get the cert's OCSP URL.
    DECLARE @ocspUrl nvarchar(4000)
    EXEC sp_OAGetProperty @cert, 'OcspUrl', @ocspUrl OUT

    -- Build the JSON that will be the OCSP request.

    -- Possible hash algorithms are sha1, sha256, sha384, sha512.  
    DECLARE @hashAlg nvarchar(4000)
    SELECT @hashAlg = 'sha256'
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    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
    -- Read more about OCSP nonce lengths
    EXEC sp_OAMethod @prng, 'GenRandom', @sTmp0 OUT, 16, 'base64'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'extensions.ocspNonce', @sTmp0
    EXEC sp_OASetProperty @json, 'I', 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'request[i].cert.hashAlg', @hashAlg
    EXEC sp_OAMethod @cert, 'HashOf', @sTmp0 OUT, 'IssuerDN', @hashAlg, 'base64'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'request[i].cert.issuerNameHash', @sTmp0
    EXEC sp_OAMethod @cert, 'HashOf', @sTmp0 OUT, 'IssuerPublicKey', @hashAlg, 'base64'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'request[i].cert.issuerKeyHash', @sTmp0
    EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'request[i].cert.serialNumber', @sTmp0

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Our OCSP request looks something like this:
    -- {
    --   "extensions": {
    --     "ocspNonce": "qZDfbpO+nUxRzz6c/SPjE5QCAsPfpkQlRDxTnGl0gnxt7iXO"
    --   },
    --   "request": [
    --     {
    --       "cert": {
    --         "hashAlg": "sha1",
    --         "issuerNameHash": "9u2wY2IygZo19o11oJ0CShGqbK0=",
    --         "issuerKeyHash": "d8K4UJpndnaxLcKG0IOgfqZ+uks=",
    --         "serialNumber": "6175535D87BF94B6"
    --       }
    --     }
    --   ]
    -- }

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

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

    -- Convert our JSON to a binary (ASN.1) OCSP request
    EXEC sp_OAMethod @http, 'CreateOcspRequest', @success OUT, @json, @ocspRequest
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @ocspRequest
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Send the OCSP request to the OCSP server
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PBinaryBd', @resp OUT, 'POST', @ocspUrl, @ocspRequest, 'application/ocsp-request', 0, 0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @ocspRequest
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Get the binary (ASN.1) OCSP reply
    DECLARE @ocspReply int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @ocspReply OUT

    EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @ocspReply
    EXEC @hr = sp_OADestroy @resp

    -- Convert the binary reply to JSON.
    -- Also returns the overall OCSP response status.
    DECLARE @jsonReply int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonReply OUT

    DECLARE @ocspStatus int
    EXEC sp_OAMethod @http, 'ParseOcspReply', @ocspStatus OUT, @ocspReply, @jsonReply

    -- The ocspStatus can have one of these values:
    -- -1:  The ARG1 does not contain a valid OCSP reply.
    -- 0:  Successful - Response has valid confirmations..
    -- 1: Malformed request - Illegal confirmation request.
    -- 2: Internal error - Internal error in issuer.
    -- 3: Try later -  Try again later.
    -- 4: Not used - This value is never returned.
    -- 5: Sig required - Must sign the request.
    -- 6: Unauthorized - Request unauthorized.

    IF @ocspStatus < 0
      BEGIN

        PRINT 'Invalid OCSP reply.'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @ocspRequest
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @ocspReply
        EXEC @hr = sp_OADestroy @jsonReply
        RETURN
      END


    PRINT 'Overall OCSP Response Status: ' + @ocspStatus

    -- Let's examine the OCSP response (in JSON).
    EXEC sp_OASetProperty @jsonReply, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonReply, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The JSON reply looks like this:
    -- (Use the online tool at https://tools.chilkat.io/jsonParse.cshtml
    -- to generate JSON parsing code.)

    -- {
    --   "responseStatus": 0,
    --   "responseTypeOid": "1.3.6.1.5.5.7.48.1.1",
    --   "responseTypeName": "ocspBasic",
    --   "response": {
    --     "responderIdChoice": "KeyHash",
    --     "responderKeyHash": "d8K4UJpndnaxLcKG0IOgfqZ+uks=",
    --     "dateTime": "20180803193937Z",
    --     "cert": [
    --       {
    --         "hashOid": "1.3.14.3.2.26",
    --         "hashAlg": "SHA-1",
    --         "issuerNameHash": "9u2wY2IygZo19o11oJ0CShGqbK0=",
    --         "issuerKeyHash": "d8K4UJpndnaxLcKG0IOgfqZ+uks=",
    --         "serialNumber": "6175535D87BF94B6",
    --         "status": 0,
    --         "thisUpdate": "20180803193937Z",
    --         "nextUpdate": "20180810193937Z"
    --       }
    --     ]
    --   }
    -- }
    -- 

    -- The certificate status:
    DECLARE @certStatus int
    SELECT @certStatus = -1
    EXEC sp_OAMethod @jsonReply, 'HasMember', @iTmp0 OUT, 'response.cert[0].status'
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @jsonReply, 'IntOf', @certStatus OUT, 'response.cert[0].status'
      END

    -- Possible certStatus values are:
    -- -1: No status returned.
    -- 0: Good
    -- 1: Revoked
    -- 2: Unknown.

    PRINT 'Certificate Status: ' + @certStatus

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @ocspRequest
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @ocspReply
    EXEC @hr = sp_OADestroy @jsonReply


END
GO