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.