SQL Server Requires Chilkat v11.0.0+
SQL Server
Validate Certificate using OCSP Protocol
See more Certificates Examples
Demonstrates how to validate a certificate (check the revoked status) using the OCSP protocol.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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/google.crt'
IF @success = 0
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
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @json int
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
EXEC @hr = sp_OACreate 'Chilkat.BinData', @ocspRequest OUT
DECLARE @http int
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpBd', @success OUT, 'POST', @ocspUrl, @ocspRequest, 'application/ocsp-request', @resp
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
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Get the binary (ASN.1) OCSP reply
DECLARE @ocspReply int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @ocspReply OUT
EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @ocspReply
-- Convert the binary reply to JSON.
-- Also returns the overall OCSP response status.
DECLARE @jsonReply int
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 @resp
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 @resp
EXEC @hr = sp_OADestroy @ocspReply
EXEC @hr = sp_OADestroy @jsonReply
END
GO