MySQL authentication error

[johnc]

Hi Members,

When trying to access the website it shows Error Establishing a Database Connection. No changes were made to the connection string.

On my VPS when I try to connect the database with the MySQL username set in config file, I see the error below.

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

Anybody here who can help ?

[Administrator]

Hi John,

It looks like MySQL 5.x version is still set to default old password hashes. You need to change this in my.cnf by adding the line below.

old_passwords = 1

Restart MySQL else it will keep using the old password format, which means that you cannot upgrade the passwords using the builtin PASSWORD() hashing function. Execute the command below at MySQL prompt

SELECT Length(PASSWORD('xyz'));

+-------------------------+
| Length(PASSWORD('xyz')) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

New password hashes are 41 characters where as old are 16 characters.

You will have to change the format of all the passwords in the database to the new format. Below command will show the users with 16 OR 41 characters.

SELECT user,  Length(`Password`) FROM   `mysql`.`user`;

It will show all the MySQL users. A sample example is as below.

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| abc_xyz    |                 16 |
| lmn_opq    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification). To update the password for your MySQL user, execute the command at MySQL prompt.

UPDATE mysql.user SET Password = PASSWORD('MySQL-User-Password') WHERE user = 'abc_xyz';

Simply flush the privileges now & check if you can connect.

Note: Replace MySQL-User-Password with your MySQL username's password in the config file & abc_xyz with your actual MySQL username.

Let me know if the above solution helps.

[johnc]

Hey Admin,

Thanks mate. I am stuck with flushing the privileges. What is the command to Flush MySQL privileges ?

Cheers. 

[Administrator]

Hi John,

Simply execute the command below.

FLUSH PRIVILEGES;

[johnc]

Bang on target Admin ! I followed the steps you posted. mysql connects & website works.

I must say, I asked for 2 solutions in few days on this forum. Got the solution in time. Bravo admin 

I have bookmarked you guys & you will get few signups from me in coming days.