MySQL Bugs: #83485: Can't create SSH tunnel with password protected private key

Bug #83485	Can't create SSH tunnel with password protected private key
Submitted:	22 Oct 2016 23:51	Modified:	14 Apr 2018 10:36
Reporter:	Tamas Turcsanyi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.3.8, 6.3.9	OS:	Windows (Microsoft Windows 8.1 Pro)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Can't create SSH tunnel with a password protected private key (it worked fine while password based login was enabled).

"Test connection" button results in two misleading error dialogs too: 

"SSH connection failed / Check your SSH connection settings and whether the SSH server is up. Error: [Errno 11001] getaddrinfo failed".

After clicking OK another dialog appears:

"Test connection / ERROR Could not determine remote OS details."

The related log:

01:37:50 [INF][wb_admin_grt.py:testInstanceSettingByName:687]: Instance test: Connecting to AN.IP.ADDRESS.HERE:
01:37:50 [INF][            grt]: connected.	
01:37:50 [ERR][wb_admin_ssh.py:connect:429]: Error opening SSH connection to AN.IP.ADDRESS.HERE:: Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\wb_admin_ssh.py", line 418, in connect
    key_filename = key, timeout = 10, look_for_keys=bool(usekey), allow_agent=bool(usekey) )
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 283, in connect
    to_try = list(self._families_and_addresses(hostname, port))
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 187, in _families_and_addresses
    addrinfos = socket.getaddrinfo(hostname, port, socket.AF_UNSPEC, socket.SOCK_STREAM)
gaierror: [Errno 11001] getaddrinfo failed

01:37:50 [ERR][wb_admin_control.py:acquire_admin_access:267]: SSHDownException: Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\wb_admin_control.py", line 254, in acquire_admin_access
    self.ssh = SSH(self.server_profile, self.password_handler)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\wb_server_management.py", line 128, in __init__
    self.wrapped_connect(profile, password_delegate)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\wb_admin_ssh.py", line 325, in wrapped_connect
    self.connect(host, port, settings.ssh_username, pwd, usekey, key_filename)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\wb_admin_ssh.py", line 434, in connect
    raise SSHDownException(str(exc))
SSHDownException: [Errno 11001] getaddrinfo failed

01:37:51 [ERR][wb_admin_control.py:get_config_options:757]: Could not open the file "/etc/mysql/my.cnf": Unable to perform function get_file_content. No SSH connection is active
01:37:51 [INF][wb_admin_control.py:query_server_installation_info:857]: Currently connected to MySQL server version 'unknown', conn status = None, active plugins = []
01:37:51 [WRN][wb_admin_control.py:acquire_admin_access:291]: Could not detect target OS details
01:37:51 [WRN][wb_admin_grt.py:testInstanceSettingByName:719]: Instance test: could not determine OS version information

How to repeat:
- create an SSH key pair using eg. puttygen.exe and add a password
- save the private and public key files somewhere
- add the public key to .ssh/authorized_keys file on server
- create a new connection in Workbench, Conn. method: Standard TCP/IP over SSH
- set host and username, and set SSH private key to the password protected key file
- set MySQL fields too
- click Test connection

Suggested fix:
Add support for password protected keys and (or at least) SSH authentication agents like Pageant.

Hello Tamas Turcsanyi,

Thank you for the bug report and steps.
Verified this behavior on MySQL Workbench in 6.3.9 version

Thanks,
Chiranjeevi.

http://bugs.mysql.com/bug.php?id=84907  marked as duplicate of this one.

http://bugs.mysql.com/bug.php?id=85038  marked as duplicate of this one.

Same problem here on version 6.3.9 with Linux Mint 18.1 (Ubuntu 16.04).

I found a solution: update the paramiko python library.
On Ubuntu: sudo pip install -U paramiko

http://bugs.mysql.com/bug.php?id=85062  marked as duplicate of this one.

Is there a workaround for Windows 10 x64 ?

Just to add I tried downloading paramiko-2.2.1 and extracting the paramiko directory over the C:\Program Files\MySQL\MySQL Workbench 6.3 CE\python\site-packages but that just resulted in an error:

09:37:37 [INF][     SSH tunnel]: Starting tunnel
09:37:37 [ERR][         python]: Error running file C:\Program Files\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py
09:37:37 [WRN][               ]: Tunnel manager could not be executed

I also tried using pagent to handle the key but that also errored.

My SSH Key is password protected and my server only accepts the aes256-ctr cipher, I know in Java I have to enable unlimited security to allow the connection if it helps.

I have found that a PuTTY private key doesn't work, you need to export it in an OpenSSH private key format. The following worked for me on Workbench 6.3.9 x64 running on Windows 10 Pro x64.

Open PuttyGen
Click Load
Load your private key
Go to Conversions->Export OpenSSH and export your private key

Save the key file somewhere safe and then open it with Workbench in the manage connections dialog. You may want to clear passwords before testing connections. Don't forget to update the key on both the Connections pane and the Remote Management pane.

Please export the key into OpenSSH format, 
I should also mention that with the next WB release we refactored ssh handling, so please give it a try and let us know if it works for you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Definitely can't use an SSH connection with a password protected key to connect. Could this possibly be fixed? It's a bug from 2016 that requires a workaround that is less secure. Or just using other software  :-/

same problem on MacOs