15

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)

enter image description here

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

tlum
  • 913
  • 3
  • 13
  • 30

7 Answers7

10

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.

tlum
  • 913
  • 3
  • 13
  • 30
  • I wasn't familiar with resetting a password within MySQL and found a good sample at the following URL: http://www.cyberciti.biz/faq/mysql-change-user-password/ – Jeff Oct 30 '13 at 16:44
  • And if you're not comfortable with the command line [phpMyAdmin](http://www.phpmyadmin.net/home_page/index.php) you'll not want to live without. – tlum Oct 30 '13 at 20:35
  • This in combination with STW's answer below set me right. – Jason Feb 24 '15 at 22:34
9

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

Md Azaharuddin Ali
  • 1,889
  • 17
  • 13
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

Sergey Bogdanov
  • 525
  • 9
  • 22
  • 1
    That doesn't really address the question and the problem is with deprecated password hash length, for some users accounts, created by those older client versions. Downgrading clients as a fix is disingenuous. Such accounts should be updated to use more secure password hashing. – tlum Jul 01 '13 at 13:45
3

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).

STW
  • 44,917
  • 17
  • 105
  • 161
1

I had a similar error message when remotely trying to access my MySQL database. Using Directadmin I easily changed the MySql database password as suggested above. This automatically generated the password using the newer hash method. This solved the remote connection problem instantly.

Shotputty
  • 31
  • 4
0

I found another solution in case anyone hits this - very weird -

  1. Install the 5.1 64 bit ODBC driver - verify an ODBC connection by itself works, if you can connect then you should be able to after doing #2
  2. Click on Linked Servers - Providers - right click on MSDASQL, click on Properties
    • uncheck "Allow inprocess" - which is a good thing to do unless you need to insert TEXT and NTEXT fields.
  3. Create your linked server connection or test the one you have been fighting with - lol

When I had "Allow inprocess" checked I still got the error even though the ODBC system DSN worked fine. I'm assuming because I had a mixture of 5.2 (with servers that worked fine) and 5.1 for the servers that didn't, SQL was sharing the processes because the 5.1 driver does not give that error.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Mike
  • 1
  • I believe that I already stated that below. "Different versions have treated the situation differently so..." This is not a solution, it's just sweeping the problem under the carpet. The hash size is deprecated. Newer clients do not support it. The fix is to rewrite the password with the deprecated length in a supported length. Anything else is just a workaround, one that is likely to come back and bite you, or someone else, again and again. OR, you've managed to find a new way to generate this error that is completely unrelated to the problem discussed so far. – tlum Dec 17 '13 at 02:56
0

If you can't change your server, perhaps you can change your client: http://bugs.mysql.com/bug.php?id=75425

niczero
  • 367
  • 1
  • 7