User can't access external Mysql server. Root can.

[freakazoid]

Hi all,

I'm trying to connect to an external MySQL cluster and am running into a strange issue. When I want to test the connection and connect in CLI to it as root using this it works nicely;

mysql -u username -p -h 123.123.123.123

But as a directadmin user it give me this error;

ERROR 2002 (HY000): Can't connect to server on '123.123.123.123' (115)

I have no additional firewall stuff, no CSF etc.


# iptables -L

# Warning: iptables-legacy tables present, use iptables-legacy to see them
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)

nmap give me this;

nmap -n -Pn -p 3306 123.123.123.123
Starting Nmap 7.80 ( https://nmap.org ) at 2022-11-18 20:31 CET

Nmap scan report for 123.123.123.123
Host is up.

PORT STATE SERVICE

3306/tcp filtered mysql

What is blocking the traffic for the user? I'm out of ideas.

Thanks for the help!

 

[Richard G]

Check your my.cnf file (probably in the /etc directory) if it contains a string like this:
bind-address=127.0.0.1
also check the permissions for the user. Normally they are bound to localhost.

Login locally as mysql root user to mysql/mariadb and issue this command:
SELECT user,host FROM mysql. user;
You might see a list like this, because this is how they are created by default for security reasons obviously:

| nicole           | localhost |
| root             | localhost |
| foo              | localhost |
| john             | localhost |

which means they are only allowed to login to mysql locally. You have to change that too for them to be able to access remotely.

 

[freakazoid]

Hi Richard,

That's not the issue. I can't access an external database.

Directadmin webserver -> external DB server.

From the Directadmin server I can connect to the external DB as the directadmin root user. But not as the directadmin regular user.

So the user I create in DA can't access the external database with the exact same command the DA root user can. So it's not related to the database server I'm trying to connect to, but it needs to be some filtering that is done on the DA side is my best guess.

 

[Richard G]

From the Directadmin server I can connect to the external DB as the directadmin root user. But not as the directadmin regular user.

If it's an external server and you can access it as the DA root user, it is accessible. So most likely not a DA issue.
The mysql command does not care which database it connects to. Did you try the command I gave you on the external database?

By the way, how come the DA root user can access the external database? Did you set it up with the same root user and pass?

 

[johannes]

There is a setting in DA, in the database management page, to define "Access-Hosts".

 

[freakazoid]

@johannes , you mean to manage the acces to databases on the DA host? But that is not what I'm trying to do. I want to connect to an external database cluster.

@Richard G this is what is happening. The following cli is from the DA host (ws17) connecting to the external DB 123.123.123.123 (example IP)

root@ws17:~# mysql -u demo -pP@55w0rd -h 123.123.123.123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
root@ws17:~# su demouser
demouser@ws17:/root$ mysql -u demo -pP@55w0rd -h 123.123.123.123
ERROR 2002 (HY000): Can't connect to server on '123.123.123.123' (115)
demouser@ws17:/root$

As you can see on the DA server the root user can connect to the external DB and with exactly the same credentials the "demouser" can't

When I check with nmap it look likes something is blocking the traffic of the demouser

root@ws17:~# nmap -n -Pn -p 3306 123.123.123.123
Starting Nmap 7.80 ( https://nmap.org ) at 2022-11-19 10:23 CET
Nmap scan report for 123.123.123.123
Host is up (0.00074s latency).

PORT STATE SERVICE
3306/tcp open mysql

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds
root@ws17:~# su demouser
demouser@ws17:/root$ nmap -n -Pn -p 3306 123.123.123.123
Starting Nmap 7.80 ( https://nmap.org ) at 2022-11-19 10:23 CET
Nmap scan report for 123.123.123.123
Host is up.

PORT STATE SERVICE
3306/tcp filtered mysql

Nmap done: 1 IP address (1 host up) scanned in 2.03 seconds
demouser@ws17:/root$

 

[Richard G]

this is what is happening.

Now I understand what you mean.
As root user, you can connect with any accountname's credentials, so the mysql settings are correct.
But when really trying as the accountname itself, the connection is blocked.

And that is indeed what the nmap command shows too.
Never seen that one before.

# iptables -L

Is that the same result on both servers? Or from which server was that output?

 

[freakazoid]

The DA server has this as output of iptables -L

root@ws17:~# iptables -L

# Warning: iptables-legacy tables present, use iptables-legacy to see them

Chain INPUT (policy ACCEPT)

target prot opt source destination

Chain FORWARD (policy ACCEPT)

target prot opt source destination

Chain OUTPUT (policy ACCEPT)

target prot opt source destination

It looks like port 3306 is filtered by DA.

When I do nmap on port 22 the connections show in both cases as open.

 

[Richard G]

It looks like port 3306 is filtered by DA.

No by the firewall, that's showing ACCEPT.

Do you have anything maybe like mod_security or some other kind of protection present?

Maybe more important, just came into my mind.
Does the user which needs that external mysql access have SSH permissions on DA?

 

[freakazoid]

The user has SSH permission and is not jailed.

Mod_security is an Apache module, that's irrelivant because it doesn't work in CLI. Something is blocking the network traffic of this user, and I have no idea on where to look. With over 10 years of running DA servers that's a first ;-)

 

[zEitEr]

Can the user have a custom version of mysql binary?
Can the user connect to another remote MySQL server under your control?
You might try and see how traffic goes with tcpdump on a source and target server.

 

[freakazoid]

They use the same binary. I'll try a differt DA server and see if I have the same issue there

root@ws17:~# which mysql
/usr/local/bin/mysql
root@ws17:~# su demouser
demouser@ws17:/root$ which mysql
/usr/local/bin/mysql

 

[freakazoid]

Well it's fixed.

It worked after I rebooted the machine (the windows fix)...

Not sure what fixed it but I did the following; I tested another DA server and that worked without an issue. So I thought it might have to do that the user I was testing was a migrated user from another server. I created a package and a new user, that also didn't work. Toggle the "jailed" setting of both users, perhaps it was jailed but showing incorrectly ?‍... all didn't work so I rebooted to have everything in a normal state after fiddling around for over a day...

So not sure what caused it or fixed it, but these were the last changes might anyone ever run into something like this.

Thanks for the help all.

 

[Richard G]

Mod_security is an Apache module,

Sorry, I was messing up names. I ment selinux. Had a memory leak.

Sometimes rebooting can fix certain things, I'm not sure why either, however, glad to hear it works now. Thanks for reporting back.