Configure Oracle SQL Developer to Connect to Database Cloud Service Using SSH

In the previous blog post, I have shown on how to create a new Oracle Database Cloud Service (DBCS) using Oracle Cloud Infrastructure Classic environment.

In this blog post, I will show on how to configure Oracle SQL Developer tool to get connected to the DBCS service in the cloud.

By default the DBCS listener port (default value is 1521) is not exposed to the outside world so tools like SQL Developer cannot be used to get connected to the Database Cloud Service.

So how do we get connected to the Database to perform the usual basic activities such as creating tables etc?

SSH Tunnel

We have to use Secure Shell (SSH) tunnel using the authorised key and perform SSH port forwarding.  This is a process whereby a TCP/IP connection that would otherwise be insecure is tunnelled through a secure SSH link.

Oracle SQL Developer Tool allows us to configure a SSH Host with port forwarding option.

The configuration is shown in the following screenshots.

Step 1 - View SSH Hosts Window 

By default SSH Host windows is not visible.  In case if it is not visible then select the SSH option under the View menu as shown below.

The SSH Hosts window should be visible on the left side of SQL Developer Window as shown below.

Step 2 - Create New SSH Host

Right Click "SSH Hosts" and select "New SSH Host..." option as shown in the below screenshot.

Specify a name for the SSH Host.  Specify the public IP Address of the DBCS service.  This can be found from the Service Console page of the DBCS service.  Specify the username as "oracle".  

Check "Use Key File" and select the Private key pair of the Public key which was used at the time of creating the DBCS service. 

Check "Add a Local Port Forward" and specify the local port number.

See the below screenshot for a sample.

Step 3 - Test SSH Tunnel Connection

Once the above details are saved then do right click the newly created SSH host and select the Test option.

If the details are correct then the Test should succeed as shown below.

Please make sure to disconnect the SSH Host i.e., right click and select "Disconnect" otherwise errors might appear when the Connection is tested in Step 4.

Step 4 - Create Database Connection Using SSH Tunnel

Now create a new Connection to get connected to the DBCS service using the SSH Host created in the previous step.  

In the new DB Connection window, enter a name for the connection and specify the DB schema username and password.  For the current example, SYS account is used.

From the Connection Type drop down list, choose SSH and from the Port Forward dropdown list choose the SSH Host created in the previous step.

Enter the Service Name for the DBCS service.  This information can be found from the DBCS Service Console page which is mentioned as "Connect String".  Include all the details after the "host:port/" which will start with the PDB name e.g., PDB1.<identity-domain>.oraclecloud.internal

Save the details and Test the connection.  If the details are correct then this should succeed.  If it fails then please check whether you are behind any firewall blocking the SSH connection or you forgot to disconnect the SSH Host after the Test in Step 3.

Successful Test indicates that we can connect from SQL Developer to a Database Service in the cloud.

Please do refer the official Oracle documentation for further details.