15

For months I've been connecting to the MySQL instance running on our local test server through an SSH tunnel without any issues. All of sudden though, with no changes I can think of, the server has started rejecting the log in attempt from Sequel Pro with the error:

Unable to connect to host 127.0.0.1 because access was denied.

Double-check your username and password and ensure that access from your current location is permitted.

MySQL said: Access denied for user 'root'@'localhost' (using password: YES)

I'm able to log in from the terminal when connected directly to the server through SSH, just not through an SSH tunnel. The problem isn't specific to Sequel Pro or just myself either, I get the same error when connecting through MySQL Workbench as do others in the office. I've reset the password with mysqladmin just for sanity's sake, that's definitely not the issue.

When I started looking into it more I noticed that the error was reporting the server as "localhost", instead of "127.0.0.1" which I entered in Sequel Pro. A friend suggested that's probably just bad error handling, but it seems strange given the significant difference between localhost and 127.0.0.1 in MySQL.

In an attempt to get around the tunnelling issue, I granted access to root@%, so that I can connect directly. This works for the most part, I can view table data, create new databases, etc. The only problem is when I come to create users I get the error:

Access denied for user 'root'@'%' (using password: YES)

Oddly the user is actually created, I think it's just an issue with grant. Again though, from the terminal I can do anything when logged in as root.

Can anybody help shed some light on why tunnel connections and (probably) grant commands are receiving the access denied error?

For reference MySQ is version 5.6.16 with mostly the default settings, installed via Homebrew on a MAC OS X Server machine.

Update

Here's the list of hosts that root is currently granted access on:

mysql> select host,user from mysql.user where user='root';
+----------------+------+
| host           | user |
+----------------+------+
| %              | root |
| 127.0.0.1      | root |
| ::1            | root |
| localhost      | root |
+----------------+------+
4 rows in set (0.00 sec)

As I understand it, the first row ("%") should really make the the others redundant?

Update 2

Fixed the grant issue; the root@% user has not been granted all privileges with the extra with grant option on the end, so it could do everything but grant. Would still love to know why SSH tunnels are being denied though.

Adam
  • 253
  • 1
  • 2
  • 7
  • so, before you made the"%" account, you made the "127.0.0.1" and the "localhost" and both of these did not work, is that correct? – Sverre Jul 21 '14 at 09:07
  • localhost only works if you are actually on the right host, so in theory only 127.0.0.1 (the network equivalent to localhost) should work over a ssh tunnel. in unix like OSes at least. – Sverre Jul 21 '14 at 09:08

3 Answers3

22

In MySQL, the localhost keyword is reserved for connection using the MySQL socket and you should use the ip-address 127.0.0.1 for TCP connections to the MySQL network port on 127.0.0.1. This means that both the server must grant privileges to users from specifically 127.0.0.1, and the client must use -h 127.0.0.1 to go through the tunnel instead of connecting to a local socket.

To allow you access using the SSH port forwarding you need something like:

GRANT SELECT ON *.* TO user@`127.0.0.1`

and then run 

FLUSH PRIVILEGES;

and possibly 

FLUSH QUERY CACHE;

If it still doesn't work, restart the server process.

In error messages 127.0.0.1 after a reverse DNS lookup gets translated to localhost making debugging difficult.

As the manual describes it:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP

The --protocol option enables you to establish a particular type of connection even when the other options would normally default to some other protocol.

fuzzyTew
  • 134
  • 2
  • 9
HBruijn
  • 77,029
  • 24
  • 135
  • 201
  • 2
    Ah! That then explains why I'm seeing localhost in the error message when I expected to see 127.0.0.1. Never the less, the root user has been granted all privileges @127.0.0.1, but I still get the access denied error when connecting through an SSH tunnel. Exact statement I ran is: `grant all on *.* to 'root'@'127.0.0.1' with grant option;` – Adam Jul 21 '14 at 10:46
  • I'm having this issue, and it is clear that is indeed the host vs socket issue. When I type `ssh -h 127.0.0.1 -u root -p` remotely on the server, the connection is also rejected. – fuzzyTew May 11 '16 at 15:36
  • I resolved the `-h 127.0.0.1` connections failing on the server by rebooting mysql, but the ssh socket still won't work – fuzzyTew May 11 '16 at 15:39
  • Here we go. 1. Server must grant to @127.0.0.1. This didn't take for me until I rebooted the server. 2. Local connection must be to 127.0.0.1 *not* localhost, so that the tunnel is used instead of a local socket. – fuzzyTew May 11 '16 at 15:41
2

I have seen in the past with ssh tunels, that there is a difference between Grant all to "localhost" and grant all to "127.0.0.1" so try grant to "127.0.0.1 instead or in addition to your "localhost" grant.

Sverre
  • 753
  • 2
  • 12
  • 23
  • or opposite, in your case it looks like – Sverre Jul 21 '14 at 08:55
  • Thanks, I've updated the question to include a list of hosts root is granted on. I did find it strange that despite entering "127.0.0.1" as the MySQL server in Sequel Pro, the MySQL error was reporting it as "localhost". I don't think this is the problem though. – Adam Jul 21 '14 at 09:02
  • do you have passwords on all the different users? also when you connect to the mysql over the tunnel, what is the actual syntax you use? – Sverre Jul 21 '14 at 09:09
  • are there any restrictions on the Root user in your mysql setup? – Sverre Jul 21 '14 at 09:10
  • Just re-run the `grant` statements with `identified by ..` to ensure all root passwords are the same, but no joy. – Adam Jul 21 '14 at 09:12
  • What kind of restrictions? None that I'm aware of. – Adam Jul 21 '14 at 09:13
  • look in my.conf to see if there are any restrictions there. also run FLUSH PRIVELEGES" – Sverre Jul 21 '14 at 09:13
  • or, you can try to make a non-root user, and grant for it, and test it trough the SSL tunnel. – Sverre Jul 21 '14 at 09:14
  • Nope, no restrictions. I actually fixed the grant issue, it was just that grant doesn't automatically give grant privilleges. Still stuck with the SSH tunnel though. – Adam Jul 21 '14 at 09:23
  • can you shed some light on how you setup the ssh tunnel? – Sverre Jul 21 '14 at 09:30
  • The tunnel is created by Sequel Pro, so unfortunately I'm not sure on the specifics. – Adam Jul 21 '14 at 10:48
  • can you screenshot or otherwise show us your setup in sequel pro? – Sverre Jul 21 '14 at 10:55
0

Instead of using the Sequel to create the tunnel, what is you create the tunnel yourself ?

ssh -Cc blowfish -Nf -vv -L3306:localhost:3306 sshuser@domain

Then connect with Sequel to 127.0.0.1:3306 Can you connect ? Does anything appear on your terminal (or ssh client logs)?

Florian Bidabé
  • 334
  • 3
  • 10