*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 | require_secure_transport = 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,
Access denied for user 'non_root_user'@'10.1.1.1' (using password: YES)
Multiple Server Block Web Apps Server > MySQL DB Connection (non-root & SSL) > Private IP > MySQL Server > Access Denied
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.
Unable to Connect to '10.2.2.2'
Access Denied
message.Access Denied Message
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?
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.
#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 thewp-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/
Hey @bobbyiliev
Thanks for the detailed response,
Yes, this works fine and I can run queries etc.
I have tried adding
define('DB_SSL', true);
I have also tried includingdefine('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
Both have not worked so far.
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.
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:
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: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:
Then drop all of the entries, for exmaple: