MySQL Workbench (version 6.0.8) SSH Authentication Issue

Question

I am trying to connection to a MySQL server on Host X through machine Y over SSH.

The same setup (but older version of MySQL workbench) works on my another box (CentOS 6.3).

However, the same setup doesn't work on my CentOS 6.5.

I tried to use the SSH key for authentication between my box and machine Y over SSH, and it works when I ssh from my box to machine Y.

However, MySQL workbench gave me the following error: ERROR Could not establish SSH connection: Bad authentication type (allowed_types=['publickey', 'gssapi-with-mic']).

Some people suggested that I set AllowTcpForwarding to yes in /etc/ssh/sshd_config, which I did, restarted the service and rebooted my machine.

But I still got the same error.

Any idea?

It seems like for some reason the .ssh/id_dsa key isn't picked up when MySQL workbench attemtps to connect.

Thanks in advance.

Draughtboard

MySQL Workbench does not automatically pick up id_rsa. Did you set it in the connection settings? – Paroicous

Hmm...I thought it would because the older version (5.x) does. Regardless, I tried to specify the private key file, and it gave me the same error. – Draughtboard

We've been struggling with this exact same problem, when using MySQL Workbench on a new computer. Our server is configured to only accept ssh with keyfile authentication. And MySQL seems to initially ignore the SSH Key File parameter entirely. Something that seems to help, sometimes, is to have the user SSH in via terminal on Mac, or cygwin on Windows. For some reason, after that, MySQL workbench seems to work. I have no idea why. The solutions offered so far have had no effect. – Abarca

Answer 1

You need to ensure that your private key is in openssh format. With puttygen you can export as Openssh. This worked for me.

Trash

Perfect answer... Load the key into puttykeygen, conversions > export OPENSSH key – Culottes

Also available in MobaXterm. Tools -> MobaKeyGen -> Load Key File -> Export OPENSSH Key – Vesper

worked for me like a charm. Thanks a ton. – Curriculum

Answer 2

Converting it to OpenSSH solves the issue. Just do the following:

1. Open Putty Key Generator.
2. Load the private key by going to File - > Private Key from the location you saved the private key file in - you should see your key loaded in Putty.
3. Now go to conversations and export to Openssh - save the file in a safe location.
4. Go to Workbench and under SSH key file point it to the new Openssh file instead of the old private key file. this should solve your problem.

Cramfull

Answer 3

I've just come across this again recently. If you use a password protected private key and you just upgraded to macOS Sierra you probably need to re-add your private key to your keychain again.

ssh-add -K ~/.ssh/id_rsa

This instantly fixed the problem for me.

Buttonhole

This fixed for me, MacOS High Sierra, v10.13.4 My id_rsa key is password protected. – Gally

It seems like Mysql Workbench picks automatically the id_rsa key, even if you specify a different one. – Devilmaycare

not fixed, didn't work, I've tried everything. Regenerated key 4 times. – Rosemarierosemary

Answer 4

On Mac OS, do the following as this is the only thing work for me -:

Step 1

brew install putty

Step 2

puttygen id_rsa -O private-openssh -o id_rsa.pem

Step 3 - In MySQL workbench

SSH Key File: /Users/local/.ssh/id_rsa.pem

Teakwood

Just a clarification: as a previous answer here shows, this approach creates a version of your existing id_rsa key, but with a .pem extension. I backed up my keys (pub/private) before doing this, but it worked like a charm. – Cordiecordier

Answer 5

I had the same problem on macos with both Navicat and MySQL Workbench. Thanks to Jonathan on this article

figure my issue out. Macos users first need to install puttygen and then convert ppk format into pem by means of Jonathan said in his tutorial and then TADA! everything works like a charm!

Infect

Answer 6

for linux and generally for workbench it must to be in pkcs8 format intead of rfc...

so export in OpenSSH format but in pkcs8

Allfired

I have this problem on Ubuntu. How would I convert key file to a PKCS#8? – Anaplastic

normally you can choose the format in the export command – Allfired

Yeah, but what is the keyword for PKCS#8? I searched around, but didn't find anything useful. I'm using this command: puttygen key-fil -O private-openssh -o new-key.pem. What should I use instead of private-openssh? – Anaplastic

sorry i used executable version, maybe you can use open ssh in gui and choose export – Allfired

Answer 7

For me, it worked using a .pem extension key. As I only had the ppk key, I had to transform the .ppk into .gem.

To transform it, I used eating in the ubuntu terminal:

$ puttygen key.ppk -O private-openssh -o key.pem

Gobbet

Answer 8

For Linux users using ssh-keygen:

As per the other answers you need to use openssh format.

ssh-keygen -o -b 4096

That gives me a new keypair RSA type 4096 bits in openssh format. It's the -o that's key here (no pun intended).

Obviously this generates a new key so is only useful if you can upload the new public key to the server. Don't forget to back up your old keys first incase you use them elsewhere.

AFAIK ssh-keygen doesn't have the ability to convert an existing key.

Why Oracle have dropped support for the ubiquitous PEM format is beyond me.

Apposite

Answer 9

I spent 3-4 days to figure out and tried many different solution but none of them worked for me except following.

I was on Windows and using XAMPP to run MySQL and localhost. I stopped both services on my machine and then tried to connect SSL connection using Workbench and its connected.

Iodize