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:
or, if you try log on in the Workbench, like this:
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:
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:
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