MySQL Over SSH – How to Expose a Server via SSH Tunnel

networkingport-forwardingroutersshssh-tunnel

I've used remote and local forwarding with succes in the past, but this time I can't get a setup to work.

What I'm trying to achieve:
Make my sql server publicly reachable so the Google CloudSql can replicate the database.

I have a NAS running debian behind a router that can do port mapping at location A
I am at location B with an CentOS VM running mariadb

From the VM at location B I run the following command

:~$ ssh -f -N -T -R 3306:localhost:3306 my.dyndns.address -p [sshport]

(no mysql server is running on the nas, or at least not at port 3306)

Then on the nas:

:~# ss -anp | grep :330
tcp    LISTEN     0      128    127.0.0.1:3306                  *:*                   users:(("sshd",pid=27182,fd=9))
tcp    LISTEN     0      128     ::1:3306                 :::*                   users:(("sshd",pid=27182,fd=8))

On the router I have port 33006 forwarded to the nas's port 3306 like so:

(columns protocol, external-start-port, external-end-port,local-port,destinationport forwarding router
Other port forwarding rules like openvpn and ssh work perfectly that way

I can connect from my own computer to the mysql database, the directive bind-address was set to '*', I tried also with this directive commented, but as expected, yielded no different results

I'm looking at both machine's output from journalctl -f, but no information is coming through that can help me to troubleshoot what I'm doing wrong.

— Clarification —

  1. I want to connect from the outside to my.dyndns.address at port 33006.

  2. my.dyndns.address translates to the public address from my ISP modem.

  3. 33006 is forwarded on the modem to the local IP of the NAS at port 3306, which is in turn tunneled to the mysql-server (at location B).

  4. I'm testing this with

    mysql -h my.dyndns.address --port=33006 -u myuser -p

    whereas

     mysql -h local_ip_on_B -u myuser -p

    works just fine

I also tried using a VPN from the db server at location B to location A (the NAS is also the OpenVpn server) and pointing the forwarded port on the ISP's modem directly to IP Address of the IP the db server gets on the tun interface. If I make a second VPN connection to location A, I can connect the database using the IP from the tun interface from my computer.

The error I'm getting is ERROR 2002 (HY000) Can't connect to MySQL server on ip_of_the_nas.

UPDATE

I made a schematics to clarify my situation further:
Overview of the networks

Can someone help me ?

Best Answer

This fragment of the ss output

127.0.0.1:3306

indicates sshd listens on the IPv4 port 3306 only on the loopback interface. Your communication from the outside comes via another (non-loopback) interface.

See man 5 sshd_config:

GatewayPorts
Specifies whether remote hosts are allowed to connect to ports forwarded for the client. By default, sshd(8) binds remote port forwardings to the loopback address. This prevents other remote hosts from connecting to forwarded ports. GatewayPorts can be used to specify that sshd should allow remote port forwardings to bind to non-loopback addresses, thus allowing other hosts to connect. The argument may be no to force remote port forwardings to be available to the local host only, yes to force remote port forwardings to bind to the wildcard address, or clientspecified to allow the client to select the address to which the forwarding is bound. The default is no.

So you need

  • GatewayPorts yes in your sshd_config; or

  • alternatively GatewayPorts clientspecified and then one of these:

    ssh -R               :3306:localhost:3306 …  # note the extra `:'
ssh -R            '*':3306:localhost:3306 …
ssh -R proper_IP_here:3306:localhost:3306 …

Don't forget you need to reload/restart sshd. Also check if the firewall on the NAS doesn't block the port.

Related Question