Using SSH in HeidiSQL and MySQL Query Browser: Encrypting Sessions

Using a MySQL GUI (Graphical User Interface) makes it easier, more efficient and user-friendly to work with a MySQL database. The two most popular MySQL GUIs, HeidiSQL and MySQL Query Browser, have their default connections to any MySQL server “unencrypted.” This is a problem, but we’re going to fix it; just keep reading.

This tutorial will help you configure these two important MySQL GUIs to connect and do sessions in the SSH (Secure Shell) protocol. This will ensure that communication to your MySQL server is encrypted. Encrypted communication ensures the integrity and security of your data against unauthorized third party packet sniffers on the Internet.

You can find the HeidiSQL GUI and MySQL Query Browser online at the links shown.

The Basic Requirements

In order to make these MySQL GUIs work with SSH, you should meet the following requirements:

1. You have SSH enabled for your hosting account. Check with your hosting company to see if you have SSH enabled.

2. You have an SSH client. This tutorial uses the Putty SSH Client, which you can download here: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html 

For the Linux version (Ubuntu), you can simply go to Applications — Ubuntu Software  Center – Internet — and click Putty SSH Client to install.

3. If you are going to use HeidiSQL, which is an entirely MySQL GUI (there is no need to run commands to work with a database and tables), you can download it here: http://www.heidisql.com/

HeidiSQL is a Windows-based MySQL GUI but will run perfectly in Linux using Wine.

4. If you like the native MySQL GUI which is MySQL Query Browser, you can download it here for Windows: http://dev.mysql.com/downloads/gui-tools/5.0.html and select the Windows version.

If you are using Linux Ubuntu, you can also download it at Applications — Ubuntu Software  Center – ProgrammingMySQL Query Browser — and click it to install.

{mospagebreak title=SSH Port Forwarding: The Solution}

Without encryption, it is said that your data (including your MySQL log-in information) will travel as packets on the Internet in "clear text format." If a hacker (or anyone) sniffs that data using a third-party application, he or she will immediately see and understand the information. If the sniffed data is your MySQL log-in, it can be compromised.

The solution is to encrypt packets using SSH is to employ "Port forwarding" techniques. It is illustrated in the screen shot below:

If a MySQL GUI (HeidiSQL or MySQL query browser) connects to your MySQL server "securely," the first thing that will happen is that it will use a local host port other than the one that your local MySQL server is using. MySQL commonly uses Port 3306 for its communications. If you are developer, chances are you have MySQL installed locally, which also uses Port 3306.

To prevent any possibility of conflict, it will use Port 3307 locally, which is then detected by an SSH Client (Putty) and "port forward" to Port 3306 in your remote MySQL server. Of course, before your SSH client can accomplish this task, it must first be connected to your remote SSH server, which is your web host.

To enable port forwarding, you need to configure Putty to accomplish that job. The result is that the SSH client will sit "in between" your MySQL GUI and your hosting MySQL server. All traffic traveling from your SSH client (in your local computer) to your remote MySQL server Port 3306 has been encrypted using SSH.

To summarize, your MySQL GUI uses local port 3307, which is "port forwarded" by the Putty SSH Client to your remote MySQL server using standard MySQL port 3306. This is how the encryption of MySQL data has been accomplished.

{mospagebreak title=Step 1: Enable Port Forwarding in Putty for MySQL SSH Application}

First, you need to enable port forwarding in Putty for MySQL purposes. To do this, follow the procedure below.

1. Launch Putty.

2. Go to Session; under "Category," enter the following required log-in information:

Host name: this is your hosting account SSH hostname (this is NOT your MySQL hostname). 

Port: This is your SSH port. Contact your web hosting company to find out which SSH port they use. The standard SSH port is 22, but some hosting companies use different ports.

Under Connection Type: Select "SSH"

For example, suppose you have the following SSH log-in for your website (from your hosting account):

hostname: yourwebsite.com

port: 22

connection type: ssh

 

3. Under Connection — SSH – click Tunnels. This is where you will configure your port forwarding options. Fill in the data listed below.

  • Source port: 3307

  • Destination: yourmysqlserverhostname:3306

  • Check " Local"

  • Check " Auto"

Now click "Add."

For example, suppose your MySQL hostname is: codex345.extremehost.com

After clicking "Add," your options for controlling SSH port forwarding should look like the screen shot below.

 

4. Finally, after going through steps 1 to 3 above, click "Open." Putty will connect to your SSH server.

5.  Enter your SSH username and password.

6.  If you finally see the bash prompt that ends with "$", you have successfully connected to your SSH hosting server and enabled port forwarding.

{mospagebreak title=Step 2: Configure your MySQL Graphical User Interface (GUI)}

Finally you are ready to connect to your MySQL server in SSH, because the port forwarding at local port 3307 has been enabled. Suppose you have the following MySQL log-in information below:  

MySQL username: codexm

MySQL password: coolguy  

It’s VERY IMPORTANT that you execute step 2 while Putty is still connected to your SSH server. Do not disconnect the Putty connection that you made in step 1 or else you will not be able to connect to your MYSQL server in step 2. 

Using HeidiSQL

Follow the procedure below.  

1. Launch HeidiSQL.

2. Click "New" in the Session Manager.

3. Configure the following:  

Network Type: TCP/IP

Hostname: 127.0.0.1

User: codexm

Password: coolguy

Port: 3307

Compressed client/server protocol: Checked  

After entering the details above, it should look like the screen shot below:

 

4. Click OPEN. If there are no errors, you have successfully connected to your MySQL server using SSH, and HeidiSQL will then show you your databases.

Using MySQL Query Browser

If you are using the MySQL Query Browser, follow the procedure below.

1. Launch MySQL Query Browser.

2. Configure the options below:

Stored connection: (leave this blank)

Server hostname: 127.0.0.1

Port: 3307

Username: codexm

Password: coolguy

Default schema: (leave this blank)

After entering the above configuration details, it should look like the screen shot below:

There are two important things you need to note regarding the use of MySQL GUI.

First, there may be times when you can not establish a connection to your MySQL server even though you have perfectly followed the steps outlined in this tutorial. If this happens, you need to contact your web host and report that error. This might be due to some MySQL server restrictions they have  imposed.

Second, if you are not certain as to whether your connection has been encrypted, you can inspect the packets going out of your network card using Wireshark and compare them with the packets leaving your computer using an unencrypted MySQL session. The recommended test procedure, if you are interested in doing this, is as follows:

Step 1: Install Wireshark.

Step 2: Hit capture to make Wireshark capture packets from your network interface card.

Step 3: Just seconds after hitting capture, log in to your MySQL server using phpMyAdmin "unencrypted."

Step 4: Immediately after pressing login (around three seconds later), hit stop capture.

Step 5: Look for any POST entry in the packets gathered. You will surely get your username and password in "clear text" because they are not encrypted.

Now, using your MySQL GUI and Putty, configure them so that they will connect to your MySQL server using SSH. Repeat steps 1 and 2 above. In step 3, instead of hitting the login button in phpMyAdmin, you will hit the Connect/Open button in MySQL GUI.

Just seconds after hitting "Connect," hit stop capture in Wireshark. If you examine those capture packets very closely, you will not be able to understand them because they are not in "clear text form;" instead, they are encrypted.

Google+ Comments

Google+ Comments