Establish SSH Connection to Oracle Database Cloud Service with SQL Developer

A couple of days ago, I showed how to Configuring the Oracle Database Cloud Service, and finished up with promising to help you connect to this database with SQL Developer.

As part of the example, you are required to create an SSH key. We are going to use this to enable SQL Developer connectivity to the cloud database service. The assumption here is that you have provisioned your database instance, created a your ssh public/private key pair, and have opened the appropriate port(s) to the database. If you haven’t click the link above, and I’ll walk you through the steps.

1. Launch SQL Developer and choose SSH under the View Toolbar menu item.

2. Right-click SSH Hosts and select the New SSH Host…

3. Complete the dialog box with the appropriate information. As a note, your Host (database ip address) can be found on the Services Overview page from the Dashboard. Click OK.

4. Right click the SSH Host you just created, select New Local Port Forward, and complete the dialog box.

Leave Host as localhost and Port should be remain 1521.

Click OK

This process creates the Port Forward name for the SQL Developer SSH Connection type. Now let’s open and configure a new database Connection.

5. Click the down arrow next to the green plus in the Connections window

6. Complete the New Connections Dialog box. Make sure you use the password you established during the Database instance setup. You can decide whether you want to Save Password. Note that the user name is SYS and you must select SYSDBA as the Role.

Select SSH as your connection type.

7. Once you select SSH for your Connection Type, the Hostname and Port boxes will be replaced by simply the Port Forward dropdown.

Select the port you created back in step 4 from this dropdown and enter the SID, which in this case will be orcl. If you find that both SID and Service name are “greyed out”, click the Service name radio button then click the SID radio button, and you should be able to enter the appropriate value.

8. Click on the Test button and you should be  presented with a passphrase dialog box. This is the passphrase you entered when creating you SSH key.

9. If all works well, you get a Success Status message.

Click Connect, and you should be connected to your Oracle Cloud Database Service.

Enjoy!

UPDATE 4/19/2017: Check out Pete Scott’s post on Tunneling Through the Clouds where he goes into additional details and features.