由于“身份验证协议被拒绝”，MySQL ODBC 链接失败

MySQL ODBC Link Fails due to "authentication protocol refused"

提问by Ambulare

I've had a good search around but not yet found an answer that allows me to rectify this problem.

我已经进行了很好的搜索，但还没有找到可以让我纠正这个问题的答案。

I'm trying to connect from MS Access to a MySQL 5.2 database on a remote server.

我正在尝试从 MS Access 连接到远程服务器上的 MySQL 5.2 数据库。

I've set up a user account which has select, insert, update, delete privileges on the table in question. However, when I try to connect with this account, I get:

我已经设置了一个用户帐户，该帐户对相关表具有选择、插入、更新、删除权限。但是，当我尝试连接此帐户时，我得到：

Connection Failed [HY000][MySQL][ODBC 5.2(w) Driver] Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

连接失败 [HY000][MySQL][ODBC 5.2(w) 驱动程序] 使用旧（4.1.1 之前）身份验证协议的连接被拒绝（客户端选项“secure_auth”已启用）

When I try to access with the root account, the connection works, which makes me wonder why it's returning an authentication protocol error, since the same driver is being used - all that's changing is the account used - so surely is passing the password via the same authentication protocol? Why would one username/password combination return a protocol error but not another?

当我尝试使用 root 帐户访问时，连接有效，这让我想知道为什么它返回身份验证协议错误，因为正在使用相同的驱动程序 - 所有改变的是使用的帐户 - 所以肯定是通过密码传递相同的身份验证协议？为什么一个用户名/密码组合会返回协议错误而不是另一个？

It seems this error is very common, but I haven't yet found a response that gives a clear solution. Can you help?

这个错误似乎很常见，但我还没有找到给出明确解决方案的回应。你能帮我吗？

采纳答案by Ambulare

The problem here was due to two concurrent issues.

这里的问题是由于两个并发问题。

1. On the remote server, the global setting OLD_PASSWORDS was set to 1 - meaning that passwords were being hashed in the pre-4.1 method.
2. the root account had been encoded in the newer hashing method despite the global setting (presumably it had been entered before this parameter was set), meaning that it was being granted access via the ODBC connection because the new hashing was beign recognised.

1. 在远程服务器上，全局设置 OLD_PASSWORDS 被设置为 1 - 这意味着密码在 4.1 之前的方法中被散列。
2. 尽管有全局设置（大概在设置此参数之前已输入），root 帐户仍以较新的散列方法编码，这意味着它被授予通过 ODBC 连接的访问​​权限，因为新的散列已被识别。

The global setting caused the PASSWORD() function in MySQL to hash any entered value in the old method instead of the new method as it was supposed to.

全局设置导致 MySQL 中的 PASSWORD() 函数对旧方法中输入的任何值进行哈希处理，而不是像预期的那样对新方法进行哈希处理。

I assumed that the DBA had set OLD_PASSWORDS to 1 for a good reason so as a solution I used

我认为 DBA 有充分的理由将 OLD_PASSWORDS 设置为 1，因此作为我使用的解决方案

Set session old_passwords=0; set password for 'user'@'%' = password('mypassword');

设置会话 old_passwords=0; 为 'user'@'%' 设置密码 = password('mypassword');

Using the password() function had not worked previously because the global setting OLD_PASSWORDS=1 caused it to use the same hashing as the OLD_PASSWORD() function. The session setting allowed it to produce the correct password.

使用 password() 函数以前不起作用，因为全局设置 OLD_PASSWORDS=1 导致它使用与 OLD_PASSWORD() 函数相同的散列。会话设置允许它生成正确的密码。

回答by Justin

"When I installed 5.1.12 instead, I had no problems"

“当我安装 5.1.12 时，我没有问题”

Ditto! Just successfully authenticated into MySQL using ODBC driver version 5.1.12. I have NO idea why MySQL doesn't provide a better way to handle this error w/ its newer drivers, but i can confirm that using ODBC driver 5.1.12 DOES work.

同上！刚刚使用 ODBC 驱动程序版本 5.1.12 成功通过 MySQL 身份验证。我不知道为什么 MySQL 没有提供更好的方法来处理带有更新驱动程序的此错误，但我可以确认使用 ODBC 驱动程序 5.1.12 确实有效。

Here is a link to the 5.1.12 ODBC driver installation page.

这是 5.1.12 ODBC 驱动程序安装页面的链接。

http://dev.mysql.com/downloads/file.php?id=411741

http://dev.mysql.com/downloads/file.php?id=411741

回答by Beth

I was getting the same error message, while my co-worker was not having any problems. I was trying to use MySQL ODBC driver 5.1.13 (the latest GA version available) and she had 5.1.12. When I installed 5.1.12 instead, I had no problems.

我收到相同的错误消息，而我的同事没有任何问题。我试图使用 MySQL ODBC 驱动程序 5.1.13（可用的最新 GA 版本），而她有 5.1.12。当我安装 5.1.12 时，我没有问题。