CODEWITHSUNDEEP
mysqllinuxapachemariadb
sveti petar
sveti petar

Reputation: 3787

MariaDB either not allowing host to connect or access denied for user

I have a Laravel project working fine on my local machine, but unable to connect to database on another Linux server.

The error I get is:

SQLSTATE[HY000] [1045] Access denied for user 'cpanel1_thewatcher'@'localhost' (using password: YES)

These are the settings I am using:

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=dbnamehere
DB_USERNAME=cpanel1_thewatcher
DB_PASSWORD=passwordhere

I have checked to make sure cpanel1_thewatcher has ALL PRIVILEGES on this database and that the password is correct.

This is on a VPS, where I have root access, and it has two IPs dedicated to it. The main IP we will call xxx.xxx.xx.165 and the second one xxx.xxx.xx.166. If I change the settings above to set DB_HOST to xxx.xxx.xx.166 I get a different error:

SQLSTATE[HY000] [1130] Host 'xxx.xxx.xx.166' is not allowed to connect to this MariaDB server

If I set DB_HOST to xxx.xxx.xx.165 then I get yet another error:

SQLSTATE[HY000] [1045] Access denied for user 'cpanel1_thewatcher'@'server1.domain.com'

If I set DB_HOST to domain.com (the main domain on the server) I get the same error I get with DB_HOST=xxx.xxx.xx.166.

I have examined the mysql.user table on the server and found the following:

+-------------------+----------------------+
| User              | Host                 |
+-------------------+----------------------+
| root              | 127.0.0.1            |
| cpanel1            | xxx.xxx.xx.165       |
| cpanel1_thewatcher | xxx.xxx.xx.165       |
| root              | ::1                  |
| cpanel1            | localhost            |
| cpanel1_thewatcher | localhost            |
| nagios            | localhost            |
| root              | localhost            |
| cpanel1            | server1.domain.com |
| cpanel1_thewatcher | server1.domain.com |
| root              | server1.domain.com |
+-------------------+----------------------+

I have tried creating an entry in the table above where user is cpanel1_thewatcher and host is % but no effect. I have also tried setting DB_USERNAME to just thewatcher but nothing changes in the error messages.

I have seen some questions on SO referring to the second error message I listed, but I have been unable to find a MariaDB configuration file anywhere on my server as suggested by some answers there.

Doing GRANT ALL PRIVILEGES ON dbnamehere.* TO 'cpanel1_thewatcher'@'localhost'; gives

Query OK, 0 rows affected (0.00 sec)

Can anyone tell me what to do, I am confused with the 2 IPs and 3 different error messages here, and not sure what else to try. I'd like to eliminate any issues on my end before I resort to contacting the hosting company and waiting for a week for them to resolve it.

Upvotes: 0

Views: 5619

Answers (1)

Hackerman
Hackerman

Reputation: 12305

You need to follow this steps in order to connect to your remote database:

In your database server, run the following command in your mariadb console:

CREATE USER 'cpanel1_thewatcher'@'xxx.xxx.xx.166';

Followed by:

GRANT ALL PRIVILEGES ON dbnamehere.* TO 'cpanel1_thewatcher'@'xxx.xxx.xx.166';
FLUSH PRIVILEGES;

Where xxx.xxx.xx.166 is the ip of the machine you are trying to connect (where your laravel project lives)

Upvotes: 1

Related Questions