Bug #61633 SSH based server administration does not connect to hosts that accept only keys
Submitted: 24 Jun 2011 17:04 Modified: 24 Aug 2011 14:05
Reporter: Rares P Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench: Administration Severity:S3 (Non-critical)
Version:5.2.34 CE OS:Windows (XP SP3)
Assigned to: Maksym Yehorov CPU Architecture:Any

[24 Jun 2011 17:04] Rares P
Description:
Remote management -> SSH login based management allows login with SSH key, but when the host accepts only SSH key login, the workbench does not successfully carry out authentication: 
Could not establish SSH connection: Bad authentication type (allowed_types=['publickey'])
When the host accepts password login, MySQL does however successfully do the publickey login.

How to repeat:
- Create SQL connection that uses TCP/IP over SSH and authenticates with key
- Create server administration instance based on that
- Choose SSH login based management, check off "Authenticate Using SSH Key" and specify key path.
- Close and attempt to connect
[12 Jul 2011 16:04] Rafael Antonio Bedoy Torres
Hello Rares,

Thank you for reporting your bug,

May you please give us a little more info about your steps and configuration,

ie.: what OS are you trying to get connected using SSH?, what version of MySQL server are you using?

And one more thing, may you please provide the info you get when click on Help-> System Info on your workbench.

Thanks in advance!
[15 Jul 2011 23:32] Rares P
This is SSH running on Linux.
My client OS is Windows XP SP3.

System info is below.

Starting thread...

Thread started

25443 INFO Connecting to SSH server at 67.192.15.48:9022 using key C:\Documents and Settings\rpamfil\ssh\Copy of Identity...

C:\Program Files\MySQL\MySQL Workbench 5.2 CE/python/site-packages\paramiko\client.py:93: UserWarning: Unknown ssh-rsa host key for 67.192.15.48: 16139c6ed70a94f01fec2c5eb59c28df

  (key.get_name(), hostname, hexlify(key.get_fingerprint())))

New client connection

client connection established

25443 INFO Tunnel now open ('127.0.0.1', 3624) -> ('67.192.15.48', 9022) -> ('localhost', 3306)

<type 'instance'>

MySQL Workbench CE for Windows version 5.2.34

Configuration Directory: C:\Documents and Settings\rpamfil\Application Data\MySQL\Workbench

Data Directory: C:\Program Files\MySQL\MySQL Workbench 5.2 CE

Cairo Version: 1.8.8

Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.

OpenGL Driver Version: 2.1.8544 Release

OS: Microsoft Windows XP Professional Service Pack 3 (build 2600)

CPU: 2x Intel(R) Core(TM)2 CPU          6600  @ 2.40GHz, 2.0 GiB RAM

Active video adapter: Radeon X1650 Series  

Installed video RAM: 512 MB

Current video mode: 1680 x 1050 x 4294967296 colors

Used bit depth: 32

Driver version: 6.14.10.6925

Installed display drivers: ati2dvag.dll

Current user language: English (United States)
[15 Jul 2011 23:33] Rares P
Screenshot of error message

Attachment: New Picture.bmp (image/bmp, text), 223.58 KiB.

[19 Jul 2011 15:15] Maksym Yehorov
Can you try to use pageant from PuTTY to handle the private key?
[21 Jul 2011 21:30] Rares P
Interestingly, I was able to successfully connect to the server for remote management after exporting the key as DSA. The key that was triggering the error was DSS. However, that key does work for logging in to MySQL through an SSH tunnel (for both querying and for server administration). It just didn't work for remote management on Linux servers where SSH key authentication was the only method.
[21 Jul 2011 22:45] Maksym Yehorov
I was trying to reproduce the bug. Here is a report and a workaround: http://wb.mysql.com/?p=1102

I think that for some reason paramiko on Windows can not understand RSA keys generated elsewhere, still I can be wrong here.

I did not check further. There are several possibilities why it fails on Windows. The exact code of Workbench works seamlessly on Linux and Mac.

So, if you can try the way which uses PuTTY's Pageant?

N.B. RSA is a preferred method, it is more robust than DSA.
[21 Jul 2011 23:03] Rares P
Thanks for trying to replicate this and finding the workaround. At first I did not understand what you were suggesting with Pageant and I simply used Puttygen to re-export the key as DSA. Then, without, Pageant running, I was able to connect successfully.

I tried what you suggested with Pageant and it also worked. It seems that keeping Pageant running supersedes the Mysql Workbench key setting for remote authentication. It did not matter what key I had loaded into Mysql Workbench (whether DSS or DSA, whether valid for that server or not) -- the key from Pageant got me in every time.

What I do not understand about your workaround is how come you say you are loading the same key in both Mysql workbench and in Pageant. Pageant only loads .ppk keys, whereas Mysql workbench only accepts OpenSSH. Again, in my case it did not matter what I had loaded in Mysql workbench as long as "SSH key authentication" was checked.

To summarize, I think the issue is that the SSH key library that you are using for remote management authentication does not properly recognize DSS keys when public key authentication is the only login method. I was able to log in successfully to such a server using either a RSA or DSA key, or while keeping Pageant on with the PPK key loaded.
[21 Jul 2011 23:13] Maksym Yehorov
You can convert OpenSSH generated keys using PuTTYgen.exe. They have Import option in the main menu.

Thanks for the valuable info: "It did not
matter what key I had loaded into Mysql Workbench (whether DSS or DSA, whether valid for
that server or not) -- the key from Pageant got me in every time."

I keen to think now, that the ssh lib on windows can not work with openssh keys. Will look deeper then.

And thanks for the report!
[21 Jul 2011 23:29] Rares P
Now I am really puzzled. I was able to login to the server for remote management even with the DSS key that had failed before. It seems that the error pops up only when the key file is called 'Identity'. If I copy the exact file to a different name, rename the file 'Identity' (to 'Identit'), or copy the 'Identity' file to a different folder, I do not get the 'Bad authentication type' message anymore.

So, safe for using the path 'C:\Documents and Settings\rpamfil\ssh\Identity' for the SSH key, I can no longer reproduce this error :(
[24 Aug 2011 14:05] Armando Lopez Valencia
Closing defect as "Can't repeat"
Verified on Windows 7 and XP and Ubuntu 10.04 SSH server.
Thanks a lot for your report Rares.
Please do not hesitate to let us know if you find something else.