Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

  • I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?

    I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

    I used the following session details:

    • Connection Method: TCP/IP over SSH.
    • SSH Hostname: mysql.myhost.com:3306
    • SSH username: my linux login
    • SSH public key file: my local public key file
    • MySQL hostname: 127.0.0.1 MySQL
    • Server Port: 3306
    • Username: root

    I get an error message when I try to connect: "Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root"

    "Can't connect to MySQL server on '127.0.0.1' (10061)"

    As another test - I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can't get "TCP/IP over SSH" working in Workbench.

    Secondary question: when Workbench asks for "Path to SSH public key file" doesn't it really need my private key file?

    Good grief. http://bugs.mysql.com/bug.php?id=61368 shows that IS a PRIVATE key file that is needed in OpenSSH format. I wondered about that but was unsure.

  • I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

    1. I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
    2. I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
    3. Finally I have the following entered in MySQL WorkBench.

      • SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
      • SSH Username: sshuser
      • SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
      • MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
      • MySQL Server Port: 3306 (also default)
      • Username: root

    The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

    One thing I needed to do on the server side was to make sure the /etc/ssh/sshd_config had was this line: `AuthorizedKeysFile /home/root/.ssh/authorized_keys` and that the authorized_keys had my PUBLIC key as an entry.

    Please clarify if step 2 which set `AllowTcpForwarding yes` is applied to the *remote server* i.e. the host that has MySQL instance we're trying to connect to; or the local machine with MySQL Workbench installed

    @NamGVU step 2 applies to the remote server where MySQL is installed. Particularly to the OpenSSH server that provides tunneling to MySQL through SSH.

    I tried but still fail to tunnel. MySQL Workbench tells me to read more error detail in the log file. May you know where to read?

    If you followed the described steps precisely, it would work. If it didn't, I suppose the remote server's SSH port might be blocked by the server's firewall (iptables in Ubuntu case). Check if the port is open. Try to SSH to the server directly from your local machine. If you can access, then the port is open, but the problem is somewhere else. You'd also need to check MySQL logs on remote server.

    In my case, I can connect SSH to it and even can connect 3306 directly to it (ie. not via SSH tunnel) - that means SSH port and 3306 port is open. I double-check that for my EC2 instance.

    I'll try to read mysql log file on that server.

    I got it work today - need a reboot after configuring the `AllowTcpForwarding` entry

  • I think the TCP/IP over SSH approach works by establishing a "normal" SSH connection underlying the MySQL connection (in the same way as you would tunnel using -L with the OpenSSH command-line client).

    Therefore, you'd need to specify a connection to an SSH server on server via which you're establishing the tunnel. Here, you seem to be using mysql.myhost.com:3306, which would imply that you're running this SSH server (not MySQL) on port 3306.

    It's possible to bind a MySQL server on 127.0.0.1:3306 and an SSH server on your external IP address for mysql.myhost.com on port 3306, but that's very unlikely. I guess your SSH server is listening on port 22 (the default).

    You should probably use mysql.myhost.com:22. (Check you can connect to it via a normal SSH client such as Putty too.)

  • You may need to check the users in the mysql.user table.

    Run this query:

    SELECT user,host FROM mysql.user;
    

    You should see something like this:

    mysql> SELECT user,host,password FROM mysql.user;
    +------------------+-------------+-------------------------------------------+
    | user             | host        | password                                  |
    +------------------+-------------+-------------------------------------------+
    | root             | localhost   | *7A670E02260CDEEFF062DD08F3A6F6DA079998CB |
    | ping             | %           | *124E1DB56CC8D6E2FEE8315BB2544BF04B980DB6 |
    | admin            | 10.67.135.% | 1a6858054a41fede                          |
    | icorbin          | 10.67.135.% | 366ed93a7396650e                          |
    +------------------+-------------+-------------------------------------------+
    4 rows in set (0.00 sec)
    

    Please notice that

    If you want root to connect via TCP/IP you must specify IP address or netblock for a root user.

    Something like this:

    GRANT ALL PRIVILEGES ON *.* TO [email protected]'%' IDENTIFIED BY 'whateverpassword';
    

    or if the root password is the same for [email protected] then

    GRANT ALL PRIVILEGES ON *.* TO [email protected]'%' IDENTIFIED BY PASSWORD '*7A670E02260CDEEFF062DD08F3A6F6DA079998CB ';
    

    CAVEAT : [email protected]'%' is normanlly not recommended. Maybe try [email protected]'10.%' or any other netblock for root.

    Give it a Try !!!

    Shouldn't `[email protected]` work via the SSH tunnel, since as far as the MySQL server is concerned, the connection comes from the end of the tunnel?

    @Bruno : One sure way to know is to successfully connect and then run SELECT USER(),CURRENT_USER(); and see what it outputs. The function USER() echoes what you attempted to authenticate as, while CURRENT_USER() echoes what MySQL allowed you to authenticate as. If CURRENT_USER() echoes [email protected], then the answer to your question is yes.

  • You may be using an older version of MySQL Workbench and need to update. This is a bug in version 6.0.8, which is currently the version in Ubuntu repositories. Updating to version 6.3.6 fixed this for me.

    Downloads here: http://dev.mysql.com/downloads/workbench/#downloads

  • One thing that isn't mentioned in any other answer is the importance of the OpenSSH format for the key as stated on SO (https://stackoverflow.com/questions/34504232/mysql-workbench-failing-to-connect-via-ssh-due-to-key/38108623#38108623).

    Despite the answer there, I was able to use a password protected key with MySQL Workbench 6.3.7 (64 bit, Windows 10).

  • My problem was due to the fact that I was trying to use an ed25519 SSH key. I noticed this error on the SSH server in auth.log:

    sshd[25251]: Connection closed by 192.168.x.x [preauth]
    

    Once I switched to using an RSA key, everything worked as expected.

  • I faced the same problem. I checked and tried to set AllowTcpForwarding Yes but it was missing in my sshd_config so no help. make sure the ssh hostname is NOT the same with the mysql hostname(use localhost).

    In workbench, choose + to add new connection and set the following:

    • connection method: standard TCP/IP over SSH
    • SSH Hostname: 192.168.0.50:22 (place remote SSH server IP and port(optional))
    • SSH Username: sshuser
    • You can set password or add at the prompt
    • MYSQL Hostname: localhost or 127.0.0.1
    • MYSQL Server port:3306
    • You can set password or add at the prompt

    Test connection. It should be successful then hit OK.Viola!

  • Sometimes keys created by PuTTY won't work. Use ssh-keygen on the Linux box to create a key pair. Copy the contents of the new id_rsa to a text file on Windows. Make sure to add the contents of id_rsa.pub to authorized_keys on the Linux box. All other defaults in Workbench are fine including 127.0.0.1 for MySQL Hostname. Of course it has to be Standard TCP/IP over SSH.

  • I came up with the same error. The problem is "somewhat" the timeout. I cranked even the value up to 120 seconds which did not help.

    In my case I could solve it doing a nslookup myserver.com and using the IP address instead of the hostname. My assumption is a problem trying to connect from IPv4 to IPv6.

  • I came up with the same error. My case:

    • Ubuntu 18.04
    • mysql workbench 8.0.18
    • ssh access without password (only via public/private ssh keys)

    I solved this issue after:

    1. remove mysql-workbench-community (previously installed via mysql website):

      sudo apt remove mysql-workbench-community -y

    2. install mysql-workbench

      sudo apt install mysql-workbench -y

    3. add new connection

    4. click "Store in Keychain" for mysql (not for ssh user) and set password
    5. click "Test connetion"
    6. after prompt password, leave input empty and check "save password"
    7. click "OK"

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM