SQL Connection via an Encrypted SSH Tunnel

Background

I recently did multiple upgrades on my Mac: the O/S to High Sierra, SQL Developer to 18.1, and then the JDK to bring it up to a level that SQL Dev requires. Naturally, I updated to the latest JDK, which is higher than SQL Dev 18.1 supports, but hey, why not?

Any time you change more than one thing, troubleshooting becomes complicated. This was no exception. The challenge started when I realized that by “upgrading” SQL Developer on my Mac, I actually overwrote the previous install, thereby deleting all of my connections and preferences. Sadly, this isn’t the first time I’ve done this. I don’t do a SQL Dev upgrade that often, so I forgot that it would overwrite everything. Sigh.

SSH Tunnel Connections

After increasing the SQL Dev font size (my eyes aren’t what they used to be) I endeavored to recreate my connections. All went well until I tried to set up the connections that require an SSH tunnel. This is a great feature. It allows you to encrypt SQL*Net (or any other kind of traffic) between your laptop and database (or other) server. I’ll spare you most of the trials I attempted working through the different upgrades and provide the spoiler: it was operator error. I didn’t set up my SSH tunnel in SQL Developer correctly.

I worked out how to do it years ago (using SSH without the assistance of SQL Developer). Worked it out again 18 months ago using SQL Developer. And worked it out again yesterday. Each time I went through the same issues and errors. Like I said, when you don’t do something frequently, you forget the details. This time, though, I’m blogging about it, including the error message, so that next year when I Google the error message, I’ll find my own blog post. It’s embarrassing how often that happens. Alas, it’s not just my eyes that are getting old.

Two Flavors

Let’s say you have a database and you want to encrypt the SQL*Net (or jdbc) traffic from the db server to the client (e.g. SQL Developer, SQLcl). This could be for a variety of reasons and there are a variety of solutions. My colleague and overall Oracle and Linux guru, Rich Soule, published a white paper with all the details of the full setup. I’m going to present the “SSH Tunnel” solution, in two flavors:

1. Use SSH command line to establish the SSH Tunnel
2. Use SQL Developer to establish the SSH Tunnel

I’m also going to include the error message you may receive if you do it incorrectly.

In SQL Developer you may see the following:

An error was encountered performing the requested operation:
IO Error: Connection reset by peer, connect lapse 73 ms., Authentication lapse 0 ms.
Vendor code 17002

In SQLcl, you may see this:

Status : Failure -Test failed: IO Error: Connection reset by peer, connect lapse 3 ms., Authentication lapse 0 ms.

In both cases, you have probably fallen victim to thinking you have followed the SQL Developer help or the many blog posts about how to do this. Unfortunately, most of those, including the help, look at a simple case with only one or two machines involved. In my case, I always have at least 3 and sometimes 4 or 5 machines involved.

Machines involved

The 4 most likely machines involved are

1. Laptop with SQLDev or SQLcl (myLaptop or “localhost”), potentially outside the firewall.
2. Firewall (insum.talan.com) which is port forwarding (potentially from some random port, e.g. 44549 *) to an SSH server
3. The SSH server (myssh.insum.talan.com)
4. The db server (db.insum.talan.com)

You might have an additional firewall between 3 & 4. Aside from more things to configure and troubleshoot, though, it doesn’t change the steps required for this blog post.

Throughout this blog post, I will use the machine names above in the examples.

* If you have your firewall forward the standard SSH port (22) you will get a LOT of random hacking attempts. If you assign some non-standard port, those attacks will be greatly reduced. In most cases, the attacker will only have a chance of finding your random port by doing a port scan on your firewall and your firewall will shut it down before it makes it to the port you chose.

1 & 4 are required.
If there is a firewall, 3 & 4 are not visible to the outside world.
If any components are collapsed (or don’t exist), you just use the appropriate machine name. So, if you are using an SSH account on your database server (which isn’t that great of an idea, unless it’s all that you have) then you’ll just configure the SSH things on your equivalent of db.insum.talan.com instead of on myssh.insum.talan.com.

Configuring the Firewall

If you’re going to do this, you should do it right. That means configuring the firewall correctly, locking down the SSH server so that only the appropriate user or users have access, using key files, etc. That said, you’ll likely have a Linux admin and firewall admin doing at least some of the work. We’ll cover the Linux steps in another post. Let’s assume that you have been given the following:

1. The firewall server name (or the SSH server name if there is not a firewall involved): insum.talan.com
2. The port on insum.talan.com that is accepting SSH traffic: 44549
3. The db server name. This is the name that the SSH server can use to see the db: db.insum.talan.com, not that your laptop doesn’t need to be able to resolve this host.
4. The db service name: dev01.insum.talan.com
5. The db listener port: 1521
6. An OS user on the SSH server: sshuser
7. Either a key file for the OS user or the password for the OS user**. Key file name: my_key_file, Password for sshuser: myPassword

