SQL Server

Trust Specific Root CA Certificates

See more Certificates Examples

Demonstrates how to trust specific root CA certificates and none others. 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
    -- 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.

    -- This example will trust the Amazon root CA certificates provided at 
    -- https://www.amazontrust.com/repository/

    -- I've previously downloaded the root CA certificates to DER format.
    -- Add each to the Chilkat TrustedRoots singleton object.

    DECLARE @tRoots int
    EXEC @hr = sp_OACreate 'Chilkat.TrustedRoots', @tRoots OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    EXEC sp_OAMethod @caCert, 'LoadFromFile', @success OUT, 'qa_data/certs/aws_root_ca/AmazonRootCA1.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @caCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tRoots
        EXEC @hr = sp_OADestroy @caCert
        RETURN
      END
    EXEC sp_OAMethod @tRoots, 'AddCert', @success OUT, @caCert

    -- Continue with the others.
    -- For brevity, we're not checking return values for success/failure.
    EXEC sp_OAMethod @caCert, 'LoadFromFile', @success OUT, 'qa_data/certs/aws_root_ca/AmazonRootCA2.cer'
    EXEC sp_OAMethod @tRoots, 'AddCert', @success OUT, @caCert

    EXEC sp_OAMethod @caCert, 'LoadFromFile', @success OUT, 'qa_data/certs/aws_root_ca/AmazonRootCA3.cer'
    EXEC sp_OAMethod @tRoots, 'AddCert', @success OUT, @caCert

    EXEC sp_OAMethod @caCert, 'LoadFromFile', @success OUT, 'qa_data/certs/aws_root_ca/AmazonRootCA4.cer'
    EXEC sp_OAMethod @tRoots, 'AddCert', @success OUT, @caCert

    EXEC sp_OAMethod @caCert, 'LoadFromFile', @success OUT, 'qa_data/certs/aws_root_ca/SFSRootCAG2.cer'
    EXEC sp_OAMethod @tRoots, 'AddCert', @success OUT, @caCert

    -- Indicate we don't want to automatically trust the operating system's installed root CA certificates.
    -- On a Windows operating system, this would be the registry-based CA certificate stores. 
    -- On a Linux system, this could be /etc/ssl/certs/ca-certificates.crt, if it exists.
    EXEC sp_OASetProperty @tRoots, 'TrustSystemCaRoots', 0

    -- Activate the trusted roots object.
    -- Once activated, all Chilkat objects that use TLS connections (HTTP, REST, Socket, MailMan, IMAP, FTP, etc.)
    -- will fail the TLS handshake if the server certificate is not verified and rooted with one of our explicitly trusted root certificates.
    EXEC sp_OAMethod @tRoots, 'Activate', @success OUT

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Note: We also need to explicitly indicate that server certificates are to be verified.
    EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1

    -- For example, the following should fail because www.chilkatsoft.com's server certificate is not rooted in one of the explicitly trusted root CA certs.
    EXEC sp_OAMethod @http, 'Download', @success OUT, 'https://www.chilkatsoft.com/helloWorld.txt', 'qa_output/helloWorld.txt'
    IF @success <> 1
      BEGIN
        -- The above Download should fail.
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        -- There should be a message in the LastErrorText indicating that we were "Unable to build certificate chain to root.."
      END

    -- However, we should be able to make TLS connections to good.sca1a.amazontrust.com
    EXEC sp_OAMethod @http, 'Download', @success OUT, 'https://good.sca1a.amazontrust.com/', 'qa_output/valid.html'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tRoots
        EXEC @hr = sp_OADestroy @caCert
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- We can still examine the LastErrorText and we'll find this message within:  
    -- "The public key was successfully validated against the public key of the explicitly trusted root cert."
    EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0


    PRINT 'Success!'

    EXEC @hr = sp_OADestroy @tRoots
    EXEC @hr = sp_OADestroy @caCert
    EXEC @hr = sp_OADestroy @http


END
GO