remote access to MySql

Support for security such as Firewalls and securing linux
jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

remote access to MySql

Post by jrgt_1976 » 2018/01/17 22:59:11

Hi, any suggestions about proper setup on my.cnf file to allow me remote access. I want to access through Navicat.

The port 3306 is already open and the rule in firewalld is declared to allow connections.

What am I missing?

Thanks.

User avatar
TrevorH
Forum Moderator
Posts: 22753
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: remote access to MySql

Post by TrevorH » 2018/01/17 23:32:42

CentOS ships mysql/mariadb server packages that only listen on localhost by default. You need to tell it to bind to a different address in my.cnf and restart.

Are you sure you can't just tunnel port 3306 via ssh and connect like that remotely? Safer all round as the port will not be open to the outside world - your firewall-cmd rules should only allow access to specified ip addresses or subnets or you'll have the entire world hammering on the port trying to brute force their way in).
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

Re: remote access to MySql

Post by jrgt_1976 » 2018/01/18 01:28:44

TrevorH wrote:CentOS ships mysql/mariadb server packages that only listen on localhost by default. You need to tell it to bind to a different address in my.cnf and restart.

Are you sure you can't just tunnel port 3306 via ssh and connect like that remotely? Safer all round as the port will not be open to the outside world - your firewall-cmd rules should only allow access to specified ip addresses or subnets or you'll have the entire world hammering on the port trying to brute force their way in).
Hi Trevor. I'm sure I can't becasuse it hasn't worked. Locally I have no problem at all. Already declared a specific IP on firewalld but, my.cnf file isn't modified to accept any other host or user.
I already took a dive on My SQL docs but can't find the procedure to add host and user and avoid "Host ... is not allowed to connect to this MySQL server" message.
All I got is default values that I get by typing mysql -u -p --execute="SELECT User, Host FROM mysql.user The question is, how to add user and host? by command line, editing my.cnf file. :roll:

jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

Re: remote access to MySql

Post by jrgt_1976 » 2018/01/18 02:46:44

I think I've found something intersting about it.
I just logged in via phpMyAdmin (on localhost) and created an account with different parameters.
Everything seems to be working fine.

I just have one tiny doubt, if I choose to use SSL, what do I need to do to get it done?

User avatar
TrevorH
Forum Moderator
Posts: 22753
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: remote access to MySql

Post by TrevorH » 2018/01/18 07:41:52

I already took a dive on My SQL docs but can't find the procedure to add host and user and avoid "Host ... is not allowed to connect to this MySQL server" message.
That means the port is open and you have connected but the request fails mysql authentication. This is exactly what I meant about hackers being able to try to brute force their way in - the port is open and anyone can connect unless you have restricted the iptables rules to only allow your own specific ip addresses.
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

Re: remote access to MySql

Post by jrgt_1976 » 2018/01/18 21:23:27

Alright, got it. What would you suggest to be the best case scenario?

I'm using firewalld instead of iptables (wich method is safer?). Applied a rule where only connections from my IP address are allowed (for now I'm just testing with this virtual server inside my LAN). But, eventually will need a few stations from the outside to connect to port 3306. What could be the solution to protect the port? To encrypt incoming connections?
I need to find out the station's IP addresses in order to add them to firewalld, but in most cases those are dynamic and change from time to time.
By now, only ports 80, 22 and 3306 are open on my firewall (ISP modem-router) and centos firewalld is up.

Probably, an ethical hacker could help me out to improve my server security :D . As always, thank you very much for your comments.

User avatar
TrevorH
Forum Moderator
Posts: 22753
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: remote access to MySql

Post by TrevorH » 2018/01/18 22:22:13

One way to do it is to disallow external connections and connect using ssh and create an ssh tunnel. For example, from a linux system you'd ssh -L:6033:127.0.0.1:3306 your.mysql.server.ip.address which would create an ssh session for you and also open port 6033 on your own machine's localhost address and tunnel it so that the other end of the tunnel connects to 127.0.0.1:3306 on your.mysql.server.ip.address. You then connect to 127.0.0.1:6033 and the connection emerges from the other end of the tunnel and connects to 127.0.0.1:3306 on the remote server. All the data sent is then also encrypted.

Using ssh can be made more secure even if the port is wide open to the world. You can disable password authentication and create an ssh private/public key pair with a strong passphrase and connect using that. Now a hacker has to steal your private key file and its passphrase to be able to connect to your server...

I'm no firewalld expert, it just seems lie a bloated pig of an app to me and one that succeeds only in making setting up firewall rules more complex than the thing it is trying to replace. I don't use it. I suspect you can tell it to restrict port 3306 connections to particular ip address(es) and/or ipsets.
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

Re: remote access to MySql

Post by jrgt_1976 » 2018/01/18 22:56:20

Great. I 'll keep that in mind.

jrgt_1976
Posts: 19
Joined: 2017/12/23 07:42:05
Location: Mexico

Re: remote access to MySql

Post by jrgt_1976 » 2018/02/22 04:11:58

Well, after a long long work around I fanally secceeded on connecting thru ssh to MYSQL server. Now I have a strange issue.

One user can connect from a MAC system using SSH and a security certificate. I, inside my LAN from a Windows system, can access without any problem. The issue is with a third person, outside my network, with a Windows system using same settings the MAC user has.
He is unable to gain access even when all parameters used are the same.

Although the user is unable to connect to MYSQL even when the password of the MYSQL user is provided, he can login onto my system using the same sec certificate and passphrase. That means he can login using private key, passphrase via putty, but, when it comes to MYSQL, the connection cannot be established. Lost connection to MYSQL server at 'reading initial communication packet' message appears.

I'm using version 5.6.39 (GPL) and I just found out abour this issue with my virtual server. If it wasn't for this third user that needs to connect I wouldn't noticed it.

Any suggestions on what I need to change on my.cnf, hosts file to get this fixed?

aks
Posts: 2524
Joined: 2014/09/20 11:22:14

Re: remote access to MySql

Post by aks » 2018/02/25 14:22:04

1) SSL on MySQL

MySQL can be compiled to (natively) accept SSL connections (not sure if your is, so you'll have to check). How to do it: https://www.percona.com/blog/2013/06/22 ... nnections/ and another: https://mysql.az/2015/04/11/activate-ss ... ion-mysql/. Another interesting post of different SSL DB connection setups: http://databaseblog.myname.nl/2015/01/d ... mysql.html. Debu8gging SSL connections: https://www.percona.com/blog/2012/11/08 ... -problems/

2) Connectivity
"inside my LAN from a Windows system, can access without any problem. The issue is with a third person, outside my network"
Perhaps a NAT problem?
"he can login onto my system using the same sec certificate and passphrase"
or perhaps not. Perhaps a port problem, or a combination of the two? Anyway with MySQL the accounts are stored in the mysql database (schema). Authentication has three factors:
a) user name
b) password
c) host
When you connect to MySQL you need to satisfy those contitions from the MySQL server's viewpoint (especially the host anme and/or ip address used). A lot of people use the wildcard (%) in the hosts part just to say from anywhere. I consider that bad practise, but you can use it proive a point I guess.

Post Reply