Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection
I'm running 2 MySQL 8.0.36 servers on a Windows server using the same configuration except the data and temp directories, as well as the TCP ports and the socket names. The SSL is disabled on both servers.
When I try to connect with the MySQL8's client from the command line I'm able to connect to one of the servers, but for the other it fails with the error in the title.
have_ssl is disabled on a session and global level on both servers, authentication_policy is set to *,, on both servers as reported via SHOW (GLOBAL) VARIABLES ..., default_authentication_plugin is set to caching_sha2_password on both servers. The mysql.user.plugin is set to caching_sha2_password for the particular user on both instances.
The only difference between the servers is that the working one was migrated from 5.7 while the failing one was freshly initialized using the MySQL8 tools.
How to solve this inconsistency ?
Answers
The error message "Client does not support authentication protocol requested by server; consider upgrading MySQL client" typically occurs when the MySQL server is configured to use an authentication plugin that is not supported by the MySQL client being used.
In your case, since you have disabled SSL and both servers have authentication_policy set to ,, and default_authentication_plugin set to caching_sha2_password, it seems that the configuration is consistent between the two servers.
However, the fact that one server was migrated from MySQL 5.7 while the other was initialized using MySQL 8 tools might be causing the inconsistency. This is because MySQL 8 introduced changes to the authentication system, including the default authentication plugin.
To troubleshoot and resolve the issue, you can try the following steps:
-
Check MySQL Client Version: Ensure that you are using a MySQL client version that is compatible with MySQL 8. Since you mentioned that one server was migrated from MySQL 5.7, make sure that the MySQL client being used to connect to the server is also upgraded to a version that supports the new authentication protocol introduced in MySQL 8.
-
Verify Authentication Plugin: Double-check the authentication plugin being used by the MySQL user account you are trying to authenticate with on the failing server. Ensure that it is set to caching_sha2_password, as expected.
-
Update MySQL Client: If you are using an outdated MySQL client, consider upgrading it to a newer version that supports the authentication protocol used by MySQL 8. You can download the latest MySQL client from the official MySQL website or use a package manager to update it.
-
Review Server Logs: Check the MySQL error log on the failing server for any additional information or error messages that might provide clues about the cause of the authentication failure. The error log can often provide valuable insights into what went wrong during the authentication process.
-
Test with Different Clients: Try connecting to the MySQL server using different MySQL clients to see if the issue persists across multiple clients. This can help determine if the problem is specific to a particular client or if it is a more general issue.
-
Recreate User Account: As a last resort, you can try recreating the MySQL user account on the failing server, ensuring that it is configured with the correct authentication plugin and privileges.
By following these steps and investigating the potential causes of the authentication failure, you should be able to identify and resolve the inconsistency between the two MySQL servers. If you continue to encounter issues, consider reaching out to the MySQL community or MySQL support for further assistance.
