The Wonders of SSH Tunneling

database | SSH | DevOps | - May 11, 2010 // Barry

Have you ever been working on a website and needed direct access to the database, but couldn't get access without using something like phpMyAdmin?  SSH tunneling can solve this common problem and a whole lot more.

From Wikipedia "Computer networks use a tunneling protocol when one network protocol (the delivery protocol) encapsulates a different payload protocol. By using tunneling one can (for example) carry a payload over an incompatible delivery-network, or provide a secure path through an untrusted network."  Translation, you can connect to things on one machine as if you were on another, like a proxy.  The biggest difference with SSH tunneling is that all of the traffic is securely encrypted.

So, let's use our database example.  As a security precaution, database users are setup with a username@location so that even if somebody had your database username and password they'd have to be accessing it from the specified location in order to connect.  An unfortunate side effect of this security precaution is that is can significantly slow down development if every user that is working on a site has to be added to every database which they require access to, from every location they may be accessing it.  

The usual solution to this conundrum is to either use the command line database tools from a terminal or to install something like phpMyAdmin so that you can access the database through a web browser.  The former can be particularly slow to work with while the latter can actually create security issues.  Enter the SSH tunnel.  By setting up an SSH tunnel, we can direct traffic to a specific port on our local machine, through our SSH connection to the server, and out to a destination server/port.  Wherever the information is sent, it will appear to have originated from the server that you're currently connected to via SSH.  What that means is that if you're SSH'd into the web server and that webserver can connect to the database, so can you.

Let's look at an example.  Say we want to connect to a database server at on port 3306 and we have ssh access to the web server  We can setup an SSH tunnel using this command:

ssh -L [email protected]

That command will send all traffic on your computer directed to localhost port 3307 over your SSH connection and out to port 3306.  Now you can connect to the database from you local computer using the web server's database creditials, by simply changing the host to localhost and the port to 3307.  In case you're wondering why I specified the local port to 3307, it's so that you can still access port 3306 on your local machine in case you have local development databases running.  Also, if you need to access multiple databases on different web hosts at the same time using this technique, you can simply ssh into all of them simultaneously while giving each host it's own port on your local machine, like so:

ssh -L [email protected]
ssh -L [email protected]
ssh -L [email protected]
ssh -L [email protected]

You can even access multiple hosts over the same connection, like this:

ssh -L -L [email protected]

Now, you may be saying to yourself "But don't some database programs include SSH tunneling?"  Why yes they do, however, this way you can use any software that you like regardless of whether or not the program includes it.  You can even have a local test web server access a remote database.

There's a lot more than you can do with SSH tunneling as well.  Say you'd like to administer a server over a VNC (Remote Desktop) connection but you don't want to open that port up to the world.  With SSH tunneling, simply add another connection:

ssh -L -L 5901:localhost:5900 [email protected]

This is directing all of our local traffic from port 5901 to port 5900 on the machine that we're connected to (aka - localhost from that machine).  Now we can securely access our remote desktop session over an encrypted connection without exposing the VNC port.

Overview of SSH Tunneling

There are numerous other uses for this technique.  For example, recently we were on site with a client who's IT department was blocking access to IRC chat clients.  Unfortunately, we needed to access a public IRC chat room to ask another development team a question about their system.  Using SSH tunneling, we were able to simply connect to our server like so:

ssh -L [email protected]

And then by pointing our IRC program to localhost port 6667, we were actually accessing port 6667 securely over SSH.  We got our question answered by the other development team quickly and our client wasn't billed for the additional time required to call someone outside the firewall, have them hop into the chat room, and relay the conversation.

Now, that you've got all the ins and outs of ssh tunnels, it's important to keep in mind that you should only use these techniques on servers where you have permission.  Additionally, connecting to outside servers may not work if your server admin is blocking outgoing ports as well as incoming ports.