Mariadb Remote Access - no route to host error

[Vipul.K]

SYSTEM INFORMATION
OS type and version	Ubuntu 20.04
Webmin version	2.111

I have 2 servers both running Virtualmin and I’m trying to access a database on Server A from Server B. On Server A, I selected the appropriate server, then went to Edit Database > Remote Access and added the IP address of Server B.

Then I created a simple php script on Server B to try and connect to the database. I used the IP address as host. But I’m getting this error in apache’s access logs:
AH01071: Got error 'PHP message: PHP Fatal error: Uncaught mysqli_sql_exception: No route to host in /home/domain/public_html/lab.php:8

Is there anything else that needs to be done? I can’t find a good tutorial on how to make this work.

[jimr1]

it may be worth setting maria like this

and also make sure you have the mariadb port (3306 by default) open in your firewalld configuration and if you have a provider firewall open the port there also

[Vipul.K]

Nice. Doing both of those things made something happen. I get this error now:

AH01071: Got error 'PHP message: PHP Fatal error:  Uncaught mysqli_sql_exception: Access denied for user 'essl'@'server2's IP' (using password: YES) in /home/domain/public_html/lab.php:8

Why do the logs mention server 2’s IP. This is the server I’m sending the request from. Here’s my PHP code:

$host = "Server1's IP";
$database = "domain";
$username = "essl";
$password = "password";

$connect = mysqli_connect($host, $username, $password, $database);
mysqli_set_charset($connect, 'utf8mb4');
if($connect != true){
	echo "Could not connect to Database. Aborting.";
} else
	echo 'connected';

[jimr1]

Does that user exist no the server you are trying to connect too ?

[Vipul.K]

Yes.

Isn’t it weird that the logs are showing server 2’s IP. Shouldn’t it be server 1’s IP there?

I turned off Server 1 and then I get “Connection refused” error so the requests are being sent to Server 1. They are probably not getting interpreted the right way.

[jimr1]

That should work which sever log is showing this error and on which server ?

[Vipul.K]

The error is on Server 2 in apache error log.

Is there any log I can check on server 1 that would list mariadb’s connection attempts from server 2.

[Vipul.K]

I tried this command from SSH

 telnet SERVER.1.IP 3306

Got this response, I’m not sure if this the appropriate response.

Trying SERVER.1.IP...
Connected to SERVER.1.IP.
Escape character is '^]'.
5.5.5-10.6.18-MariaDB-ubu2004-log▒▒VS!EzZ0"▒▒-WoKgGkc2kFZqmysql_native_passwordConnection closed by foreign host.

[jimr1]

Yes it is what you would expect, and just making sure server 1 is the database server, the connection error, I ran your code but rather than IP I used the hostname (I could not remember the IP address) and it worked fine. You have set the user essl to be able to connect from different IP addresses ? on server 1 go to webmin → servers → maria database server → User Permissions click the essl user and change the settings there or you can make a new user with the same name if you wish and make the settings something like this

Ignore the warning at the top, make sure any is active (Virtualmin created users will have this set to localhost ). As this is a remote user you may wish to set permissions to exactly what you want the remote user able to do. Last thing worth considering is enabling fail2ban’s mysql jail on server 1 with some fairly strict rules just to keep the hackers out of the database

[Vipul.K]

Yes Server 1 or Server A has the database I want to connect to. Server 2 or Server B is what I’m connecting from. I have tried “Any” host but get the same error.

I want to post again what I’m doing.

I have 2 servers both running Virtualmin and I’m trying to access a database on Server A from Server B.

1. On Server A, I selected the appropriate server say callingmaria.com
2. Then went to Edit Database > Remote Access and added the IP address of Server B.
3. Next I created a new user from Edit Users. I don’t have Pro version so can’t create database user. I pressed “Add User” and here are the settings I chose

Now in Webmin > Servers > MariaDB Servers > User Permissions I have 2 new users for a total of 4:
i. essl@callingmaria.com, Host: Server B’s IP
ii. essl@callingmaria.com, Host: localhost
iii. callingmaria, Host: localhost
iv. callingmaria, Host: Server B’s IP

4. On Server B, I created a simple php script to try and connect to the database. I used the IP address as host. Here’s the code:

$host = "SERVER.A.IP";
$database = "callingmaria";
$username = "essl@callingmaria.com";
$password = "HExEqRykWLSXJ7R";

$connect = mysqli_connect($host, $username, $password, $database);
mysqli_set_charset($connect, 'utf8mb4');
if($connect != true)
	echo "Could not connect to Database. Aborting.";
else
	echo 'connected';

But I’m getting this error in apache’s access logs:
Access denied for user 'essl@callingmaria.com'@'SERVER.B.IP' (using password: YES)

Is there anything else that needs to be done? That Server.B.IP in the log bothers me. Shouldn’t it be Server.A.IP?

[Vipul.K]

Nevermind. It worked somehow.

I deleted everything and redid everything while writing the above post and it worked. Not sure if it was a glitch or something.

[jimr1]

@Vipul.K

Next I created a new user from Edit Users. I don’t have Pro version so can’t create database user. I pressed “Add User” and here are the settings I chose

go to webmin → servers → maria database server → User Permissions click the essl@callingmaria.com user and change the permissions from there
you need to set

• select table data
• insert table data
• update table data
• delete table data
• create tables
• drop tables
• manage indexes
• alter tables
• create temp tables
  rather than the default
  afterwards you should see something like
  
  
  
  then retry your script

[Vipul.K]

Thanks. I only selected Insert table data because that’s all this users needs to be able to do.