MySQL User Permissions Error When Attempting Remote Connection

[archivist007]

I’m having problems with a web-server not being able to log in remotely to a database server. Both servers are running Ubuntu 8.04.2. The web-server is running Apache 2.2.8 and PHP5 and is located in our network’s DMZ, and the database server is running MySQL 5.0.51 in the trusted internal network.

I’ve opened the bind address on MySQL to allow for external connections. I can log in locally to MySQL on the database server using the database user. But I still can’t make a complete remote connection. Does anyone have any ideas as to what the problem might be?

Thanks.

[koenn]

you need to check the users in mysql. They're usually granted rights in the form
GRANT something TO 'user'@'computer' IDENTIFIED BY 'password'

the account your web app uses probably only has privs as ....@'localhost' but if your web server is remote to the database server, you'll need something like .... @'webserver' . You can try with ...@'%' (any host) also, for troubleshooting or testing.

[archivist007]

Thanks for the suggestion.

I have checked the user permissions. I tried it with both 'user'@'webserver' and 'user'@'%', and neither option resolves the issue. In MySQL CLI, the privileges table shows that the user has full permissions, but I still can't make the remote connection. The problem still persists even when I select "any hosts".

[koenn]

and you did 'FLUSH PRIVILEGES;' after those changes, to force mysql to load the new privileges ?

[archivist007]

Yes; I did flush permissions and I also restarted MySQL after each set of changes.

Thanks!

[koenn]

what do you get if you try to connect remotely with the webapp's mysql account, something like

mysql --host dbserveripaddress --user webappmysqlaccount -p

copy and paste the output, it may provide clues you yourself are not aware of

[dragos2]

Did you connected to mysql port from the inside of the dmz ?

[archivist007]

Thanks for the help!

I ran the command, substituting my configurations and got the following:

With the user set to 'user'@'webserverIP'
ERROR 1130 (00000): Host 'namedbserver' is not allowed to connect to this MySQL server

With the user set to 'user'@'%':
ERROR 1045 (28000): Access denied for user 'user'@'namedbserver' (using password: NO)

[koenn]

this is getting confusing. Can you post the actual commands you run ? and where you run them from ?

ERROR 1130 (00000): Host 'namedbserver' is not allowed to connect to this MySQL server
this looks like you ran it from the database server itself, and the database server refuses the connection. But you're interested in connections from other hosts than the db server, right ?

[archivist007]

Sorry for the confusion...

I just found the revised command (with the added -p option).

From the database server I ran the command:mysql --host dbserveripaddress --user webappmsyqlaccount -p
I get a password prompt; enter my password and was able to successfully login.


From the webserver I ran the same command:mysql --host dbserveripaddress --user webappmsyqlaccount -p
And I get a password prompt; enter my password and get the error:ERROR 2003 (HY000): Can't connect to MySQL server on 'dbserveripaddress' (111)
It was the correct password, and I tried it more than once to ensure there were not typos.

[koenn]

This is most likely a network problem, not a mysql problem ; the error no is in the 2000's which indiates it is generated by the mysql client, not the server, so most likely it's not an authentication problem.-, but something to do with network connectivity.

This could be anything from routing and general networking (can you ping, traceroute, .... between the two servers ? both directions ?), a firewall blocking the connection between the web server and the db server (does your firewall allow traffic coming from DMZ into your secure LAN ? - most DMZ setups try to avoid that), etc.
you should also check my.cnf again : there might be a line 'skip networking' there, you need to command that out (along with setting the bind address to a non-loopback, which you already did)

there's a FAQ about this, you might want to go through it if non of the above works
http://forge.mysql.com/wiki/Error2003-CantConnectToMySQLServer