Topic: WAN connection to MySQL

[Larry]

I have SME 5.6 U5.  All working well. My problem is that I can not connect to the MySQL server (version: 3.23.49) remotely.  Local connections are fine.  I have found tons of information regarding this probelm here in the forums and have applied what appears to be the correct procedure:

   /sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
   /sbin/e-smith/expand-template /root/.my.cnf
   /etc/rc.d/init.d/mysqld restart

All three commands respond without complaint, but I still can not connect:

ERROR 2013: Lost connection to MySQL server during query

Can anyone help me with what I must do?

Thanks for any insight.

[rla22]

I dont klnow if your tried this yet or not, but it is worth a shot. Did you cerate a user account with world wide privelages such as user@ instead of user@localhost.
I know it worked fine form me when i did that seemed to work.

[Nathan Fowler]

Larry, one thing I would advise against is hitting mysql over a WAN (I'm going to assume you are traversing a public network) without some type of secure encryption.  You may want to look at using stunnel to wrap that mysql session.

Now, you could also use the following to "fix" your issue (I use fix loosely, as it is a work around with provides increased security)

1) Use stunnel in non-transparent proxy mode to wrap your connection securely, this means mysqld would see your connection as local and would be wrapped over secure encryption.

2) Use ipchains/iptables to limit the addresses connecting.  If you always connect from the same ipaddr then deny everything but this one, if from the same netblock then grant that netblock only (still more secure than world-open).

3) You could leave 3306 TCP alone (if it's only open internally), and use stunnel to redirect TCP 57600 to TCP 3306, thus avoiding possible "scans" and intrusion attemps because you are listening on a high port wrapped over stunnel.  Additionally, I'd block 3306 from external access if this isn't already the case.  I would use ipchains/iptables in addition to the tcpwrapper since ipchains/iptables can drop the request before a connection can be established.

Hope this helped, should you need any help implementing/understanding this please let me know and I would be more than happy to work with you towards a secure solution.

Thanks,
Nathan

[Larry]

Nathan,

Your approach would seem to provide a very good method for us to properly use our servers and I'd like to implement it.  But the references to your solution are to vague for me to handle.  I am aware of rules to change the IPtable/chains but don;t know where or how to find them, and I don't know how to get or implement stunnel.  We are using ssh and sftp so probably we could do as you suggest.  I also like the idea of port mapping.

Can you supply or point me to where I could get explicit instructions for this as it applies to the SME?

[Larry]

rla22,

Thank you for your suggestion, and we have done this.  It is clearly a refusal of external requests that eventualy time out.

[Nathan Fowler]

Larry, the client trying to access MySQL, is it Linux or Win32?

[Larry]

We have both, but the major use would be for a Linux client.

[Nathan Fowler]

Larry, let me first appolgize for leaving this unanswered for so long, I've been very busy here over the weekend and am now completely exhausted.

I hope you don't mind if I address this on Monday, I will give you detailed step-by-step instructions for doing exactly what you want.

Sorry for such a long delay,
Nathan

[Larry]

Nathan,

I'm just very grateful that you are willing to take the time at all!  I've been preocupied with other things as well.

[Nathan Fowler]

"#>" Denotes a console command

1) Deny access to MySQL over the WAN using iptables
   #> iptables -A input -p tcp --dport 3306 -j DROP -i ethX, where ethX is your external interface.
   #> echo "iptables -A input -p tcp --dport 3306 -j DROP -i ethX" >> /etc/rc.d/rc.local

2) Create an stunnel certificate
   You must have "make" installed, so you may need to install make and other related tools.
   
   #> cd /usr/share/ssl/certs
   #> make stunnel.pem

   Your common name should be the FQDN (fully qualified domain name) for the host that you will use.

3) Create a hosts.allow template fragment
   
   #> mkdir -p /etc/e-smith/templates-custom/etc/hosts.allow
   #> cd /etc/e-smith/templates-custom/etc/hosts.allow
   #> pico -w SecureSQL
      Add the following line:
         ssql : ALL
      Hit Ctrl-X to save
   #>/sbin/e-smith/expand-template /etc/hosts.allow


4) Start stunnel daemon
   #>/usr/sbin/stunnel -d 57600 -r localhost:3306 -N ssql
   #>echo "/usr/sbin/stunnel -d 57600 -r localhost:3306 -N ssql" >> /etc/rc.d/rc.local

