MySQL - ODBC connect fails, Workbench connect works
Solved.
As it turns out, it was a permissions problem. I ran the following command on the remote server SQL:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'(my_host)' IDENTIFIED BY '(my_password)';
I had run the previous command, but without the "IDENTIFIED BY" password. Then, to reset the mysql permissions cache, I also ran
FLUSH PRIVILEGES;
And now it works.
PDoria
Updated on June 15, 2022Comments
-
PDoria almost 2 years
I am trying to install and test a MySQL ODBC Connector on my machine (Windows 7) to connect to a remote MySQL DB server, but, when I configure and test the connection, I keep getting the following error:
Connection Failed [MySQL][ODBC 5.3(w) Driver]Access denied for user 'root'@'(my host)' (using password: YES):
The problem is, I can connect with MySQL Workbench (remotely - from my local machine to the remote server) just fine. I have read this FAQ extensively but it's not helping out. I have tried:
- Checking if mysql is running on the server (it is. I even tried restarting it many times);
- Checking if the port is listening for connection on the remote server. It is.
- Connecting to the remote server using MySQL Workbench. It works.
- Checking if the IP address and Ports of the remote database are correct;
- Checking if the user (root) and password are correct;
- Re-entering the password on the ODBC config window;
- Checking and modifying the contents of the "my.conf" on the remote server to allow connections from all sides (0.0.0.0);
- Including (my host) on the GRANT HOST tables from mySQL (I also tried the wildcard '%' but it's the same as nothing);
- Running a FLUSH HOSTS; And FLUSH PRIVILEGES; command on the remote mySQL server to reset the privilege cache;
- Turning off my Firewall during the configuration of the ODBC driver;
- Checked if the MySQL variable 'skip_networking' is OFF in order to allow remote connections.
What is frustrating is that I can connect with MySQL Workbench on my local machine (with the same IP/user/password), just not with ODBC.
What could I be doing wrong, or what could be messing up my attempt to connect with ODBC?
Update: I managed to set up the ODBC driver and get it running correctly on the server side. I can connect there to the localhost using a command line (with the "isql" command). But I still can't connect over remotely with my Windows 7 machine.