Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am in the process of running a php script on a webserver at a remote site (I'll call SiteB) that connects to a MySql server at a remote location (I'll call Site A). Now, the SQL server at Site A has 2 users, one named 'usr'@'192.168.1.%' for access from within the internal network at Site A, and one named 'usr'@'client.siteB.com'.

The server at Site A is behind a router, and has all TCP and UDP traffic on port 3306 forwarded to 192.168.1.12 (the local IP of the MySQL server at Site A). Site A has a dynamic IP, all references to the SQL server are initiated via hostname (we'll call server.siteA.com). Site B does have a static external IP, but all SQL connections that are coming in are refered to by hostname only, thus why the SQL user is 'usr'@'client.siteB.com'.

The issue I am having is that whenever I run the php script from the webserver on site B, I get the following error:

Access denied for user 'usr'@'client.siteB.com' (using password: NO)

It also references a line, which is:

$this->MCdbConnection = mysql_connect($this->MySQLhost, $this->MySQLuser, $this->MySQLpassword);

So, as you can see I am passing in a password. The strangest part is that by running that same script on the local network at Site A and changing nothing but the variable $MySQLhost from server.siteA.com to 192.168.1.7, the script works fine, and connects and selects and inserts data fine.

An error like this usually makes me think that it's a username issue, but both MySQL users ('usr'@'192.168.1.%' and 'usr'@'client.siteB.com') are completely identical, and both have the same password and privileges (SELECT, INSERT, and UPDATE only).

I also got the same error while attempting to connect via MySQL Workbench and the command line client from another machine on the local network at Site A when I dropped the user 'usr'@'192.168.1.%'. All signs point to a missing, bad, or under-authenticated user but I know the user is there and it has the proper permissions, the only thing that makes me suspicious is that it uses a hostname instead of an IP Address.

I've also verified that I have the following IPTables rule set on the MySQL server at SiteA:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

I also can and have run similar mysql connections via PHP on the same server at Site B, but they all connect to localhost.

Also, for good measure, SQL server at SiteA's my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
general-log
expire_logs_days = 60

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have also tried changing the host of 'usr'@'client.siteB.com' to '%' but I am still getting the same error. Also, I am running the MySQL server on Fedora and I do have selinux set to permissive.

Also, I tried changing the from the hostname to the ip of Site B in the MySQL user's host, but I still get the same error. However, all of the error I've seen see to it refered to it as "client.siteB.com", even when I do a tcpdump on SiteA I see traffic coming in from the source "client.siteB.com"

I know that SQL Access Denied errors are very common and have a multitude of root problems, but none of the topics I've seen on so far on SF seem to resolve this issue and I'm completely stumped. Also, my google-fu has failed me this time. :(

share|improve this question

2 Answers 2

That error isn't the one that comes out of mysql_connect, in my experience; it's the one that comes from the mysql command line client. Is your PHP script shelling out to the command line at some point?

share|improve this answer
    
It's just a generic MySQL error that the MySQL client is displaying because it died on that statement and it calls mysql_error() if the variable == false. I also got the same error while attempting to connect via MySQL Workbench and the command line client from another machine on the local network at Site A when I dropped the user 'usr'@'192.168.1.%'. All signs point to a missing, bad, or under-authenticated user but I know the user is there and it has the proper permissions, the only thing that makes me suspicious is that it uses a hostname instead of an IP Address. –  Ballistic Buddha Aug 14 '11 at 7:15
    
If you are using port forwards, depending on how they are done there's a slight chance that the IP address MySQL sees is the IP address of the router,in which case your permissions are messed up. However, for a more in-depth clarification, enable all logging on mysqld and see what goes where. Also, using tcpdump on the machine with mysql might aid –  O G Aug 14 '11 at 8:12
up vote 1 down vote accepted

So, I've figured out the problem, and it turns out it was a very oblivious mistake in my php script. So, on the webserver at SiteB, the script takes variables that store connection info from a separate file, and that file originally had collisions with the variable names within my script after I did an include. This file does not exist in my test envirionment on SiteA, so All of the variables for user, schema, host, and password were being overridden by other connection information that I was storing in the same file for a SQL connection to localhost that uses a different password. So, I had to change the variable names within the script and I had missed one variable (the password one), so basically it was using the other password when it was at SiteB but used the correct password when it was at siteA.

So, yeah, after fixing that variable name the connection is now getting established and it now getting correctly authenticated, I'm just having errors when selecting the default schema for some reason, but that seems to be an unrelated issue. Thanks for the help to those that gave input though.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.