Connect to Oracle Autonomous Database Using SQL Developer Through SSH Tunnel

Oracle Autonomous Database is the world’s first autonomous data management platform in the cloud that delivers automated patching, upgrades, and tuning—including performing all routine database maintenance tasks while the system is running—without human intervention. It is self-driving, self-securing, and self-repairing using artificial intelligence and machine learning technologies, which help organisations save costs, lower risks and drive innovations. Businesses like National Pharmacies, Applied Precision Medicine, Hertz and so on have experienced tremendous benefits from it. Currently, Oracle offers two flavours of Autonomous Database: Autonomous Data Warehouse (ADW) for data mart or analytical workloads and Autonomous Transaction Processing (ATP) for OLTP and mixed workloads.

As Oracle Autonomous Database runs in the public cloud, certain network configurations need to be set up in order for you to connect to it from corporate networks. For example, one can use HTTP Proxy or Oracle FastConnect to connect to Oracle Autonomous Database today. If the traffic is actually initiated from an OCI compute instance or an Oracle PaaS instance, one can connect to Oracle Autonomous Database privately via Oracle Service Gateway. Very soon you will also be able to connect to it via IPSec VPN.

This article, however, addresses one scenario where a database administrator or data scientist needs to connect to an Oracle Autonomous Database from a corporate network that doesn't have the aforementioned facilities. Can s/he connect to ADW/ATP with Oracle SQL Developer through SSH tunnel?

The short answer is YES. Let's take a look at two different corporate network scenarios:

a) Outbound SSH traffic to Internet is allowed from corporate network

b) Outbound SSH traffic is only allowed to reach a DMZ host which can forward any traffic to Internet

In both scenarios, you can set up a SSH tunnel to either a cloud bastion host or DMZ host to forward the network traffic to Oracle Autonomous Database.

Prerequisites:

To start with, you need a Linux server that can be used for port forwarding which:

• sits either in a DMZ network or in the cloud as a bastion host, and
• runs sshd service and can be reached on port 22 from your client machine

Then, of course, you need an Oracle Autonomous Database instance running. If you don't have one, you can get a free trial account from here. You need to download the client credentials zip file from the OCI service console.

For the purpose of this exercise, you need Oracle SQL Developer (ideally 18.4 or higher) installed on a client machine. 

Step one: Set up SSH Tunnel

First, we need a SSH tunnel between your client machine and the bastion host. From the Mac OS/Linux client machine, you can set up the tunnel by running 'ssh -L 1521:your_adb_host:1522 -N osuser@your_ssh_server -i your_private_key'. You can use Putty to set up the SSH tunnel on Windows as mentioned here.

Kurts-MacBook-Pro-2:Downloads kurtliu$ ssh -L 1521:adb.us-ashburn-1.oraclecloud.com:1522 -N opc@129.146.xxx.xxx -i mykeyfile
Last login: Thu May  2 03:42:22 2019 from xxx.xxx.xxx.xxx
[opc@myords01 ~]$

-L allows you to set up a local port forwarding from any local port (e.g. 1521) to the remote port (e.g. 1522) on the remote host (e.g. adb.us-ashburn-1.oraclecloud.com). You may replace us-ashburn-1 with us-phoenix-1 or other region name if your ADW/ATP instance runs in a different region.

-N is optional, which means no further commands can be run on this SSH session but the tunnel.

opc is the os user on your SSH server (129.146.xxx.xxx) that you use for port forwarding.

-i is needed if you use SSH key pair to authenticate to your SSH server for osuser user. (You may want to add passphrase to protect your key pair.)

 Once the SSH session is connected, the tunnel is up and we can move to the next step.

Step Two: Prepare for TNSNAMES.ORA

Normally, when we create a SQL Developer connection to an autonomous database, we choose either Cloud Wallet or Cloud PDB as connection type depending on the SQL Developer version you use and then browse to the client credentials zip file we downloaded before. However, as we use tunnel, we have to unzip the client credentials zip file and edit the tnsnames.ora file because we have new hostname and TCPS port number to use.

In this example, the client credentials zip file was decompressed into a directory called /Users/kurtliu/Downloads/Wallet_ATPLab01, which will be referenced in the next step. I added a new TNS alias in the tnsnames.ora file which can be found in the wallet directory by copy/paste an existing TNS alias and replacing the values for host and port with localhost and 1521.

Original connect string:

myadw_low = (description=(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com)) (connect_data=(service_name=...))   )

New connect string:

myadw_low_l = (description=(address=(protocol=tcps)(port=1521)(host=localhost)) (connect_data=(service_name=...))   )

Save the changes and we are ready for the last step.

Step Three: Create a Connection in SQL Developer

As mentioned above, we don't need to use Cloud Wallet/Cloud PDB connection type. Instead, I used Custom JDBC (it was called Advanced prior to SQL Developer 19.1) connection type when creating a SQL Developer connection. The key question is how to write the JDBC URL here?

If we follow the traditional way of writing JDBC thin URL like:

jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1521)(host=localhost))(connect_data=(service_name=...))   )

We will end up getting the following error. This is because SQL Developer doesn't know where to find the wallet file by default.

Status : Failure -Test failed: IO Error: IO Error General SSLEngine problem, Authentication lapse 0 ms.

While you could specify the wallet location by adding a JVM Option in sqldeveloper.conf (on Windows or Linux) or product.conf (on Mac OS) as mentioned here, there is a much easier way to fix this problem with latest version of SQL Developer.

Simply put the TNS alias that we modified in step 2 and the TNS_ADMIN variable in the JDBC URL as follow.

jdbc:oracle:thin:@myadw_low_1?TNS_ADMIN=/Users/kurtliu/Downloads/Wallet_ATPLab01

Please note having TNS_ADMIN in the JDBC Thin URL is a new feature of Oracle JDBC 18.3, which has been included in new version of Oracle SQL Developer. It allows us to tell SQL Developer where the client credentials wallet and SQL*Net files are. I tested the same with SQL Developer 18.4 and 19.1 and both worked perfectly.

That's all and you can give it a go.

As a bottom line, you can use loads of tools and drivers to connect to Oracle Autonomous Database JDBC thin/thick drivers, ODBC, Python (cx_Oracle), ROracle, Node.js, the list goes on. Enjoy your Autonomous Journey.

Disclaimer: The views expressed in this article are my own and do not necessarily reflect the views of Oracle. While SSH tunnel can help you avoid certain firewall restrictions, you may want to consult with your security team before using it.