Now, 3306 should be denied from the WAN interface and 57600 TCP is a stunnel daemon that is wrapping to 3306 as localhost (no transparent proxy flag set, intentionally)

For your linux client, you simply need to type:
#>stunnel -c -d 3306 -r SERVER:57600

Now, localhost:3306 redirects to 57600 over stunnel, which then redirects to your server on 3306.
Now from the client, you can access your mysql database while enjoying the benefits of SSL encryption with the mysql client with the following command:

#>mysql -h CLIENT -u YOURDBUSER -p

The same command would hold true for your Windows client, you just need to find a Win32 port of stunnel.  I'd be more than happy to email it to you if you want.

Note my lack of experience with 5.6 may be an issue, you may need to open 57600 using iptables.

Hope this helped,
Nathan

[Larry]

Nathan,

Thanks so much for the detailed instructions.  I gather the stunnel is similar to a VPN connection between the two units.

I was unable to get the deny access to run:

#> iptables -A input -p tcp --dport 3306 -j DROP -i eth0

I get: iptables: No chain/target/match by that name

But since there is apparently something already blocking the WAN access in the firewall then that is probably OK.  

The other statmenents all ran without any complaints at all.  However, on the client it times out.

[GSSLINUX01 larry]$ /usr/sbin/stunnel -c -d 3306 -r 223.223.223.223:57600
[GSSLINUX01 larry]$ mysql -h 223.223.223.223 -u loudmouth -p mydata
Enter password:
ERROR 2013: Lost connection to MySQL server during query

Seems like I should have gotten some kind of message whe I started the clinet stunnel.  Something about the certificate and accepting it.  

Sorry to be so inept re the ssl side of things, but I appreciate your assistance.  I'll look up the documentation of stunnel.  Never knew about it before.

Thanks again.

Larry

[Nathan Fowler]

For the iptables command, try all upper-case "INPUT" instead of "input" (see statement about lack of use of 5.6 heh)

From your client, you need to connect to localhost, not the IPAddr of the box.

[GSSLINUX01 larry]$ /usr/sbin/stunnel -c -d 3306 -r 223.223.223.223:57600
[GSSLINUX01 larry]$ mysql -h localhost -u loudmouth -p mydata

When you run Stunnel on the client, it looks like this:

[Stunnel Client, 3306] -> [Stunnel Daemon 223.223.223.223, 57600] -> [Localhost MySQL, 3306]

Basically, the client is listening on 3306 TCP, forwarding the requests over Stunnel to 223.223.223.223 57600, which then forwards the packets to 3306 on 223.223.223.223 as localhost.

It's wrapping the connection around stunnel.

I hope this makes sense.  When you run stunnel -c -d 3306 -r IP:57600 you are effectively saying "create and bind local socket 3306 to remote host on 57600" so, when you connect to localhost:3306 you are really connecting to remotehost:57600 -> mysql:3306

[Larry]

OK Nathan, I understand the concept now.  

I can see the demon for stunnel running on the server but after starting the client I get no compaints but also no indication that there is anything running to intercept the mysql login.  And it won't login to the remote server but instead logs in to the local mysql server running on my client.  That may be the problem now, that I'm running mysql on the same unit that is going to be the client?

[Nathan Fowler]

That is the problem

3306 is already bound on your linux client, so stunnel can't bind to it.

Try this instead.
/usr/sbin/stunnel -c -d 57600 -r 223.223.223.223:57600 -v

Then, launch mysql to connect to localhost using port 57600.  I think -v is verbosive, but if it errors out just remove it.

[Larry]

I see what you are driving at here, but same thing. Wants to connect to the local sever no matter what -P port I set in the mysql command.

When you run the client command it is -c (client) and -d (demon).  Shouldn't there be a demon running at that point on the client?  I don't see any process for it so it seems to me mysql is just running locally.

Anyway Nathan I appreciate the time you have devoted to this.  It's a neat concept and I'm going to keep playing with it.

But back to the original question and your reluctance to run MySQL over the WAN.  If we grant with specific incomming IPs aren't we failry safe?  I know the data and logins may not be secure but we don't really have sensative info flowing.  Until I can get this to work I wish I knew how to get the IP tables/chains which ever SME 5.6 uses, to let me open it up.

Again, thanks for the assist.

Larry