Unable to connect to database with MySQL Workbench

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.