(SQL Server) Get the Server Certificate, Certificate Chain, and Root CA Certificate

Demonstrates how to get the HTTP server certificate, its certificate chain, and the root CA certificate.

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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- We're getting the SSL/TLS certificate, so make sure to connect to the SSL/TLS port (443).
    DECLARE @sslCert int
    EXEC sp_OAMethod @http, 'GetServerSslCert', @sslCert OUT, 'nationalgeographic.com', 443
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Note: Prior to v9.5.0.50, this method would fail if the certificate chain could not 
    -- be completed to the root.  Starting in v9.5.0.50, the incomplete certificate chain 
    -- will be returned.  The certificate chain's ReachesRoot property can be examined to 
    -- see if the chain was completed to the root.
    DECLARE @certChain int
    EXEC sp_OAMethod @sslCert, 'GetCertChain', @certChain OUT
    EXEC sp_OAGetProperty @sslCert, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @sslCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sslCert

        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @certChain, 'NumCerts', @numCerts OUT
    WHILE @i < @numCerts
      BEGIN
        DECLARE @cert int
        EXEC sp_OAMethod @certChain, 'GetCert', @cert OUT, @i


        EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
        PRINT 'SubjectDN ' + @i + ': ' + @sTmp0


        EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
        PRINT 'IssuerDN ' + @i + ': ' + @sTmp0
        EXEC @hr = sp_OADestroy @cert

        SELECT @i = @i + 1
      END

    -- If the certificate chain reaches the root CA cert, then the last cert in the chain
    -- is the root CA cert.
    EXEC sp_OAGetProperty @certChain, 'ReachesRoot', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        DECLARE @caCert int
        EXEC sp_OAMethod @certChain, 'GetCert', @caCert OUT, @numCerts - 1

        EXEC sp_OAGetProperty @caCert, 'SubjectDN', @sTmp0 OUT
        PRINT 'CA Root Cert: ' + @sTmp0
        EXEC @hr = sp_OADestroy @caCert

      END

    EXEC @hr = sp_OADestroy @sslCert

    EXEC @hr = sp_OADestroy @certChain


    EXEC @hr = sp_OADestroy @http


END
GO