VBMySQL.com

Introduction

A large percentage of MySQL users are using MySQL on a web server hosted by an ISP. Most hosting providers block port 3306 (the MySQL server port) at the firewall, preventing outside access to MySQL. This is an important security practice and you should be very concerned if your ISP does not block port 3306. In this article I will demonstrate how to connect the MySQL GUI tools, including MySQL Administrator and MySQL Query Browser, to a remote server using SSH port forwarding. This article will be written with the assumption that the reader is using Microsoft Windows, but the principles presented will be applicable to Linux users.

What Is SSH?

SSH stands for Secure SHell and is typically used as an encrypted version of telnet. SSH allows you to access a remote server’s shell without compromising security. In a telnet session all communications, including username and password, are transmitted in plain-text, allowing anyone with adequate resources to listen-in on your session and steal passwords and other information. Such sessions are also susceptible to session hijacking, where a malicious user takes over your session once you have authenticated. SSH serves to prevent such vulnerabilities.

OpenSSH, the tool included with most Linux variants, is described as follows in the OpenSSH FAQ at http://www.openssh.org/faq.html#1.1 :

“OpenSSH is a FREE version of the SSH suite of network connectivity tools that increasing numbers of people on the Internet are coming to rely on. Many users of telnet, rlogin, ftp, and other such programs
might not realize that their password is transmitted across the Internet unencrypted, but it is. OpenSSH encrypts all traffic (including passwords) to effectively eliminate eavesdropping, connection hijacking, and other network-level attacks.”

What is SSH Port Forwarding

When a mysql client communicates with the MySQL server, all communication (with the exception of the user password) is done in plain text. What this means is that if an unscrupulous individual gets between your client and the server, they can have full access to all information transmitted. In order to protect your information you need to encrypt communications between the MySQL server and the GUI client.

SSH can be used to encrypt communications between the client and server. This is known as SSH port forwarding or SSH tunneling. One benefit of SSH port forwarding is that we can connect to a MySQL server from behind a firewall when the MySQL server port is blocked.

SSH will listen on a specified port on the client machine, encrypt the data it receives, and forward it to the remote SSH host on port 22 (the SSH protocol port). The remote SSH host will then decrypt the data and forward it to the MySQL server. The SSH host and the MySQL server do not have to be on separate machines, but separate SSH and MySQL servers are supported.

Requirements for SSH and MySQL

To perform port forwarding between a MySQL client application and the MySQL server, you will need a SSH login account for port forwarding. This account needs to either be located on the server running MySQL, or on a machine that can be accessed remotely via SSH and which in turn has network access to the MySQL server.

For this article we will be using Putty, an Open Source SSH client application written by Simon Tatham and available at http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html . Linux users should have a command-line SSH client already installed.

Creating the SSH Tunnel

We will first need to configure a Putty session for port-forwarding. Our first step is to configure Putty to connect via the SSH protocol and specify the server address:

Once we have configured our host and protocol, we can move on to configuring the SSH tunnel. To create an SSH tunnel, we specify a destination host and port:

In this example, we are specifying that port 3306 on our client machine should be forwarded to port 3306 on the remote server. You can also forward data from a local port number that is different than the remote port number. For example, on my development machine I keep a local copy of MySQL running on port 3306. On my production server I also run MySQL on port 3306. I can configure port forwarding with port 3306 as the local port, but all traffic on port 3306 will be intercepted and forwarded, making the local copy of MySQL unreachable. If I change the source port setting to 3307, I can access the remote server through port 3307 and the local server through port 3306.

When the remote SSH host is on a different machine than the MySQL server, replace 127.0.0.1 with the IP address of the MySQL server (relative to the SSH host).

Once we have added our port forwarding directives, we can then save the session to make it available for repeated use. In the session menu specify a saved session name and click save to add this to the list of saved sessions:

Once you have created and saved your session, you can add a shortcut to your desktop to quickly access port forwarding. Right-click on your desktop and choose New> Shortcut. Configure the shortcut and assign the target as /path/to/putty/putty.exe -load sessionname. In the example above, with putty at C:\putty.exe and the profile saved under the name MySQLTunnel, you would assign the shortcut target to be:

C:\putty.exe -load mysqltunnel 

To open the session, double-click the icon and provide a username and password when prompted. A Putty window will open and SSH port forwarding will be established after you successfully log in. When you are finished using the tunnel you can close the Putty window to end SSH port forwarding.

Using the SSH Tunnel

Once SSH port forwarding is established, open your client application (I will use the MySQL Query Browser in this example).

Set the server host to 127.0.0.1, using port 3306 (unless you configured a different port for your tunnel to prevent conflicts with a local copy of MySQL).

When you connect, Putty will act as a proxy and the client will connect to the remote copy of MySQL through the ISP’s firewall. As an added benefit, these communications will be protected by the encryption capabilities of SSH, preventing third parties from eavesdropping on your MySQL session.

Conclusion

SSH port forwarding is a valuable tool for communicating with remote MySQL servers securely, especially when the remote server is protected by a firewall. While an SSH account on the remote server is required, many ISPs are willing to provide one. SSH port forwarding can be used to protect all MySQL client sessions, including MySQL Administrator, MySQL Query Browser, mysqldump, etc.