How to connect to Oracle Autonomous Database using mTLS in SQL Developer

Introduction

I previously wrote a post on How to connect to TLS-enabled Oracle DB using SQL Developer, and I am pleased to see that people have found it to be a simple and useful resource. Now that Oracle Cloud is getting more and more popular and the Autonomous DB free tier is available to try, my love for Oracle is reignited. I am writing this post regarding how to connect to the autonomous database using SQL Developer.

Create Autonomous Database

The first resource I provisioned after enabling my OCI account was Oracle Autonomous Database for transaction processing. I am using ClickOps to create Autonomous Database Instance.

This section is worthless if you already have an Autonomous Database that you want to connect and you may proceed to the next section

With the Free tier, you don’t have many selections to make, and below are the options I selected:

Notice mTLS authentication is by default configured for free Tier Autonomous Database.

Oracle Autonomous Database comes with fancy “Database Actions” page with all sorts of available actions.

SQL Developer Web is one of those, but it is not fully featured like SQL Developer. So, how do you connect to Autonomous Database using SQL Developer installed on your Laptop?

Connect using SQL Developer

Download the wallet

Navigate to the “Database connection” tab for your instance in the OCI web console

The wallet type we selected is the Instance wallet. That means the wallet will be specific to this Instance. Another type is “regional” which is not instance specific and it contains wild card certs and TNS entries for all ADB instances in a region.

To download the credential wallet (keystore), you need to provide a password on the prompt. This is your wallet/keystore password.

The zip will contain the keystore/certstore/wallet in p12, pem, and jks format. This also contains the sqlnet.ora and tnsnames.ora. This makes it so easy to use the TLS/mTLS since you have all the files in all popular formats.

There are two main methods to connect to Autonomous Database

Using Oracle Autonomous Database Serverless

Method 1

• Start SQL Developer
• Create a new connection
• Fill “admin” as the user and its password
• Select the connection type as “cloud wallet” and select the downloaded zip having the wallet files.

method1 success

• Test and Save.
• This method is simple and doesn’t need any additional configuration.
• You can have as many databases configured in SQL Developer and wouldn’t need any changes to the keystores. Keystores are separate for each Instance ( when we select wallet type “Instance wallet”)

Method 2

This is more like what I did in my last post except this time we are using mTLS not TLS.

How to connect to TLS-enabled Oracle DB using SQL Developer

So what changes?

• Since SQL Developer is a Java application, You can use JKS for TLS connections to the Database
• Extract the downloaded zip having the wallet files.
• Find the sqldeveloper.conf file and append the below lines to it.

AddVMOption -Djavax.net.ssl.trustStore=C:\Users\harin\Downloads\Wallet_onemoreadb1\truststore.jks
AddVMOption -Djavax.net.ssl.trustStoreType=JKS
AddVMOption -Djavax.net.ssl.trustStorePassword=<walletpass>
AddVMOption -Djavax.net.ssl.keyStore=C:\Users\harin\Downloads\Wallet_onemoreadb1\keystore.jks
AddVMOption -Djavax.net.ssl.keyStorePassword=<walletpass>

• Notice that this time we are also setting -Djavax.net.ssl.keyStore and -Djavax.net.ssl.keyStorePassword as we are using mTLS.
• Restart SQL Developer
• Create a new connection
• Fill “admin” as the user and its password
• Select the connection type as “Custom JDBC”.
• Navigate to the Database connection tab for your instance in the OCI web console and copy the connection string

• Make it JDBC compatible like below

jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ca-toronto.oraclecloud.com))(connect_data=(service_name=g8BALALALABABABa_onemoreadb1_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

• Copy this into the custom JDBC URL text box

method2 success

Conclusion

Method1 is way easier and more convenient and scales well for SQL Developer connections. This method is simple and doesn’t need any additional configuration.

Method 2 steps can be used by any Java application for mTLS connections to Oracle Autonomous Database. That is a big advantage.

The main disadvantage of method 2 is that you will have to maintain one keystore and one trust store which means you will have to manually add the trusted certs and server certs to the JKS keystore/truststore each time you add a new Instance (unless you are using the wallet type “regional wallet”).

Please read my other articles as well and share your feedback. If you like the content shared please like, comment, and subscribe for new articles.