Trying to connect to MySQL database server through MySQL Workbench using SSH

Try converting your PuTTy key to the OpenSSH format:

1. Open PuttyGen
2. Click Load
3. Load your private key
4. Go to Conversions->Export OpenSSH and export your private key
5. Leave the SSH Password field blank and select the converted private key

Does that work? Also, are you using MySQL’s root password or your Drupal MySQL user’s password?

I had done all of the above and still was not able to connect workbench to the mysql db in my lemp droplet.

For the default lemp droplet on the marketplace, the root mysql user is configured to authenticate via auth_socket instead of password. This allows you to connect using the mysql prompt when connected to the droplet via ssh, but not from outside (even though the ssh tunnel connection part of your workbench connection profile is working). If you are getting an error from mysql workbench when trying to connect of: Access denied for user 'root'@'localhost', this is likely the cause.

To fix it, follow the instructions at Configuring Password Access for the MySQL Root Account here: https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-20-04#step-2-—-adjusting-user-authentication-and-privileges to change from auth-socket to caching_sha2_password or the older mysql_native_password and you should be able to connect. You can change it back to auth_socket later when you are done if you are concerned about security.

This looked promising to me, but i’m unable to connect using this method.

When i test the connection it tells me:

Could not connect the SSH Tunnel Authentication error, unhandled exception caught in tunnel manager, please refer to logs in details

In the mysql workbench logs i see:

17:37:43 [INF][ SSH tunnel]: TunnelManager.wait_connection authentication error: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:37:43 [ERR][ SSH tunnel]: Authentication error opening SSH tunnel: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:38:49 [INF][ SSH tunnel]: Existing SSH tunnel not found, opening new one 17:38:49 [INF][ SSH tunnel]: Opening SSH tunnel to xxx.xxx.xxx.xxx:22 17:38:49 [WRN][sshtunnel.py:_connect_ssh:288]: IOError, probably caused by file C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\known_hosts not found, the message was: [Errno 2] No such file or directory: u’C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\known_hosts’ 17:38:49 [ERR][sshtunnel.py:notify_exception_error:233]: Traceback (most recent call last): File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py”, line 298, in _connect_ssh look_for_keys=has_key, allow_agent=has_key) File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py”, line 301, in connect t.start_client() File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\transport.py”, line 461, in start_client raise e SSHException: Incompatible ssh peer (no acceptable kex algorithm)

17:38:50 [INF][ SSH tunnel]: TunnelManager.wait_connection authentication error: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:38:50 [ERR][ SSH tunnel]: Authentication error opening SSH tunnel: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details

Any ideas on what this might be?