MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

Question

when I tried to restore all database dump which is in 5.0 version to 5.6 version, it got restored and after that when I tried to reconnect, am getting the following error

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)..

I have tried adding the following lines in My.ini and restarted the service,but the issue persist till.

skip-grant-tables The following link says its a bug in MYSQL.

https://github.com/santisaez/powerstack/blob/master/packages/mysql/mysql-powerstack-secure_auth.patch

Do anyone have any fixes for this solution?

Answer 1

This is not a bug if you have user accounts with passwords that used the ancient old hashing algorithm. If you read the bug report mentioned in the link you posted:

http://bugs.mysql.com/bug.php?id=69027

[1 May 15:24] Todd Farmer

The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash. This is really a recommended best practice, regardless - password hashing and authorization process prior to 4.1 has notable security limitations (discussed in documentation at http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html).

Restoring a 5.0 version of the mysql schema on to a 5.6 server is a bad idea at any rate, because 5.6 has additional columns in some tables and some completely new tables, which may or may not be missing now depending on how you configured mysqldump when you created the dump file. You may have caused other problems that you may not see immediately.

Also, I didn't see skip-grant-tables mentioned in the article ... but if you correctly apply that option to the server, all authentication is bypassed and you should be able to log in and reset passwords.

Answer 2

On the command line, use something like the following, if you have no choice...

mysql -uTheUseerNAme -pThePassword DbName -h HostName --skip-secure-auth

Hope this helps someone, as this was my problem connecting from a Linux

Answer 3

If you use MySQL Workbench, you need to check this option:

Answer 4

This is really meant as a comment on the previous answer, but is too big to fit into a StackExchange comment.

I too was suffering from this problem. So I created a new user with a new-style hash, and now use that new user with no trouble. Here's what I did:

    [172.16.2.222:mysql Thu Nov  7 16:16:25 2013]> use mysql;
    Database changed
    [172.16.2.222:mysql Thu Nov  7 16:22:23 2013]> describe user;
    describe user;
    +-----------------------+-----------------------------------+------+-----+---------+-------+
    | Field                 | Type                              | Null | Key | Default | Extra |
    +-----------------------+-----------------------------------+------+-----+---------+-------+
    | Host                  | char(60)                          | NO   | PRI |         |       |
    | User                  | char(16)                          | NO   | PRI |         |       |
    | Password              | char(41)                          | NO   |     |         |       |

I was happy to see that our Password column was already wide enough to hold new-style hashes. (Had it been less than 41 characters wide, I might not have had the courage to widen it :-)

    [172.16.2.222:mysql Thu Nov  7 16:13:10 2013]> show variables like '%pass%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | old_passwords   | ON    |
    | report_password |       |
    +-----------------+-------+
    2 rows in set (0.06 sec)

old_passwords being ON is clearly the problem, so I temporarily changed it:

    [172.16.2.222:mysql Thu Nov  7 16:13:59 2013]> set session old_passwords = 'OFF';
    Query OK, 0 rows affected (0.05 sec)

    [172.16.2.222:mysql Thu Nov  7 16:14:12 2013]> show variables like '%pass%';
    show variables like '%pass%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | old_passwords   | OFF   |
    | report_password |       |
    +-----------------+-------+
    2 rows in set (0.06 sec)

Then I created a new user:

    [172.16.2.222:mysql Thu Nov  7 16:14:16 2013]> create user 'erich' IDENTIFIED BY 'SEKRIT PASSWORD';

... and took a look at the new hash:

    [172.16.2.222:mysql Thu Nov  7 16:14:26 2013]> select * from user order by User;
    +-----------+--------------+-------------------------------------------+--------
    | Host      | User         | Password                                  | Select_
    +-----------+--------------+-------------------------------------------+--------
    | localhost | someguy      | 3d9505dd323e53f1                          | Y      
    | %         | someotherguy | 79b3df3b004bb855                          | Y      
    | %         | erich        | *D2589EF6B59146801234567897BB190123456789 | N      
    | %         | anotheroldguy| 60577e0d77b9212b                          | Y      

Note how my hash is bigger than the others!

Just to be tidy, I set old_passwords back to OFF. This was probably pointless, since I can't think why anyone would want to create new users using old passwords, but who knows.

Anyway: this solved it for me.