-3

This is my setup (the IP numbers are fictional of course):

Server A (10.0.0.1)

hosts a database called database1 with the user db_user and the password db_pass. This user has access to the database and remote connections from any host are permitted (I know it's a leak and I will fix it once it works)

Update 1: This server shows no signs of receiving the connection (like connection refused or something like that) Port 3306 is open

Server B (20.0.20.0) hosts a PHP script which connects to the database with the following command:

$connection = mysqli_connect("10.0.0.1","db_user","db_pass","database1",3306);

My log on Server B says:

Access denied for user 'db_user'@'20.0.20.0' (using password: YES) in <path-to-php-file> in line 42

The line number matches the statement, so it is indeed the statement above which fails.

Why? I explicitly specified the IP of Server A (also tried server-a.com instead of 10.0.0.1)

Update 2: I ran the following query via commandline as MySQL-Root and this is the output:

mysql> SHOW GRANTS FOR 'db_user';
GRANT USAGE ON *.* TO 'db_user'@'%' IDENTIFIED BY PASSWORD '<password hash>'
GRANT ALL PRIVILEGES ON `database1`.* TO 'db_user1'@'%'

Seems valid to me. What strikes me as odd is that in the log of B it shows it own address (B's address) instead of A's where the Database is located. My idea is it tries to connect to a database on server B where no MySQL user db_user exists.

Update 3: I connected via SSH to server B and ran mysql --host=10.0.0.1 -udb_user -p and typed in the password => it worked. SHOW GRANTS FOR current_user; returned the same like on server A.

DBX12
  • 2,041
  • 1
  • 15
  • 25
  • 1
    20.0.20.0 is the IP of the client that tries to connect. You don't have permissions to connect, remote connection is disabled or your authentication details are not correct. – Allkin Nov 01 '16 at 11:01
  • @Allkin please read my question completely. I stated, that on server A connections from ANY host are permitted. Username and password are copy-pasted so I don't expect any errors there. – DBX12 Nov 01 '16 at 11:05
  • Did you try without setting 3306, which seems to be default – RST Nov 01 '16 at 11:07
  • @RST I removed the parameter 3306 from the connect statement, still getting this log message. I added something about the logs on server A. – DBX12 Nov 01 '16 at 11:14
  • Try this query on the server: `SELECT host FROM mysql.user WHERE user = 'db_user'`. What does it return? – Barmar Nov 01 '16 at 11:15
  • The error message you got indicates that it connected to the server successfully, but the user was refused access because it doesn't have the appropriate `GRANT`s. Make sure you granted access to `db_user@%` or `db_user@20.0.20.0`. – Barmar Nov 01 '16 at 11:17
  • @Barmar I ran your command as root user of mysql on the command line. It said % if I'm not completely wrong this means "any host" – DBX12 Nov 01 '16 at 13:47
  • Based on your updates, the only thing I can think of is that you're sending the wrong password in the PHP code. AFAIK, there's no difference between the way mysqli connects to the database and the `mysql` CLI tool. But just for completeness, what happens if you try PDO instead of mysqli? – Barmar Nov 01 '16 at 14:59
  • I checked the password several times (also spaces at the beginning and ending) and copied into the CLI. I will try PDO, but it may take a while, I never worked with it before. – DBX12 Nov 01 '16 at 15:03
  • @Barmar What kind of sorcery is this?! It works with PDO. Please add all your statements as an answer, so I can mark it as accepted :) – DBX12 Nov 01 '16 at 15:26
  • Ugh, I really hate posting cargo-cultish answers like that. I like my answers to explain what's wrong and how the answer fixes it. Otherwise, it's of little use to future users. – Barmar Nov 01 '16 at 15:40

1 Answers1

1

If you can't connect using mysqli, try using PDO instead. I'm not sure why, but apparently in this case PDO works.

Personally, I like PDO better than mysqli, because of named parameters instead of ?, and the ability to provide an array of values when calling PDOStatement::execute(). You may find you like it as well.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I asked my hoster about this weird behavior of `mysqli`. Turned out, blocked mysqli is sold to be a "security feature". `PDO` works without problems. They probably messed with the mysqli library to send invalid credentials to the remote server but forgot about PDO. – DBX12 Feb 24 '17 at 07:05