I am trying to connect to a legacy database using MySQL Workbench (8.0).
MySQL version is 5.6. I don't know much about MySQL, but I believe the problem may be to do with native passwords.
I can access the database via MySQL from the command prompt so I know I'm using the right credentials. However, when I try to connect via WorkBench I get the error:
Your connection attempt failed for user ... Authentication Plugin '' cannot be loaded. The specified module cannot be found.
I've googled on the web and tried any help I can find.
I've added default_authentication_plugin=mysql_native_password to my.ini. This had no effect. I did restart the server.
I've run a query via the command prompt to alter the user ALTER USER '[USERNAME]'@'[HOST]' IDENTIFIED WITH mysql_native_password BY '[PASSWORD]';. I get the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH mysql_native_password BY '[UserName]'' at line 1
I cannot see what syntax error there is in this query.
I've also noticed that in all the posts I've seen on this topic Workbench actually throws up the following error:
Authentication plugin 'caching_sha2_password' cannot be loaded.
You'll note from the error I'm getting that there is no plugin named at all.
I had this same issue. I was able to right-click my new connection info from MySQL Workbench and click the menu option for "open a command-line session" with no issue using the same login/password, so it had nothing to do with the MySQL Database user configuration. I resolved this by uninstalling the latest version of MySQL Workbench 8.0.40 I installed from MSI installer and installing an older version 8.0.12 from the MySQL complete installer (not MSI). You may be able to just use the complete installer version for 8.0.40 versus the MSI installer and still fix this - I did not try that yet.