Hashing MySql passwords gives: "Authentication with old password no longer supported, use 4.1 style passwords"

Question

I try connect to a MySql (version 5.0.95) database from a ASP.NET (Razor) web page...
(Assembly MySql.Data.dll, v6.6.5.0)

First I tried directly to specify directly the password in the connection string like "...;pwd=myClearPassword". But When trying to Open obtained the error

"Authentication with old password no longer supported, use 4.1 style passwords."

So I try now to update my code, like this:

@using MySql.Data.MySqlClient
@using System.Security.Cryptography

private const string ConnectionStringFormat = 
   "server=xxx.xx.xxx.xx;database=mydbname;uid=username;pwd={0};";      

private string GetHashedPassword(string clearPassword)
{
    var hasher = new SHA256Managed();
    var unhashedPassword = System.Text.Encoding.Unicode.GetBytes(clearPassword);
    var hashedBytes = hasher.ComputeHash(unhashedPassword);
    var hashedPassword = Convert.ToBase64String(hashedBytes);

    return hashedPassword;
}

public Dictionary<int, string> GetIdStringCollectionFromTable(string tableName)
{
    var hasehdPassword = GetHashedPassword("myClearPassword");
    var connectionString = string.Format(ConnectionStringFormat, hasehdPassword);
    MySqlConnection conn = new MySqlConnection(connectionString);
    conn.Open(); // >>>>> ERROR !!!!!!!!

    var sqlCommand = "select id, name from {0}".Fill(tableName);
    MySqlCommand command = new MySqlCommand(sqlCommand, conn);
    var reader = command.ExecuteReader();

    Dictionary<int, string> list = new Dictionary<int, string>();
    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string text = reader.GetString(1);
        list.Add(id, text);
    }
    return list;
}

However I obtain the same error. Where is the problem?

PS.

On the SQL server, the old_passwords variable is set to ON (default value is OFF). We don't control the server so this variable should remain unchanged.

NB.
There is a lot of questions with the same title. However please not to make this question as duplicate, because of different context of the question. I was mainly inspired from this answer for the code above...

Answer 1

First, a couple things about authentication in general;

There are normally two types of authentication used in developing applications:

database authentication - This is how the application authenticates for access to the database

user authentication - This is how the user authenticates to your application for access

The article you link to above is talking about user authentication, whereas your question is actually about database authentication.

The original hashing algorithm used by MySQL (prior to 4.1) has been deemed to be unsecure. Version 4.1 implements a new hashing algorithm. The password in your connection string does not need to be hashed, the hashing is performed internally in your .Net connector during authentication to the database (it is done for you). The problem is if you've upgraded your database from a pre-4.1 version and not reset the password to use the new hashing.

You can do either of two things to rectify the situation. These scripts are run at the database.

1. To allow the database to accept the oldstyle hash run

SET old_passwords=TRUE

2. Set a new password using the new hashing

SET old_passwords=FALSE

SET PASSWORD=PASSWORD('your_new_password_here')

The suggestion is to use the second method and use the new hashing algorithm because it makes your database access more secure.