Can't connect to MySQL database over SSH with Workbench

Question

I'm trying to connect to my database via SSH tunneling from one of our web app servers with MySQL Workbench. Here's the basic config; note that I changed some values in the screenshot for security reasons.

The problem is every time I try to connect over an SSH tunnel from one of our app servers, I get the following error:

Failed to connect to us-east-1.amazonaws.com through SSH tunnel at computer.amazonaws.com with user social_shop_prod. Can't connect to MySQL server on 127.0.0.1.

However, if I use the same credentials over SSH via the following command line:

mysql -u social_shop_prod -h us-east-1.amazonaws.com -p

I can connect successfully and get the MySQL interactive command prompt.

Been talking with the rest of my development team here and none of us can figure out why I can't tunnel over SSH from our app servers with Workbench; but when I SSH to one of our app servers and connect to MySQL via the command line; I can connect successfully.

And why is it trying to connect over 127.0.0.1? I didn't specify that in the configuration; nor is my hosts file redirecting the domains shown below to that IP.

Any constructive input is greatly appreciated.

Answer 1

Since you are connecting through a SSH tunnel, this means that the MySQL port 3306 from us-east-1.amazonaws.com is being opened locally on your computer. The ip address of your computer is 127.0.0.1 or localhost. When you connect to the mysql server on us-east-1.amazonaws.com, you're actually accessing it via 127.0.0.1, i.e. your computer. If you had another tunnel open, or MySQL running locally on your computer, then it may be that other MySQL server that's rejecting your authentication attempts

There are some tests you can try :

1. What ports is you Windows computer listening on

From a command prompt : netstat -a (lists all the ports that are open)

In linux it would be : netstat -tlpn

2. Basic connectivity test

From a DOS command prompt or linux console : telnet 127.0.0.1 3306

If you get a time out, or some other program responds then your tunnel isn't setup correctly.

3. Change the port number that MySQL Workbench is opening locally

We're assuming that MySQL workbench is creating the tunnel on your computer. If so, in MySQL workbench, try tunneling through another port number like 9000.

Make sure that 9000 wasn't listed as an open port from : netstat -a

If you have ssh access to us-east-1.amazonaws.com

4. Try connecting to MySQL from us-east-1.amazonaws.com

mysql -u myuser -h 127.0.0.1 -p

And as Rolando said, you'll want to verify that you are connecting with the right credentials. For example, if you are connecting as myuser@127.0.0.1 and you have a user myuser without a host, you probably won't be able to connect using user@127.0.0.1.

Answer 2

The reason that 127.0.0.1 is being contacted is because the tunnel connects a port on your local machine to the remote host. The message seems to suggest that an SSH connection is not being made.

Try this from the command line:

ssh -L 33000:remotehost:3306 user@remotehost

Make sure that SSH is allowing forwarded ports; if you get a message that states the forward was not permitted or was refused, then that is why.

To fix this, you'd have to change the server configuration; add this configuration to the SSH server:

AllowTcpForwarding yes

Don't forget to restart the server to activate this configuration.

Answer 3

I kept dealing with this problem for nearly 2 weeks now I've managed to set it. I'll post the it here so more people can try it.

Ok, I'm using OpenSSH (Win10 native) and Workbench 8.

Step-by-step:
1. Add your SSH host keys to the agent using ssh-add.
2. Generate the key pairs using ssh-keygen. In my case, these files go automatically to Users/myUser/.ssh as per installation configs.
3. Add the public key to the authorized_keys file (which must be inside your server installation path, in my case Windows/System32/OpenSSH/.ssh) with no extensions.
4. Add the generated keys you just made to agent using ssh-add.

The steps above are pretty much the configuration of a command line SSH Server which I figure out the OP already did for his connection is working through the terminal. To configure MySQL Workbench 8 you pretty much do the exact same thing except you must convert the private_key.pem to a OpenSSH format before moving them to Users/myUser/.ssh and OpenSSH_instalation_path/.ssh

1. Convert private_key.pem to OpenSSH format using PUTTYgen.
   
2. Copy the "Public Key for pasting into OpenSSH authorized_keys" field.
   
3. Save the public key in the folders User/myUser/.ssh and OpenSSH_instalation_path/.ssh
   
4. Paste the key you copied from PUTTY to the authorized_keys file at the OpenSSH_instalation_path/.ssh folder.
5. Export the OpenSSH format key to the User/myUser/.ssh.
6. Restart sshd and mysql services
7. Configure The Workbench to connect to The SSH server using the converted private key.

UPDATE: You will need to set the following in your sshd_config file:

PermitRootLogin without-password
PubkeyAuthentication yes
PasswordAuthentication no
PermitEmptyPasswords no
AllowTcpForwarding yes

PLEASE READ:

I'm an inexperienced analyst, so if a more advanced user knows which of the two .ssh folders is the right one, please tell us. I find the OpenSSH kinda ambiguous about this.
This worked for me while setting a local development environment using localhost only. Just for learning purposes.
You may want to add myUser@localhost to MySQL Workbench users before testing the connection.
If is really needed I can add images.

Answer 4

In my case the issue I had to switch back to a valid domain name or IP instead of a custom host locally host resolved.

Custom host resolution fail (/etc/hosts)

I work with a local hosts resolution mechanism which defined:

#.#.#.#    my-vm

For some reason with MySQL 5.2.47 on Linux Mint 14 (Nadia) which is similar to Ubuntu 12.10 (Quantal) the resolution mechanism doesn't work.

Solution

Simply switch to a public domain name such as my-website.com solve the issue.

Answer 5

See here for a list of resources for connecting to various targets (Amazon RDS, Amazon EC2, Windows Azure and others): http://forums.mysql.com/read.php?152,252640,252640#msg-252640.

Answer 6

I had a similar problem, and this may be obvisous, but check you firewall rules in AWS. I have an IP range set, to restrict connections from the outside world. And the IP at my office changed, so it got outside the IP range. My colleague in another ofice could still connect, so I thought the problem is on my PC, but the problem was with the firewall rules in AWS. Hope that helps someone :)