Question

MySQL DB Connection Access Denied

Posted September 19, 2019 2.4k views
NginxMySQLPHPWordPressNetworkingFirewallDatabases

Environments:

*IP address shown are fictitious

App Server (Droplet) MySQL Server (Droplet)
Public IP: 153.1.1.1 Public IP: 153.1.1.1
Private IP: 10.1.1.1 Private IP: 10.2.2.2
Ubuntu 18.04 Ubuntu 18.04
NGINX 1.14.0 MySQL 14.14 Distribution 5.7.27
Lets Encrypt UFW Firewall
PHP 7.2.19 my.cnf: bind-address = 0.0.0.0
MySQL 15.1 Distribution 10.1.41-MariaDB requiresecuretransport = ON
UFW Firewall Client Connections via SSL Enabled (REQUIRE X509)
UFW Rules: OpenSSH, NGINX Full, MySQL OpenSSH, 443, MySQL, 3306 ALLOW IN 10.1.1.1
Non-Root User Created Non-Root User Created
0 Package Updates 0 Package Updates
Cloudflare Cache Disabled -

Hi DO Community,

Issue:

Access denied for user 'non_root_user'@'10.1.1.1' (using password: YES)

Scenario:

Multiple Server Block Web Apps Server > MySQL DB Connection (non-root & SSL) > Private IP > MySQL Server > Access Denied

I have a 2 droplet setup currently:

  • 1 x App Server
  • 1 x MySQL (DB) Server

App server is configured to host multiple websites using the NGINX server blocks.

MySQL (DB) Server will host the multiple databases required and ONLY be available via the Private IP (in this example: 10.2.2.2).

My full configuration was going swimingly until the very last hurdle, I setup 2 sites and configured a singel static HTML file to test and allow SSL setup.

I then also run a php file to check phpinfo(); output works and this also worked fine.

I then deployed one of my sites WordPress filesystems via FTP to the new vhost directory. this all went online fine.

I recreated the Database (I did change the name on import and in the wp_config.php file also)

To this point everything appeared to be going smoothly.

I fun terminal level ping tests
App Server: ping 10.2.2.2
MySQL Server: ping 10.1.1.1

Each returning packet responses as expected.

From the APP Server I then then executed a DB connection request using my non-root user and this connected fine and showed me the database and I could run sql queries as you would expect.

So at this point everything works.

Bearing in mind I have enabled DB user SSL based connections using ALTER USER 'non_root_user'@'10.1.1.1' REQUIRE X509;

This works at the terminal level perfectly as I mentioned above.

Its only when i then navigate to my Wordpress domain to check the DB connection is all working and should see my already developerd website, but instead i get:

Access denied for user 'non_root_user'@'10.1.1.1' (using password: YES).

I proceeded to restart the MySQL DB, NGINX, and Flushed all privileges also.

So at this point i am pretty much out of ideas.

Trouble shooting steps taken:

  • Fully disable UFW on both environments.
  • Changed nonrootuser host to be wildcarded (%) instead of IP specific.
  • Changed nonrootusers password to not included possible problematic symbols.
  • Created a Database Connection test PHP file, output: Unable to Connect to '10.2.2.2'
  • Enabled WP DEBUG to see the Access Denied message.
  • reviews all log files which all point to the same Access Denied Message

Solution (Pending):

8 Hours on and I am as confused as to why this i am getting access denied.
Any pointers or possible idea as to why this may be getting denied would be greatly appreciated?

Add a comment
8a857fad1ed5cb9737dcfe995c08ac499779eaf8
By:
DigitalKrate
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
3 answers
bobbyiliev September 20, 2019

Hello @DigitalKrate

From the networking side of things, it all looks correct to me. I think that the problem is with the MySQL user itself or Wordpress.

So just to confirm, are you able from your Webserver to connect to the MySQL server directly with:

mysql -u non_root_user -p -h 10.2.2.2

If this works for you then the user should be fine and the problem is with the Wordpress config. In this case, I think that you should just add this to your wp-config.php file:

define('DB_SSL', true);

If you are not able to connect to the MySQL server from the command line, then it is most likely the non-root-user that you’ve created.

I would suggest starring fresh by dropping all of the entries for the non-root-user and then re-creating it again. To check this just run this command as root:

SELECT user,host,password FROM mysql.user;

Then drop all of the entries, for exmaple:

DROP User 'non_root_user'@'localhost';
DROP User 'non_root_user'@'10.1.1.1';
...

Then re-create the user again:

CREATE USER 'non_root_user'@'10.1.1.1' IDENTIFIED BY 'password' REQUIRE X509;
GRANT ALL PRIVILEGES ON wp_db.* TO 'non_root_user'@'10.1.1.1';

Let me know how it goes!
Regards,
Bobby

Reply Report
DigitalKrate September 20, 2019

Hey @bobbyiliev

Thanks for the detailed response,

So just to confirm, are you able from your Webserver to connect to the MySQL server directly with:

Yes, this works fine and I can run queries etc.

If this works for you then the user should be fine and the problem is with the Wordpress config. In this case, I think that you should just add this to your wp-config.php file:

I have tried adding define('DB_SSL', true);
I have also tried including define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

Both have not worked so far.

I would suggest starring fresh by dropping all of the entries for the non-root-user and then re-creating it again. To check this just run this command as root:

I have not yet tried fully removing the user, I have attempted password changes / resets etc. but will try complete removal to see if that helps.

Thanks again @bobbyiliev for the response.

Reply Report
DigitalKrate September 20, 2019

Hooray.....

So I have pinpointed the issue, now looking to find a viable solution.

So Wordpress’s DB class (Need to check if its the most current version) does not listen for the DB_SSL constant and looking at the wp-db.php file, its clear the database class does not support the SSL connection either currently.

As soon as i disabled the need for the SSL DB connection its all working, so need to find a robust solution to the lack of DB_SSL connection availability in the version of Wordpress I am using and look to upgrade it if not already latest. First thing will be to check the current installed version of WP clearly and upgrade respectively.

I have come across this blog post on the topic but need to review the code first: https://blog.slowb.ro/enable-ssl-database-connections-for-your-wordpress-installation/

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help