I don’t think this is documented anywhere (and no hits on google) but it shows once again how awsome SQL Developer is. 🙂
Instead of using “jdbc:oracle:oci” for TLS enabled connections we’re going to set SQL Developer up to do just that with “jdbc:oracle:thin”. In that regard, SQL Developer is no more special than any Java application.
To keep it short I’m sticking to the basics, maybe I’ll add some variations into the comments later.
First, we need to add “OraclePKIProvider” to the JRE security provider’s list. The provider’s list is defined in “$JAVA_HOME/jre/lib/security/java.security”.
I’m going to append to it by creating a new file that I only source from SQL Developer. In my case, the next free slot is 10. Depending on your OS and JRE version the list may slightly vary.
(also make sure that the java.security file is appendable: “security.overridePropertiesFile=true”)
$ vi /opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security security.provider.10=oracle.security.pki.OraclePKIProvider
Next, we add a few options to the sqldeveloper.conf file:
$ vi /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf # add this to end of the config file # specify the path to your SSO wallet file AddVMOption -Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/btr/tns/wallet)))" # this is required if you want to use TLS authentication next to in-flight traffic encryption AddVMOption -Doracle.net.authentication_services="(TCPS)" # optionally, if you want the client to verify the server certificate AddVMOption -Doracle.net.ssl_server_dn_match=true # append/override JRE security config to add Oracle provider AddVMOption -Djava.security.properties=/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security # even SQLDev 18.1 ships with a very old version that is not compatible - just download the latest one from OTN AddJavaLibFile /home/btr/orasec/jlib/oraclepki.jar
That’s it, we’re done.
Now you can create connections using TNS, LDAP, or Advanced. Basic/EZCONNECT does not support to specify the protocol, so it can’t be used for TLS.
Here we only use TLS for in-flight traffic encryption:
And here, we make use of TLS user authentication:
Pingback: SQLcl – enabling TLS for JDBC thin driver | Spot on Oracle
Hi,
How to create a wallet in this location if we are using only JDBC client and no oracle client.
Hi Saravana,
You can create the wallet (p12) on any machine where you have an Oracle client (or database) installation and copy it to your machine for use. This also works for the .sso wallet created with just “-auto_login” (although I highly discourage the use of such SSO wallets -> always use “-auto_login_local). The purpose of “-auto_login_local” is that you cannot copy it to other machines for use there, you have to create the auto-login-local SSO file on the target machine.
To create a client-side wallet you best use “orapki” (also to create TLS keys and cert requests). To add credentials to use the Secure External Password Store feature you additionally need “mkstore”. A few years ago I had the same need and wanted to just “install” orapki and mkstore on a machine. This is how it works for 12.1 (for 18c/19c it might still be the same, just follow the references in the “orapki” and “mkstore” files). In any case you need to install a client (or database) home somewhere in order to copy the necessary files. Luckily, orapki and mkstore both are Java programs. So you only need a JVM on your target machine.
# set Java env
export JAVA_HOME=/usr/java/jdk1.8.0_51
# setup directory structure
TOOL_BASE=/home/btr/orasec
# create directory structure
mkdir -p $TOOL_BASE
mkdir $TOOL_BASE/bin
mkdir $TOOL_BASE/jlib
# copy following jar files from a DB server (or client installation) to the target machine $TOOL_BASE/jlib directory
$ORACLE_HOME/jlib/oraclepki.jar
$ORACLE_HOME/jlib/cryptoj_5_0.jar
$ORACLE_HOME/jlib/osdt_core.jar
$ORACLE_HOME/jlib/osdt_cert.jar
$ORACLE_HOME/jlib/ojmisc.jar
# copy orapki and mkstore from a DB server (or client installation) to the target $TOOL_BASE/bin directory
$ORACLE_HOME/bin/orapki
$ORACLE_HOME/bin/mkstore
Now you can create wallets and manage all content.
Hope this works for you, too.
Hi,
Thanks for quick response. My client machine is windows where SQL developer is installed. I will try the steps you have mentioned above.
Thanks lot.
Hi,
I am not sure whether you can help further. I have managed to install oracle client (windows) where SQL developer is installed. Also created wallets on both sides( db and client) and exchanged the certificates by adding into wallet. Then I am able to connect from Client’s SQL Plus using TCPS. Then I have tried to connect DB using the TNS option from SQL developer, but looks like still it is uses JDBC. I have configured as you mentioned, looks like it didn’t recognise the wallet path, and I got the following error:
Status : Failure -Test failed: encountered a problem with the Secret Store. Check the wallet location
for the presence of an open wallet (cwallet.sso) and ensure that this wallet contains the correct credentials using
the mkstore utility: java.io.IOException: Unsupported WRL type”(SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=D:\Users\Saravana\Software\Oracle\wallet)))”
I am not using secret key, so I didn’t use mkstore. Please see the below config
# Saravana – added lines
AddVMOption -Doracle.net.wallet_location=”(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\Users\Saravana\Software\Oracle\wallet)))”
# this is required if you want to use TLS authentication next to in-flight traffic encryption
AddVMOption -Doracle.net.authentication_services=”(TCPS)”
# optionally, if you want the client to verify the server certificate
AddVMOption -Doracle.net.ssl_server_dn_match=true
# append/override JRE security config to add Oracle provider
AddVMOption -Djava.security.properties=D:\Users\Saravana\Software\sqldeveloper-18.1.0.095.1630-x64\sqldeveloper\sqldeveloper\bin\sqldev.java.security
# even SQLDev 18.1 ships with a very old version that is not compatible – just download the latest one from OTN
AddJavaLibFile D:\Users\Saravana\Software\sqldeveloper-18.1.0.095.1630-x64\sqldeveloper\sqldeveloper\lib\oraclepki.jar
I am having the exact same problem, and when I search google for “Unsupported WRL type” I get this page and a page listing strings found in oraclepki.jar. How frustrating.
Saravana, Were you able to connect DB using SQL Developer with TLS authentication?
If Yes, Please share the steps. I’m stuck here on how to add client wallet and certs in SQL Developer home?
Thanks,
Ravi
Great Article