(SQL Server) Download and Trust the DigiCert Global Root CA

Demonstrates how to download a root certificate and trust it.

For more information, see https://www.digicert.com/digicert-root-certificates.htm

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- In this example, the URLs for the DigiCert root CA certs are available at this web page:
    -- https://www.digicert.com/digicert-root-certificates.htm

    -- This example downloads the "DigiCert Global Root G3"
    -- Valid until: 15/Jan/2038
    -- Serial #: 05:55:56:BC:F2:5E:A4:35:35:C3:A4:0F:D5:AB:45:72
    -- Thumbprint: 7E04DE896A3E666D00E687D33FFAD93BE83D349E

    DECLARE @certUrl nvarchar(4000)
    SELECT @certUrl = 'https://dl.cacerts.digicert.com/DigiCertGlobalRootG3.crt'

    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

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

    DECLARE @success int
    EXEC sp_OAMethod @http, 'DownloadBd', @success OUT, @certUrl, @bdCert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @bdCert
        RETURN
      END

    -- Load it into a Chilkat cert object.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromBd', @success OUT, @bdCert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @bdCert
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Examine the common name,serial, and thumbprint:

    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
    PRINT 'CN: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
    PRINT 'Serial: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'Sha1Thumbprint', @sTmp0 OUT
    PRINT 'Thumbprint: ' + @sTmp0

    -- Output from the above:
    -- CN: DigiCert Global Root G3
    -- Serial: 055556BCF25EA43535C3A40FD5AB4572
    -- Thumbprint: 7E04DE896A3E666D00E687D33FFAD93BE83D349E

    -- If desired, the certificate can be saved to a local file so it does not need
    -- to be downloaded from the website every time.  
    DECLARE @certPath nvarchar(4000)
    SELECT @certPath = 'qa_data/certs/DigiCertGlobalRootG3.crt'
    EXEC sp_OAMethod @bdCert, 'WriteFile', @success OUT, @certPath

    -- To load the cert from a file...
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, @certPath

    -- Do the following to add the cert to the collection of trusted roots
    -- for this application.  (Note:  The trust is not persisted.  Each time the
    -- application runs, it should load the cert (from whatever source, whether it is
    -- a file, a database,etc.) and do the following:
    DECLARE @troots int
    -- Use "Chilkat_9_5_0.TrustedRoots" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.TrustedRoots', @troots OUT

    EXEC sp_OAMethod @troots, 'AddCert', @success OUT, @cert
    EXEC sp_OAMethod @troots, 'Activate', @success OUT

    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT

    PRINT @sTmp0 + ' is now trusted for this run of this application.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @bdCert
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @troots


END
GO