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