(SQL Server) Load Certs from Java KeyStore into Trusted CA Roots

Demonstrates how to load a Java KeyStore containing CA root certificates that are to be trusted by the application. This can be done once at the beginning of an application, and then the trusted roots can be activated so that only these root CA certs are trusted by the application for any TLS connections.

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.

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

    EXEC sp_OASetProperty @jks, 'VerboseLogging', 1

    DECLARE @password nvarchar(4000)
    SELECT @password = 'myPassword'
    DECLARE @success int
    EXEC sp_OAMethod @jks, 'LoadFile', @success OUT, @password, 'qa_data/jks/entrust_caCerts.jks'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @jks, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END

    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_OASetProperty @troots, 'VerboseLogging', 1

    EXEC sp_OAMethod @troots, 'AddJavaKeyStore', @success OUT, @jks
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @troots, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jks
        EXEC @hr = sp_OADestroy @troots
        RETURN
      END

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @troots, 'NumCerts', @numCerts OUT
    WHILE (@i < @numCerts)
      BEGIN
        DECLARE @cacert int
        EXEC sp_OAMethod @troots, 'GetCert', @cacert OUT, @i

        EXEC sp_OAGetProperty @cacert, 'SubjectDN', @sTmp0 OUT
        PRINT @i + ': ' + @sTmp0
        EXEC @hr = sp_OADestroy @cacert

        SELECT @i = @i + 1
      END

    -- Activate this specific set of trusted roots.
    EXEC sp_OAMethod @troots, 'Activate', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @troots, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jks
        EXEC @hr = sp_OADestroy @troots
        RETURN
      END

    -- Output:

    -- 0: C=US, O=Entrust.net, OU=www.entrust.net/CPS incorp. by ref. (limits liab.), OU=(c) 1999 Entrust.net Limited, CN=Entrust.net Secure Server Certification Authority
    -- 1: O=Entrust.net, OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.), OU=(c) 1999 Entrust.net Limited, CN=Entrust.net Certification Authority (2048)
    -- 2: C=US, O="Entrust, Inc.", OU=www.entrust.net/CPS is incorporated by reference, OU="(c) 2006 Entrust, Inc.", CN=Entrust Root Certification Authority

    EXEC @hr = sp_OADestroy @jks
    EXEC @hr = sp_OADestroy @troots


END
GO