由于“身份验证协议被拒绝"，MySQL ODBC链接失败. [英] MySQL ODBC Link Fails due to "authentication protocol refused"

问题描述

我在附近进行了很好的搜索，但是还没有找到可以解决这个问题的答案.

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

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

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

我已经建立了一个用户帐户，该用户帐户在相关表上具有选择，插入，更新，删除特权.但是，当我尝试与此帐户建立联系时，会得到:

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:

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

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

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

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?

该错误似乎非常普遍，但我尚未找到给出明确解决方案的响应.你能帮忙吗?

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

推荐答案

此处的问题是由于两个并发问题引起的.

The problem here was due to two concurrent issues.

1. 在远程服务器上，全局设置OLD_PASSWORDS设置为1-表示正在使用4.1之前的方法对密码进行哈希处理.
尽管已进行了全局设置，但
2. 根帐户已使用较新的哈希方法进行了编码(大概是在设置此参数之前输入的)，这意味着已通过ODBC连接被授予访问权限，因为可以识别新的哈希.

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.

全局设置导致MySQL中的PASSWORD()函数对旧方法而不是新方法中的输入值进行哈希处理.

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.

出于充分的理由，我认为DBA已将OLD_PASSWORDS设置为1，以此作为我使用的解决方案

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

设置会话old_passwords = 0; 为用户'@'％'设置密码=密码('mypassword');

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

使用password()函数以前无法正常工作，因为全局设置OLD_PASSWORDS = 1导致它使用与OLD_PASSWORD()函数相同的哈希.会话设置允许它产生正确的密码.

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.

这篇关于由于“身份验证协议被拒绝"，MySQL ODBC链接失败.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！