MySQL: Access denied for user 'user'@'IP_ADDRESS' - Remote access allowed for some hosts fails for other hosts

Question

Background: I am able to access a mysql instance from some subnets but not others. At least that is what it appears to be. Machines on network 10.0.21.xx are able to connect to the MySQL instance. But from 10.0.7.xx, I get "Access denied for user using password". Interestingly, MySQL is installed on the same subnet (10.0.7.xx) as the machines that it will not authenticate users from.

Here is the command that I issue.

 mysql -u user -h 10.0.7.21 -p

And the error I get is

ERROR 1045 (28000): Access denied for user 'user'@'10.0.7.30' (using password: YES)

I have already checked the /etc/mysql/my.cnf file and made sure that mysqld listens from all IP addresses. I have also checked grant privileges to make sure it is not tied to specific ip addresses.

I'd appreciate if someone can give me additional insight. If you need me to add extra information kindly let me know.

Thanks for reading.

Answer 1

It turns out it was a DNS issue. What led me to go this route is the MySQL Documenation:

If you specify a host name when trying to connect, but get an error message where the host name is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:

One of the solutions it offers is to flush the DNS host cache; in my case I didn't have this table.

Here are recommended fixes from the documentation:

Some permanent solutions are:

• Determine what is wrong with your DNS server and fix it.

• Specify IP addresses rather than host names in the MySQL grant tables.

• Put an entry for the client machine name in /etc/hosts on Unix or \windows\hosts on Windows.

• Start mysqld with the --skip-name-resolve option.

• Start mysqld with the --skip-host-cache option.

In my case I added the IP address of our local DNS server to the client's /etc/resolv.cnf file and then restarted network services (service networking restart).