NetCore Mysql 身份验证方法“caching_sha2_password”失败:Authentication method ‘caching_sha2_password‘ failed._authentication method 'caching_sha2_password' fail

最新部署的Net6 Webapi项目，服务器重新启动之后连接mysql数据库偶尔会出现错误信息：Authentication method 'caching_sha2_password' failed. Either use a secure connection, specify the server's RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True.

经过一番查找：对于不安全的连接，不启用 RSA 公钥的检索
从 C# 程序连接到 MySQL 服务器时，您可能会收到以下错误之一：

MySqlException (0x80004005)：不为不安全的连接启用 RSA 公钥检索。（连接器/网络）
身份验证方法“caching_sha2_password”失败。使用安全连接，使用 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或设置 AllowPublicKeyRetrieval=True。(MySqlConnector)
身份验证方法“sha256_password”失败。使用安全连接，使用 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者设置 AllowPublicKeyRetrieval=True。(MySqlConnector)
使固定
使用以下修复程序之一。（注意：如果使用MySql.Data（连接器/NET），请先卸载它然后安装 MySqlConnector。）

;SslMode=Required（首选）通过添加到连接字符串来使用安全连接。
;ServerRSAPublicKeyFile=path/to/file.pem通过添加到连接字符串来指定包含服务器公钥副本的（本地）路径。要检索服务器的公钥，请安全地连接到服务器并执行以下查询，将结果保存在文件中：SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key';
;AllowPublicKeyRetrieval=true（不推荐）通过添加到连接字符串来自动检索服务器的公钥；这可能是不安全的。
背景
MySQL Server 5.7 添加了sha256_password身份验证插件。MySQL Server 8.0 添加了caching_sha2_password身份验证插件 并使其成为默认插件。这些插件使用 RSA 公钥加密来保护传输中的用户密码。

正如MySQL 服务器团队所写：

安全地分发密钥可能是一个令人头疼的操作问题。MySQL 服务器将根据客户端的请求提供自己的 RSA 公钥，因此不必为每个客户端显式分发和配置密钥。但这引入了另一个安全问题——中间的代理可能会替换它拥有私钥的 RSA 公钥，解密并获取明文密码，然后使用实际服务器的 RSA 公钥重新加密密码连接尝试继续。出于这个原因，强烈建议客户端定义一个本地 RSA 公钥来使用，而不是在握手期间请求服务器 RSA 密钥。

默认情况下，客户端库不会发送密码，除非可以建立安全连接（使用 TLS 或 RSA 公钥加密）。为避免 MITM 攻击，RSA 公钥不会以明文形式发送。对于 Connector/NET，您可以使用 TLS ( SslMode=Required) 来保护 RSA 公钥。使用 MySqlConnector，您还可以选择直接指定服务器的公钥，使用该ServerRSAPublicKeyFile选项，或使用AllowPublicKeyRetrieval=true.
 

英文版：Fix: Retrieval of the RSA public key is not enabled for insecure connections - MySqlConnector

Problem

When connecting to MySQL Server from a C# program, you may receive one of the following errors:

• MySqlException (0x80004005): Retrieval of the RSA public key is not enabled for insecure connections. (Connector/NET)
• Authentication method ‘caching_sha2_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector)
• Authentication method ‘sha256_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector)

Fix

Use one of the following fixes. (Note: if using MySql.Data (Connector/NET), uninstall it first then install MySqlConnector.)

• (Preferred) Use a secure connection by adding ;SslMode=Required to the connection string.
• Specify the (local) path that contains a copy of the server’s public key by adding ;ServerRSAPublicKeyFile=path/to/file.pem to the connection string. To retrieve the server’s public key, connect securely to the server and execute the following query, saving the results in a file: SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key';
• (Not recommended) Automatically retrieve the server’s public key by adding ;AllowPublicKeyRetrieval=true to the connection string; this is potentially insecure.

Background

MySQL Server 5.7 added the sha256_password authentication plugin. MySQL Server 8.0 adds the caching_sha2_password authentication plugin and makes it the default. These plugins use RSA public key encryption to protect the user’s password in transit.

As the MySQL Server Team writes:

Distributing keys securely can be an operational headache. MySQL Server will supply its own RSA public key upon request from the client, so that the key doesn’t have to be explicitly distributed and configured for each client. But this introduces another security concern – a proxy in the middle may substitute an RSA public key for which it has the private key, decrypt and harvest the plain-text password, then re-encrypting the password with the actual server RSA public key for the connection attempt to continue. For this reason, it’s strongly recommended that clients define a local RSA public key to use instead of request the server RSA key during the handshake.

By default, client libraries will not send the password unless a secure connection (using TLS or RSA public key encryption) can be established. To avoid a MITM attack, the RSA public key will not be sent in plain text. For Connector/NET, you can use TLS (SslMode=Required) to protect the RSA public key. With MySqlConnector, you also have the option of specifying the server’s public key directly, using the ServerRSAPublicKeyFile option, or allowing a potentially-insecure connection by using AllowPublicKeyRetrieval=true.