SQL Server

Get the Server Certificate, Certificate Chain, and Root CA Certificate

See more HTTP Examples

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

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    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 @hr = sp_OACreate 'Chilkat.Cert', @sslCert OUT

    EXEC sp_OAMethod @http, 'GetServerCert', @success OUT, 'apple.com', 443, @sslCert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sslCert
        RETURN
      END

    DECLARE @certChain int
    EXEC @hr = sp_OACreate 'Chilkat.CertChain', @certChain OUT

    EXEC sp_OAMethod @sslCert, 'BuildCertChain', @success OUT, @certChain
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sslCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sslCert
        EXEC @hr = sp_OADestroy @certChain
        RETURN
      END

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

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


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


        EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
        PRINT 'IssuerDN ' + @i + ': ' + @sTmp0
        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 @hr = sp_OACreate 'Chilkat.Cert', @caCert OUT

        EXEC sp_OAMethod @certChain, 'CertAt', @success OUT, @numCerts - 1, @caCert

        EXEC sp_OAGetProperty @caCert, 'SubjectDN', @sTmp0 OUT
        PRINT 'CA Root Cert: ' + @sTmp0
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sslCert
    EXEC @hr = sp_OADestroy @certChain
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @caCert


END
GO