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 [email protected] -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 [email protected] none debug1: kex: client->server [email protected] 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 [email protected] 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 [email protected] 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.
Sequel Prodoing 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.netstat -tln | grep :3306orsudo netstat -tlnp | grep :3306and 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 trymysql -h 127.0.0.1(add-u userif needed) (NOT-h localhostwhich is NOT equivalent here although in most other places it is)tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN, andmysql -h 127.0.0.1 -urootreturnsERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)