MySQL "access denied": Setting up an ODBC connection over an SSH tunnel

I just set up an SSH tunnel to a MySQL database using MyODBC version 3.51. It took several hours to get everything working; hopefully these instructions will save someone else the time I spent on it.

1. Setting up the tunnel.

   
   I use OpenSSH in Cygwin on Windows XP. To set up an SSH tunnel from my local machine to the host, I use the command

    ssh -N -f -L 3307:localhost:3306 myname@myhost
    

   in Cygwin. The -N means "no command"; the tunnel will be set up, but nothing will be done. The -f means "run in the background", so the command will return to let me do other things.

   The "-L 3307:localhost:3306" describes the kind of tunnel I want. It says the local port number is 3307. The remote machine should connect to itself ("localhost") on port 3306.

   Finally, "myname@myhost" is the name of my account on the remote host for ssh to connect to.

   This is all made quite convenient because I use public key logins with ssh-agent; you can read about setting those up somewhere else, e.g. here.

   You could also use plink to make this connection.

2. Setting up access to the database

   
   I'm not sure if this is just the way our system is set up, but I found that I needed to grant access privileges to my user on host "localhost.localdomain". Granting them to my user on host "localhost" was not enough, even though it works for local connections. I also found that my random 9 letters and digits password worked fine for local connections, but not over ODBC: I needed to shorten it to 8 letters and digits.

3. Setting up the ODBC connection

   
   First, you need to download and install the MyODBC driver from www.mysql.org, currently at http://dev.mysql.com/downloads/connector/odbc/3.51.html.

   Then, in Windows XP, go to Settings|Control Panel|Administrative tools|Data sources|Add... Choose the "MySQL ODBC 3.51 driver" (or whatever version you downloaded), and fill in the fields. The meaning is:

   • Data source name: the name you'll use in Windows to refer to the database.
   • Description: your helpful description.
   • Server: localhost
   • User: mysqlname (your MySQL database username)
   • Password: ******** (your MySQL database password)
   • Database: mydatabase (the name of the database to connect to)
   Careful with the password: remember, max 8 letters and digits.

   On the 2nd page of the dialog, choose to connect to port 3307 (matching the local port that the tunnel is using).

   Then click "Test". Getting the details above right caused me the most grief. I got errors like

   [MySQL][ODBC 3.51 Driver]#HY000Host 'localhost.localdomain' is not allowed to connect to this MySQL server
   

   This was fixed by granting my username access rights from host localhost.localdomain.

   [MySQL][ODBC 3.51 Driver]Access denied for user 'mysqlname'@'localhost.localdomain' (using password: YES)
   

   This was fixed by switching to an 8 character password.

Last modified 23 May 2006

Back to home page