I have a stock install of Ubuntu 16.04.2 with key based authentication setup and working fine. I've also installed an unmodified mysql-server 5.7.17. I cannot seem to connect to the mysql server via Sequel Pro ssh tunneling. It always returns the following:

Unable to connect to host 127.0.0.1 because the port connection via SSH was refused.

Please ensure that your MySQL host is set up to allow TCP/IP connections (no --skip-networking) and is configured to allow connections from the host you are tunnelling via.

You may also want to check the port is correct and that you have the necessary privileges.

Checking the error detail will show the SSH debug log which may provide more details.

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

The full log:

Used command:  /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 root@x.x.x.x -L 53471:127.0.0.1:3306

OpenSSH_6.9p1, LibreSSL 2.1.8
debug1: Reading configuration data /Users/example/.ssh/config
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 21: Applying options for *
debug1: Control socket " none" does not exist
debug1: Connecting to x.x.x.x [x.x.x.x] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/example/.ssh/id_rsa type 1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_rsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_dsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_dsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_ecdsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_ecdsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_ed25519 type -1
debug1: key_load_public: No such file or directory
debug1: identity file /Users/example/.ssh/id_ed25519-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_6.9
debug1: Remote protocol version 2.0, remote software version OpenSSH_7.2p2 Ubuntu-4ubuntu2.1
debug1: match: OpenSSH_7.2p2 Ubuntu-4ubuntu2.1 pat OpenSSH* compat 0x04000000
debug1: Authenticating to x.x.x.x:22 as 'root'
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client chacha20-poly1305@openssh.com  none
debug1: kex: client->server chacha20-poly1305@openssh.com  none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: Server host key: ssh-rsa SHA256:eFHrFt6z4o0mErU0vnzcuzlyIQqZPUa09a0RFl0wE7Q
debug1: Host 'x.x.x.x' is known and matches the RSA host key.
debug1: Found key in /Users/example/.ssh/known_hosts:36
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey
debug1: Next authentication method: publickey
debug1: Offering RSA public key: /Users/example/.ssh/id_rsa
debug1: Server accepts key: pkalg ssh-rsa blen 279
debug1: Authentication succeeded (publickey).
Authenticated to x.x.x.x ([x.x.x.x]:22).
debug1: Local connections to LOCALHOST:53471 forwarded to remote address 127.0.0.1:3306
debug1: Local forwarding listening on ::1 port 53471.
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 53471.
debug1: channel 1: new [port listener]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: Connection to port 53471 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
channel 2: open failed: connect failed: Connection refused
debug1: channel 2: free: direct-tcpip: listening port 53471 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 53473 to 127.0.0.1 port 53471, nchannels 3

And here is my sshd_config:

Port 22
Protocol 2
HostKey /etc/ssh/ssh_host_rsa_key
HostKey /etc/ssh/ssh_host_dsa_key
HostKey /etc/ssh/ssh_host_ecdsa_key
HostKey /etc/ssh/ssh_host_ed25519_key
UsePrivilegeSeparation yes

KeyRegenerationInterval 3600
ServerKeyBits 1024

SyslogFacility AUTH
LogLevel INFO

LoginGraceTime 120
PermitRootLogin yes
StrictModes yes

RSAAuthentication yes
PubkeyAuthentication yes

IgnoreRhosts yes
RhostsRSAAuthentication no
HostbasedAuthentication no

PermitEmptyPasswords no

ChallengeResponseAuthentication no

PasswordAuthentication no



X11Forwarding yes
X11DisplayOffset 10
PrintMotd no
PrintLastLog yes
TCPKeepAlive yes


AcceptEnv LANG LC_*

Subsystem sftp /usr/lib/openssh/sftp-server

UsePAM yes

Match Group mystaff
    ChrootDirectory %h
    X11Forwarding no
    AllowTcpForwarding yes
    ForceCommand internal-sftp
    PermitTunnel yes

AllowTcpForwarding yes
PermitTunnel yes
AllowAgentForwarding yes

I know some of the values in sshd_config are redundant, but I've tested many variations of it. This is frustrating because this setup is totally vanilla aside from disabling password authentication.

Is there something I need to do with the firewall? I didn't think there was since ssh is already allowed through.

Update

Interesting note: I can complete remove mysql from the server, and Sequel Pro will still return the exact same error. I guess that means it's related to SSH configuration.

Also noteworthy is that this same error message is returned on HeidiSQL and Mysql Workbench.

  • AFAIK your mysql is not configured to listen on that port, but only on a socket (default configuration if I remember well). Try to forward that socket instead. – Jakuje Feb 20 '17 at 20:12
  • @Jakuje I'm not sure I understand, can you provide an example? I haven't read any setup guides that describes what your mentioning. – Brandon Feb 20 '17 at 20:19
  • 1
    I have no idea what is Sequel Pro doing and what is that for nor what options you used in that software. That would probably make sense to mention, because that is the place where from you see the errors. – Jakuje Feb 20 '17 at 21:01
  • On the server do netstat -tln | grep :3306 or sudo netstat -tlnp | grep :3306 and if there is not a LISTEN on 3306 (by mysqld in the latter case) then your mysql is not correctly set up and running. If it is try mysql -h 127.0.0.1 (add -u user if needed) (NOT -h localhost which is NOT equivalent here although in most other places it is) – dave_thompson_085 Feb 21 '17 at 1:24
  • @dave_thompson_085 That returns tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN, and mysql -h 127.0.0.1 -uroot returns ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) – Brandon Feb 21 '17 at 1:28

I'm not familiar with the Sequel Pro product, but I'm wondering if it has something to do with X11Forwarding set to no.

Have you tried contacting the vendor?

  • I've tried enabling that as well with no luck. I believe that particular property has something to do with being able to access the desktop/gui remotely. I have also tried contacting the vendor, and they say they are not filtering this type of traffic. – Brandon Feb 20 '17 at 22:22

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.