Monthly Archives: May 2019

SQLcl – enabling MCS for JDBC thin driver

3 Replies

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. 🙂