Why can't I connect to a remote MySQL server from a PHP application, but can connect from mysql-client

October 14, 2019 442 views
Ubuntu 18.04 MySQL PHP

Hi! I’m bamboozled.

Here’s the setup:

  • One application server, Ubuntu 18.04 LTS (PHP 7.2, mysql-client)
  • One database server, Ubuntu 18.04 LTS (mysql-server 5.7)

I need to connect to the database from a PHP application running on the application server. Everything’s set up. Notably, both PHP and MySQL are installed from the stock packages available in Ubuntu current LTS, which means they are PHP 7.2, and MySQL 5.7.

For the sake of example, let’s say that the IP address of the application server is 20.20.20.20, and the database server is 20.20.40.40. They’re connecting over a DO private network.

Here’s what happens:

  • SSH into application server
  • issue mysql -u argonflowers -p -h 20.20.40.40
  • am prompted for password, enter it
  • am presented with a mysql prompt. I’ve successfully accessed the remote database. I can query everything I need to in the databse, it’s confirmed working, etc. I kill this SSH session.

But:

  • SSH into application server
  • run a PHP script that I will copy at the bottom for the sake of keeping this list organized, run with command php -f db-test.php
  • returns with error: PHP Warning: mysqli_connect(): (HY000/1045): Access denied for user 'argonflowers'@'20.20.20.20' (using password: YES) in /home/argonflowers/db-test.php on line 10
  • K. What the heck. Let’s look at the MySQL server logs. They read: 2019-10-14T02:17:42.923737Z 27 Connect argonflowers@20.20.20.20 on using SSL/TLS 2019-10-14T02:17:42.924714Z 27 Query select @@version_comment limit 1 2019-10-14T02:17:44.503559Z 27 Quit 2019-10-14T02:17:51.359508Z 28 Connect argonflowers@20.20.20.20 on using TCP/IP 2019-10-14T02:17:51.359681Z 28 Connect Access denied for user 'argonflowers'@'20.20.20.20' (using password: YES)

What? This doesn’t make any sense to me. This seems like a “you are sending me the wrong password” error, but the PHP script contains the same password as I enter when prompted by mysql-client. Yes, SSL is enabled, but it’s not being enforced for all incoming connections. I’ve dug around a lot to find out if there are any known gotcha’s in how password hashing happens between PHP 7.2 and MySQL 5.7, but there seemingly aren’t. Also, these are the packages that are default to this distro, so I’m extra surprised it’s this much of a struggle. Please tell me I’m a fool and I’ve missed something obvious!

Here’s the PHP script:

<?php
# Fill our vars and run on cli
# $ php -f db-connect-test.php

$dbname = 'inventory';
$dbuser = 'argonflowers';
$dbpass = 'otkgjazmcutrls';
$dbhost = "20.20.40.40";

$link = mysqli_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
mysqli_select_db($link, $dbname) or die("Could not open the db '$dbname'");

$test_query = "SHOW TABLES FROM $dbname";
$result = mysqli_query($link, $test_query);

$tblCnt = 0;
while($tbl = mysqli_fetch_array($result)) {
  $tblCnt++;
  #echo $tbl[0]."<br />\n";
}

if (!$tblCnt) {
  echo "There are no tables<br />\n";
} else {
  echo "There are $tblCnt tables<br />\n";
} 
?>
1 Answer

Hello,

This is quite interesting. As you are able to connect to the database with the mysql command, my guess would be that the password that you are using might not be correct, does your password have any special characters?

Another thing that you could check is that your user is actually authorized to access the database from your app server:

SELECT user,host FROM mysql.users;

You should see your app server’s IP there.

Another thing that you could check is that your user actually has privileges to access the database in question:

SHOW GRANTS FOR argonflowers@20.20.20.20;

Let me know how it goes.
Regards,
Bobby

  • The password does not have any special characters – like in the example above, it’s 14 characters long and is all lowercase letters.

    SELECT user,host FROM mysql.user; gives us:

    +------------------+--------------+
    | user             | host         |
    +------------------+--------------+
    | argonflowers     | 20.20.20.20  |
    | debian-sys-maint | localhost    |
    | mysql.session    | localhost    |
    | mysql.sys        | localhost    |
    | root             | localhost    |
    +------------------+--------------+
    5 rows in set (0.00 sec)
    

    SHOW GRANTS FOR argonflowers@20.20.20.20; gives us:

    +----------------------------------------------------------------------+
    | Grants for argonflowers@20.20.20.20                                  |
    +----------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'argonflowers'@'20.20.20.20'                   |
    | GRANT ALL PRIVILEGES ON `example`.* TO 'argonflowers'@'20.20.20.20'  |
    | GRANT ALL PRIVILEGES ON `events`.* TO 'argonflowers'@'20.20.20.20'   |
    | GRANT ALL PRIVILEGES ON `inventory`.* TO 'argonflowers'@'20.20.20.20'|
    +----------------------------------------------------------------------+
    

    This database was set up using this DO guide, skipping Step 6 — (Optional) Configuring Validation for MySQL Connections.

    The problem is reproducible with two completely fresh Ubuntu 18.04 LTS droplets.

    My current my.cnf looks like this:

    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/
    
    [mysqld]
    # Require clients to connect either using SSL
    # or through a local socket file
    #require_secure_transport = ON
    bind-address = 0.0.0.0
    

    And the firewall is blocking incoming connections from all but the application server.

    Thank you so much for looking at this! :)

    by Justin Ellingwood
    by Mark Drake
    If you need to access your MySQL database from a remote location, it's important that you do so securely. This guide will demonstrate how to configure MySQL on Ubuntu 18.04 to accept remote connections with SSL/TLS encryption.
    • Hello,

      Thank you for the clarification, I think the problem here would be the SSL connection not being set in your PHP script. As you’ve set up SSL for MySQL you would also need to specify this in your PHP MySQL connection.

      With mysqli you can use the mysqli_ssl_set() funciton.

      Here’s an example:

      <?php
      $con=mysqli_init();
      if (!$con)
        {
        die("mysqli_init failed");
        }
      
      mysqli_ssl_set($con,"key.pem","cert.pem","cacert.pem",NULL,NULL);
      
      if (!mysqli_real_connect($con,"localhost","my_user","my_password","my_db"))
        {
        die("Connect Error: " . mysqli_connect_error());
        }
      
      // Some queries...
      
      mysqli_close($con);
      ?>
      

      Hope that this helps and let me know how it goes!
      Regards,
      Bobby

Have another answer? Share your knowledge.