MySQL ODBC Link Fails due to "authentication protocol refused"

Question

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.

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)

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?

Answer 1

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.

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.

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

Set session old_passwords=0; set password for '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.

Answer 2

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

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.

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

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