SSH Tunnels in SQL Developer

In a presentation by krisrice and thatjeffsmith at KScope15, Jeff pointed out some new features including one for creating SSH tunnels using SQL Developer. SQL Developer is an all purpose (and free) development environment for the Oracle database. This feature looks like it should work regardless of whether or not you actually want to connect to an Oracle database. Yes, you can create SSH tunnels the traditional way using your favorite SSH command-line client. This post is not a pros/cons, dogs are better than cats, etc., and nobody should need therapy when we are done. This is just an intro to the feature.

If you happen to be in the population of people who sometimes need to create SSH tunnels and you also happen to use SQL Developer then you may find this interesting. I am also making the following assumptions and will not discuss these items:

You have a remote server that has SSH running and supports key-based authentication.
You already have your SSH public and private keys generated and properly installed on the remote server.
You need to forward requests to (any) service running on a port on the remote server that may be blocked by a firewall or is otherwise only accessible once you are logged into that remote server.

For that last point, assume I have an instance of an Oracle database running behind a firewall on a remote server and the only port accessible to external hosts is TCP port 22 (default for SSH). Using an SSH tunnel with port forwarding you can get SQL Developer up and running on your laptop so that it can connect to your remote Oracle database instance. Here is the lowdown:

First, if you don't have it yet you will need to download SQL Developer version 4.1 or later. I had version 4.0+ and this feature was not yet available.

Note: SQL Developer 4.1 also requires Java 1.8 so if you don't have it installed yet I recommend that you get a copy. If you are not sure if you need this you can just install SQL Developer and it will tell you to update your Java version as needed when you launch it.

Next, open SQL Developer and choose View > SSH which will open up the SSH connections panel.

And then... in the SSH panel, right-click on SSH Hosts and choose New SSH Host...

And then... fill in the dialog with your connection details as required and press OK. Note: if you specify the wrong port (like I did) on the remote server or want to specify the TCP port to bind for your local connections you can edit the connections afterward.

In this example I am using SSH keys to connect to the remote server. This is the recommended practice and will also eliminate the step of having to enter a password in the future. Note: The key you specify in this dialog should be your private key and your public key should already be setup on the remote server with the correct permissions for this to work correctly.

The first port specified is the port of the (Oracle DB) service running on the remote server and the second (local port) can be any available port on your machine that you would like to use. This tunnel will forward traffic from your machine on the local port to the remote server. In this case I would probably recommend using the same port as the remote server unless you have multiple tunnels running to remote servers at the same time.

And then... in the SSH panel your new connection should appear. Right-click on the connection and choose Connect.

At this point, if your connection succeeds you don't necessarily get any positive feedback. You can check to see if you are connected by hovering over the named SSH tunnel with your mouse or by right-clicking on it again to see if you are prompted to Disconnect.

And then... Finally, you should have an SSH tunnel running on your local machine that you can use to access your remote Oracle database by connecting to localhost:<local port> which will forward your connection request and all other SQL Developer TCP traffic to the remote server on the TCP port you have specified. Except for the Hostname and TCP Port number, you should specify all other connection details as usual.

And then... NO AND THEN... I know some of you were waiting for that. I hope you found this overview of creating SSH tunnels in Oracle SQL Developer to be useful and that it helps you along the way.

Cheers!