Problem Description
- You have a VPS. (Remote database connections are not allowed on shared hosting for optimal security)
- You are attempting to remotely access your database, either with a database management utility on your computer or to connect a website on another server.
- The connection never reaches the authentication stage, with it flat out denying the connection well before that point
Important: if your SQL application supports using an SSH tunnel, you should opt to connect via SSH tunnel for a fully secured end-to-end connection, then enter your SQL details with localhost (the SSH tunnel makes you local to the server). This also ensures you can avoid the following steps.
Problem Resolution
There's a few areas that you must enable remote access for a remote connection to work successfully.
SQL server global settings & individual user permissions
If you are using Plesk control panel
- Login to Plesk as admin, then go to Tools & Settings > Database Servers
- Select "localhost" then check the box to "Allow local MySQL server to accept external connections" and save changes.
- Select "Settings" (button) and select the option "Allow remote connections from", then enter the IP addresses that are allowed to access the database server. You can choose the option for any host as long as you have limited it using your firewall (see below)
- Go to the database you're connecting to and click on the user you'd like to allow access to
- Under Access Control, enable remote access. You can choose the option for any host as long as you have limited it by IP either in the global setting above, or in the firewall (see below)
If you are not using Plesk
Check the MariaDB or MySQL configuration files (/etc/my.cnf or any of the files in /etc/my.cnf.d/) for the skip-networking directive and comment it out, then restart the mysql service like `systemctl restart mysql`. This does the same thing as the Plesk setting above.
Also check to be sure that your individual SQL user has remote access enabled.
Be sure to use a Firewall
Now that you've opened up your database server as a potential attack vector, it's imperative that you also block port 3306 in your firewall by default, then whitelist only specific IPs to allow access to the service. We show how to do this in our documentation here.
Note: for optimal security port 3306 inbound is blocked in your firewall when we apply our speed & security optimization (also included with Hands-On Support).
