Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Question

I have a problem when trying to connect to MySQL database using Windows OBDC driver. There are plenty of search hits regarding the obvious... people are using old versions, however, I'm not.

mysqld is on CentOS 6.4 32bit

./usr/libexec/mysqld  Ver 5.1.69 for redhat-linux-gnu on i386 (Source distribution)

So I'm at a loss to understand where any pre 4.1.1 protocol is coming from. Any ideas?

Answer 1

I guess that if you ask the right question its easier to find the answer.

In this case "my" problem relates to how the passwords are hashed and stored in the database. Legacy passwords were stored with a shorter hash that's now deprecated.

A few important points:

mysql_upgrade cannot and does not upgrade passwords, nor does it warn about it in some versions, see: http://bugs.mysql.com/bug.php?id=65461.

Even it you have mostly the latest server and clients, all it takes is one legacy client somewhere to create a legacy password and then you'll have trouble with that account no matter what client tries to use it.

Different versions have treated the situation differently so you can be sitting on some legacy passwords in your database and then suddenly, for no apparent reason, some accounts stop working... this is because of how different versions chose to handle the situation.

You cannot upgrade passwords. You must know what they are and you must change them.

EDIT: To be more clear, you must change the password that is stored with the shorter hash using a new client that uses longer hashes. By doing so you will be writing that accounts password with the longer hash, at which point nothing should be flagging attempts to access the account any more. If the problem is recurrent you should be looking for the older clients at your site which are still writing passwords with the deprecated hash length.

Answer 2

MySQL Workbench 6.08 in the Manage Server Connections, Connection tab, Advanced sub-tab you must check the box 'Use the old authentication protocol.'

Answer 3

Try installing old version driver 3.51.30: http://dev.mysql.com/downloads/connector/odbc/5.1.html#downloads It works on my Mysql Ver 5.0.24a-community

Answer 4

I ran into this while using the ODBC Connector for Windows to connect to a Percona 5.5 server. which has secure_auth disabled.

From what I found the ODBC connector, unlike MySql Workbench, does not support an option to authenticate logins which use the old 16-byte hashed passwords. There is a bug report regarding this, but it appears the assignee is/was confused about the feature request (See bug #71234).

I was able to update the mysql login to use the new 41-byte hash using these commands:

 set old_passwords=0;
 set password=password('yourpasswordhere');

As I mentioned our server has secure_auth disabled, which appears to cause password() to return old_password() results. Running set old_passwords=0; will enable the password() method to generate the new 41-byte hashes (for the duration of your session).