MySQL Workbench 6.1: Updating accounts using the old (pre-4.1.1) authentication protocol

March 26, 2014 – 8:23 pm by Milosz Bodzek

In MySQL each ‘user’ has its own password hash. To provide better security, pasword hashes were extended from 16 to 41 bytes in MySQL 4.1.

This change created a situation. If the user was created prior to version 4.1 and the server updated to a newer version, the password hash that was stored in the database is left in the old, deprecated format. This is because MySQL doesn’t store passwords in plain text so there’s no way to automatically regenerate a password hash. For this case, we consider two scenarios:
- If the secure_auth server option is disabled, you can login and update your password. You may also need to enable the allow old_password option in the Workbench advanced options tab for the connection.
- If secure_auth is enabled, you do not have possibility to log in to the database and the only thing you can do is to disable that option or log in as different user (such as root) to change the password. In this case, attempt to log on to the server may fail with message:
“ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled)”
as in the screenshot below:

login_to_server

or, if you try log on in the Workbench, like this:

wb_login_to_server

In Workbench 6.1 an easy way to update your account to a format compatible with the new type of authentication has been added. Just log in to your account or some account that has permissions to manage other users (eg root) and go to the ‘Users and Privileges’ tab. Select the user whose account you want to update from the user list on the left. You should see ‘This account is using the pre-mysql-4.1.1 password hashing type. (…)’ in red in the lower right corner of the screen and the button [Upgrade] on the right, as the screenshot below:

upgrade_account

Enter a new or the current password and click the [Upgrade] button. After this operation, the user can login to his account without any further problems.

If you’re using a server 5.6 or later, we also offer a second way – generating a random password and marking it as expired. For this purpose, after selecting the user, leave the ‘password’ and ‘confirm a password’ fields untouched and immediately click on the [Upgrade] button. When you see the message shown in the screenshot below:

reset_to_expired

click on the [Reset to expired] button, it will generate a new random password, update your account and mark it as expired. The user will be able to log in to his account using the new password, but before being able to do anything else, they will need to change his password first.

Milosz Bodzek

  1. One Response to “MySQL Workbench 6.1: Updating accounts using the old (pre-4.1.1) authentication protocol”

  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

    By Shaun on Aug 1, 2014

Post a Comment


6 − two =