MySQL client in SSH tunnel: error at reading initial communication packet

Quite recently, I had a following problem when connecting to MySQL server over SSH tunnel (in this case, client application was Navicat):

2013 - Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Possible causes:

  1. SSH daemon does not allow port forwarding (most likely).
  2. Hosts.deny/hosts.allow or iptables (less likely).

SSH server port forwarding

Edit /etc/ssh/sshd_config and make sure, there’s following line:

AllowTcpForwarding yes

Some of the system hardening guides disallow usage of this directive and some of sysadmins actually read those guides ;)

Hosts.deny, hosts.allow and iptables

In case of above issues you most likely have no remote access to the machine, so tunneling MySQL connection won’t work either. Access control based on hosts files is checked in the following way:

  • First, /etc/hosts.allow file is checked. If a rule placed there matches incoming connection, access is granted and further processing stops.
  • Then, /etc/hosts.deny file is checked. If a rule denying access matches incoming connection, access is denied and further processing stops.
  • If none of matching rules are found, access is granted to given service.

Note: configuration of hosts.allow and hosts.deny REQUIRES to have an empty line at the end of hosts.allow and hosts.deny.

So, log in locally and check:

/etc/hosts.deny

Hosts.deny and hosts.allow files are the most basic access control features of Linux machine. Typical usage of these files is denying all daemons from accepting connections from all possible sources, then enabling only services that really need to be accessed from the network. Hosts file based access control mechanism is very simple and effective, although it is not that widely used. Many of administrators prefer to use iptables firewall rules due to much higher flexibility.

Either way, a file that is used to deny access to your host is below. Default content of this file on Debian Squeeze:

# /etc/hosts.deny: list of hosts that are _not_ allowed to access the system.
#                  See the manual pages hosts_access(5) and hosts_options(5).
#
# Example:    ALL: some.host.name, .some.domain
#             ALL EXCEPT in.fingerd: other.host.name, .other.domain
#
# If you're going to protect the portmapper use the name "portmap" for the
# daemon name. Remember that you can only use the keyword "ALL" and IP
# addresses (NOT host or domain names) for the portmapper, as well as for
# rpc.mountd (the NFS mount daemon). See portmap(8) and rpc.mountd(8)
# for further information.
#
# The PARANOID wildcard matches any host whose name does not match its
# address.
#
# You may wish to enable this to ensure any programs that don't
# validate looked up hostnames still leave understandable logs. In past
# versions of Debian this has been the default.
# ALL: PARANOID
#

Adding ALL:ALL to that file will prevent any incoming connections to all of your services. Basic configuration pattern here is following:

[daemon]:[src 1],[src 2],...[src n]

Where [daemon] can be a listening service or wildcard (for example, ALL), and [src n] variables refer to remote machine trying to connect.

So, denying access to all services from all origins will look like:

# /etc/hosts.deny: list of hosts that are _not_ allowed to access the system.
#                  See the manual pages hosts_access(5) and hosts_options(5).
#
# I cut original comments here.
#
ALL:ALL
#

/etc/hosts.allow

Now, that we have followed our rule to deny all access and open our host only to several services that need to be open, we should edit /etc/hosts.allow to allow remote administration of our system:

# /etc/hosts.allow: list of hosts that are allowed to access the system.
#                   See the manual pages hosts_access(5) and hosts_options(5).
#
# I cut the original comments here.
#
# Allow access from 10.1.0.20 to all services
ALL:10.1.0.20
# Allow 10.1.0.15 to connect to ssh server
sshd:10.1.0.15
# Allow 10.1.0.16 to all services except for ssh server
ALL EXCEPT sshd:10.1.0.16
# Allow 10.1.0.1/24 (entire LAN with 255.255.255.0 netmask) to connect to print server
cupsd: 10.1.0.1/24
#

iptables

Iptables is used to create firewall rules on many Linux machines. Full configuration directives and methodology standing behind that is way beyond scope of this article, so let’s focus on enabling SSH access from a single machine.

Many systems have default iptables DROP policy, similar to ALL:ALL in /etc/hosts.deny file. It’s purpose is the same, as we want to deny all incoming traffic and then open several services to certain group of remote computers.
Typing iptables -L or iptables-save in terminal while logged in as root user will produce currently applied rules, similar to:

root@host:~# iptables-save
# Generated by iptables-save v1.4.8 on Wed Aug 15 16:30:20 2012
*nat
:PREROUTING ACCEPT [4846375:527813970]
:POSTROUTING ACCEPT [99547:5973129]
:OUTPUT ACCEPT [99547:5973129]
COMMIT
# Completed on Wed Aug 15 16:30:20 2012
# Generated by iptables-save v1.4.8 on Wed Aug 15 16:30:20 2012
*filter
:INPUT DROP [2031370:285099881]
:FORWARD DROP [0:0]
:OUTPUT ACCEPT [4167661:1116594356]
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -s 10.1.0.15/32 -p tcp -m tcp --dport 22 -j ACCEPT

-A FORWARD -o lo -j ACCEPT
-A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
COMMIT
# Completed on Wed Aug 15 16:30:20 2012

root@host:~# iptables -L
Chain INPUT (policy DROP)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:www
ACCEPT     tcp  --  10.1.0.15            anywhere            tcp dpt:ssh

Chain FORWARD (policy DROP)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Both outputs state the same: server accepts incoming connections to www (port 80) and a single host (10.1.0.15) can connect to ssh (port 22). In order to achieve similar results, type as root:

iptables -A INPUT -p tcp --dport 22 -s 10.1.0.15 -j ACCEPT

to enable SSH for  10.1.0.15, and:

iptables -A INPUT -p tcp --dport 80 -j ACCEPT

to enable world-wide access to www service.

Links

  1. http://www.solarum.com/2012/04/10/navicat-ssh-tunnel-error-2013-lost-connection-to-mysql-server/
  2. http://linux.die.net/man/5/hosts.allow

2 thoughts on “MySQL client in SSH tunnel: error at reading initial communication packet

  1. If the user is running SELinux, it also blocks the port forwarding. To allow it, one should execute:

    # setsebool -P sshd_forward_ports 1

    It should work after this. Worked in my case

  2. You saved my day! I forgot that I’ve added
    AllowTcpForwarding no
    line to sshd_config when installed server first time and now faced the issue with connecting to MySQL with SSH tunel on Navicat software.
    After changing back to
    AllowTcpForwarding yes
    tunnel worked as supposed to.

    Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s