Throughout this blog post, I will use the values above in the examples.

** Of course, I recommend a passphrase protected key file for this. Describing how to create the key file, set up a no-login SSH account, configure the firewall, etc. is covered in Rich’s white paper. As all of this opens a significant hole in your firewall, I recommend reading that white paper and working with your firewall and Linux admins to do it correctly.

In this blog post, I’m not covering the server-side setups, but I will show how to set up SQL Dev (and SSH command line) to connect with either a key file or password.

Creating an SSH Tunnel Command Line

Creating an SSH tunnel simply establishes a secure channel between two machines and says where to route the traffic passing through the tunnel. In practice, this is accomplished by defining a port on the local machine to accept traffic and routing it via SSH to an SSH server on another machine. The SSH server then routes the traffic to a machine and port accessible to the SSH server. In our scenario, we also have a firewall that in the mix. The network traffic will ultimately look like this:

SQLcl connects to localHost on port 55444 > myLaptop forwards the traffic to the firewall (insum.talan.com) on port 44549 > the firewall forwards the traffic without inspection to the SSH server (myssh.insum.talan.com) on port 44549 > myssh.insum.talan.com forwards the traffic to db.insum.talan.com on port 1521

The firewall is pre-configured to port forward, so as a user, you don’t need to know the name of myssh.insum.talan.com. As mentioned above, though, you do need an ssh user on myssh.insum.talan.com. You can use the following command line to establish this tunnel:

ssh -L 55444:db.insum.talan.com:1521 -f -C -q -N -i my_key_file sshuser@insum.talan.com -p 44549

The Break down

-L   localPort:destination_machine:destination_port
        localPort               This can be any available port on localhost (your laptop). 
                                I simply chose 55444 and checked to see it was available.
        destination_machine     The database server
        destination_port        The database listener port

-f -C -q -N  combine to allow you to do a nologin connection, return to the command line without having to ctl-c,
             and to continue running after closing the command window. In order to stop your SSH tunnel,
             you will need to find the process and kill it using
             ps -ef | grep ssh
             Locate the process id and
             kill -9 [pid]

             Note: if you have any issues, change -q to -v to get verbose output. 

-i   key file name (if you are using a key file)

-p   the port your ssh server is using (or that your firewall is port forwarding)

If you don’t plan to use a key file, you can skip the -i portion:

ssh -L 55444:db.insum.talan.com:1521 -f -C -q -N sshuser@insum.talan.com -p 44549

You will be prompted for either the key file pass phrase or the SSH user’s password.

Voilà, Encryption

That’s it, now you have an SSH tunnel with encrypted traffic running between myLaptop, port 55444, and myssh.insum.talan.com and forwarding it unencrypted on port 1521 to db.insum.talan.com.

Keep in mind that anything can use this tunnel: SQLcl, SQL Developer, or literally any other process on your machine that might be aware of this tunnel.

To run SQLcl through the SSH tunnel, just “pretend” the database is running on localhost port 55444:

./sql anton@localhost:55444/dev01.insum.talan.com

Of course, once you have this SSH tunnel running, you can do the same thing in SQL Developer. Just create a connection using
host: localhost
port: 55444
service name: dev01.insum.talan.com

Keep in mind, exiting from SQLcl or SQL Developer does not close the SSH tunnel. To do this, you’ll need to kill the process as described above.

Creating an SSH Tunnel in SQL Developer

If you skipped “Creating an SSH Tunnel Command Line above” you may wish to read it just to get an understanding of what is happening. SQL Developer does essentially the same thing as the command line above, but it gives you a user interface to put in all the bits of information. The key is to put the info in the right spots. Rich’s white paper has lots of pictures of each of these steps. I’ll capture the minimum here.

1. Run SQL Developer
2. Click View > SSH
3. At the lower left, right click on SSH Hosts, then choose New SSH Host

This will create an SSH Tunnel definition equivalent to the command line in the previous section.

You can then create a database connection with connection type SSH.

When you connect to the database you will be prompted for either the key file pass phrase or the SSH user’s password. This will open the SSH tunnel. Alternatively, you can open the SSH tunnel by right clicking on the local port forward under SSH Hosts.

Once the tunnel is up and running, it is accessible to anything running on your laptop. Hence, you are able to use SQLcl through the SSH tunnel established within SQL Developer, much as you could use SQL Developer through the SSH command line tunnel.

Keep in mind, disconnecting from the database in SQL Developer does not close the SSH tunnel. To do this, you’ll need to right click on the local port forward connection (under SSH Hosts) and disconnect, or exit SQL Developer.

Questions on creating an SSH tunnel? Comment below or Contact Us.