Med fokus på din virksomheds forretningsgange watermark with eaktion circles

Connecting Access to MySQL through a SSH tunnel using Putty and port forwarding

I’m publishing today this article that is some years old and was just collecting dust in my drawer. I believe it can still be useful to many. In particular the problem I’m going to solve is about the MySql error:

Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’

Just paste the title of the post in a Google search box and you’ll get plenty of tutorials for how to set up Putty to do this.

The reason why I add something to the argument, is that after having used tunneling to connect to a MySQL RDBMS for some years, the day came when my settings didn’t work any longer. I used a fair amount of hours to find a solution to the problem and Google was only helpful to exclude other solutions. There’s no reason why you should do the same.

The error I got when testing the connection from the Connector configuration window was:

Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’, system error: 2

If you google the Internet to find a solution to this error, you will probably come to the conclusion that something is wrong with your firewall settings or your network connectivity. Many times the solution given to solve problems that lead to the same error message, is to correct the server connect_timeout from the once default 5 (seconds) to a higher time span.

However this was definitely not the culprit in my case.

My setup

Now, before we go on to read about the mistake I made and the solution, I’d better add some details about the setup I’m working with. In a different setup you might have to use somewhat different settings.

I’m connecting to a MySQL server on a Linux box (Debian etch), from Windows XP / Vista.
The tunnel (port forwarding over SSH) is done using Putty.

What is important to notice for this text to be valid is the fact that MySQL runs on Linux and not on Windows.

The wrong tunnel configuration

The mistake is shown in this picture showing Putty tunnel configuration.

I had set the tunnel in such a way, to forward my local port to remote Linux box, port 3306 – writing the domain name, it could also have been the remote IP address. Now, this is not wrong in general and among the tutorials you’ll find on the Internet many recommend this setting. On the other hand the tunnel had been working fine for some years, and it would have gone on for more years if I hadn’t switched to a different server.

DSN configuration

Before we come to why this setting in fact is a mistake we’ll also have a look at another of the settings needed for the Access-MySQL connection to work, the DSN settings.

As you might know, here we set the data source as being the

local port (2306 in my case, different from MySQL default port 3306 to avoid conflicts with a possible MySQL for Windows installed on my PC) that we want to forward to the remote MySQL server. The setting under “Server” must be 127.0.0.1 in most of the cases. The reason is that your /etc/mysql/my.cnf file will have a line:

bind-address            = 127.0.0.1

This tells the server to only allow connection from the localhost, that is from users of the same Linux box, not connecting from outside. This line is default in a Debian distribution, and I believe it’s default in many other distros too. It avoids to expose the server to all possible connection attempts from black hat guys on the Internet. This is actually one of the reasons why we do SSH tunneling in the first place.

When you have logged into your server through Putty, your server is no longer remote, and that’s why 127.0.0.1 here doesn’t prevent the connection, and at the same time you comply with what is written in the option file (bind-address   = 127.0.0.1).  What happens is that all what you send to your local port will be transferred by Putty to the corresponding port on what “was” the remote server. MS Access or any other application using that port will never know.

This setting is shown correctly in all the tutorials, I have seen on the Internet.

Why the Tunnel configuration was wrong

However one could ask, whether or not we need to write the remote address or the localhost address in the tunnel configuration, not all the tutorials agree on this point. And in fact the answer is that we don’t need to write the remote address, or rather that we should avoid this: When Putty is establishing the port forwarding, it is already connected to your remote server, the connection happens between the two ports on the local and the remote server no matter if we write the local or the remote address in the tunnel settings, as long as it is a valid way of specifying it.

Putty doesn’t care, SSH doesn’t care, but MySQL does apparently care: as we have seen,

it will toss a: Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’, system error: 2

To sum up

Summing up:

write the loopback address in the tunnel settings,

write the loopback address in the DSN setting

One last detail

Yes, one last thing remains to be explained and this is why on earth the “wrong” settings did work on my first server, and did no longer work when I used them on a new server?

Now, my explanation here is a fair amount more than plain guesswork, but still guesswork, however it makes sense to me and it is enough to let me sleep at night. It might satisfy you as well: I believe the reason is that on the first box (where the remote address didn’t hinder the connection) I had only one IP address on one network card. The OS was probably making its own translation of the remote address into the loopback address before MySQL received it. On the second box (where this setting stopped working) I had several IP addresses, and probably for this reason the remote address was no longer just translated by the OS into the loopback address for MySQL, and this triggered the connection error.

At least this will give you one more chance to find a solution to the problem, before blaming your firewall settings or network connectivity.

6 Responses to “Connecting Access to MySQL through a SSH tunnel using Putty and port forwarding”

  1. Luciano Says:

    tks a lot man u saved my day

  2. Gary C Says:

    Thanks for the effort on the write up… I only have this problem on ONE server.. but it is still annoying…

    Quick observation:
    You never actually say or show an example of what the correct config looks like. Can you add that info??

  3. Jules Says:

    Cheers man you saved me some time.
    Just to expand on your “To sum up” point:
    Summing up:
    – in the “Destination” box in the SSH Tunnels section, write “127.0.0.1:3306” (or replace “3306” with the port number your server is serving mysql on.
    – write the loopback address (“127.0.0.1”) in the DSN setting

  4. Kalyan Says:

    Thanks a ton. This helped me resolve a burning issue

    Regards,
    kalyan

  5. serhost Says:

    It seems to me that mysql only listens one address, your public IP address and not the loopback. Try connecting with the public address of your server instead of 127.0.0.1.

    At least it is what happened in my case.

  6. pnp Says:

    @Gary C pls. see the answer by @Jules
    @Jules Exactly, thank you.
    @serhost No, this is taken care of by the part: “… The setting under “Server” must be 127.0.0.1 in most of the cases. The reason is that your /etc/mysql/my.cnf file will have a line:

    bind-address = 127.0.0.1

    This tells the server to only allow connection from the localhost, that is from users of the same Linux box, not connecting from outside.
    If you have “bind-address = 127.0.0.1” in your my.cnf you can only connect from localhost, no outside ip address.