SQLcl – enabling MCS for JDBC thin driver

Let’s say you have configured TLS authentication for your database users using the Microsoft Certificate Store (MCS) on Windows clients.

For OCI based client this is pretty well documented and understood:

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (TCPS)
WALLET_LOCATION = (SOURCE = (METHOD = MCS))
SSL_SERVER_DN_MATCH = yes

tnsnames.ora

DEV1_TLS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = ol7ora19dev1.spotonoracle.com)(PORT = 2484))
    (CONNECT_DATA =
      (SERVICE_NAME = DEV1.spotonoracle.com)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN = "cn=dev1,ou=servers,ou=oracle,dc=spotonoracle,dc=com")
    )
  )

With above configuration I can connect to the database using SQL*Plus.

C:\Users\user1>sqlplus /@dev1_tls

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 18:31:53 2019

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Wed May 29 2019 18:10:15 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Note that I use a 12.2 OCI client. This is because of bug 25809524 for which the fix has not made its way into the 18c release. I hope the soon to be released 19c client for Windows will have the fix.

In a previous post I’ve shown how to use a file based wallet (cwallet.sso) for JDBC thin applications,namely SQLcl. Unfortunately, we cannot simply change the WALLET_LOCATION parameter as we do in sqlnet.ora. This does not work:

set JAVA_TOOL_OPTIONS=-Doracle.net.wallet_location=(SOURCE=(METHOD=MCS)) -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=true

In order for JDBC thin applications to access the Microsoft Certificate Store we have to set Windows platform specific settings for trust store and key store properties:

set JAVA_TOOL_OPTIONS=-Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=true

C:\Users\user1>sql /@dev1_tls
Picked up JAVA_TOOL_OPTIONS: -Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS,KERBEROS5) -Doracle.net.ssl_server_dn_match=true

SQLcl: Release 19.1 Production on Wed May 29 18:45:21 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed May 29 2019 18:45:22 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

This works for SQLcl, SQL Developer, and any reasonable JDBC thin application. 🙂

3 thoughts on “SQLcl – enabling MCS for JDBC thin driver

  1. Mike

    Any idea how we might choose a certificate, say by alias, if there are multiple certificates in the Microsoft Store? Unlike OCI, JDBC thin does not prompt with a certificate chooser and seems to arbitrarily pick one.

    Reply
    1. son Post author

      Hi Mike,

      I think sqlcl and SQLDev use the default Java KeyManager and this selects the first cert that matches the requested trust chain (at least that’s what I read): https://stackoverflow.com/questions/42442721/java-keystore-programatically-select-the-certificate-to-use-from-keystore-file
      I’m not aware of any configuration in sqlcl that would allow you to provide a custom KeyManager.
      So I guess it is currently not possible to choose a certificate for JDBC thin (you may use jdbc:oci, though).

      Best regards,
      Beat

      Reply
      1. Mike

        I definitely prefer to use JDBC-OCI, but as you mentioned in this post, certain versions have bugs, so it is nice to have alternatives. FWIW, JDBC-OCI now works with the 19.16 client (with TLSv1.2).

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.