Can't make remote connection from MySQL Workbench

Question

I'm trying to connect from Windows 10 to Ubunutu 14.04. I'm using ssh. The connection appears to get as far as logging on to the machine, but it doesn't connect to the database. I know I'm using the correct password. The error message is:

Failed to connect to MYSQL at localhost:3306 through SSH tunnel at username@xx.xx.xx.xx with user xxxx 
Unable to establish SSL connection 

The Log shows:

12:27:02 [INF][ SSH tunnel]: Existing SSH tunnel not found, opening new one 
12:27:06 [INF][ SSH tunnel]: Opening SSH tunnel to xx.xx.xx.xx:22 
12:27:08 [INF][ SSH tunnel]: TunnelManager.wait_connection returned OK 
12:27:08 [INF][sshtunnel.py:set_keepalive:502]: SSH KeepAlive setting skipped. 
12:27:08 [INF][ SSH tunnel]: SSH tunnel connect executed OK

The user I'm connecting to was created like this:

mysql> create database fred 
mysql> CREATE USER 'fred'@'localhost' IDENTIFIED BY 'xxxx'; 
mysql> CREATE USER 'fred'@'%' IDENTIFIED BY 'xxx'; 
mysql> grant all privileges on fred.* to 'fred'@'x'; 
mysql> grant all privileges on fred.* to 'fred@'%' 

I can connect from a remote ubuntu machine like this;

mysql --user=fred --host=xx.xx.xx.xx --port=3306 -p

There's no mention of the connection in /var/log/mysql

/var/log/auth shows

Dec  7 14:03:46 www.example.com sshd[14474]: error: Could not load host key: /etc/ssh/ssh_host_ed25519_key
Dec  7 14:03:47 www.example.com sshd[14474]: Accepted publickey for root from xx.xx.xx.xx port 53591 ssh2: RSA ...
Dec  7 14:03:47 www.example.com sshd[14474]: pam_unix(sshd:session): session opened for user root by (uid=0)

The ssh connection is to root, but I've tried it with a non-root user and it made no difference.

These are the details from the connection tab:

SSH Hostname: xx.xx.xx.xx
SSH Username root
SSH KeyFile: [dir]\.ssh\id_rsa
MySQL Hostname:localhost
MySQL Server Port: 3306
username: xxxx

Answer 1

Look carefully at the error message. It says the SSL connection could not be established. It doesn't say anyhting about SSH. So I guess MySQL Workbench is set to use SSL encryption for the connection, which the server does not support. You can change that in the connection settings:

Alternatively, you can use the SSL Wizard (also available from this page) to generate SSL certificates for a proper SSL connection.

Good news: starting with server 5.7.9 SSL is enabled by default and the server and client software automatically set up the SSL details. MySQL Workbench of course works with that too.

Answer 2

I think the problem for you is to configure correctly your SSH server to work with keys rather than passwords. Once check in its config file /etc/ssh/sshd_config & changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.