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

Question

I have my website with hostgator and I want to access mysql database with C# windows application but when I tried to connect got this message:

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

I have tried given solution:

SET SESSION old_passwords=0;
SET PASSWORD FOR user@host=PASSWORD('your pw here');

first query executed successfully but I got the error "Access denied for user@host" when second query executed. I can't understand why there is this problem. I am using MySQL-connecter-net 6.6.5.

I successfully connect my database with MySql workbench 5.2.47.

Can anyone help me what I can do more?

---

I have contact my hosting site and they make changes to my.cnf file to use 4.1 style password. and i am able to connect with mysql

mysql -u <username> -p <password> -h <hostname>;

but when i tried to connect with C# with mySQL connecter net 6.6.5 i again got this error.

I am using Visual Studio 2012 with MySQL connector 6.6.5 on Windows 8 64 bit. this is the code i used to connect

using MySQL.Data.MySQLClient;

private void button1_Click(object sender, EventArgs e)
            {
                string connStr = String.Format("server={0};port={1};uid={2};password={3};database={4}",
                    txtserver.Text, txtPort.Text, txtUser.Text, txtPassword.Text, txtDatabase.Text);
                conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    MessageBox.Show("Test Connection Succeded");
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

I can't understand where the problem is. Please Help me

Answer 1

I had the same problem. In my case just ran the command below connected on my database by the Workbench:

SET SESSION old_passwords=0; SET PASSWORD FOR my_user=PASSWORD('my_password'); 

After that I could connnect using MySql Connector 6.6.5 in the c# code with the common mysql connection string:

"server=my_server_ip;user=my_user;database=my_db;port=3306;password=my_password;" 

Answer 2

Today I had a similar problem in my C# application. I tried connecting to a server at hostgator.com (mysql) and connecting to localhost. After reading many posts and making many alterations, following every step but I couldn't connect to my hostgator mysql.

The solution in my case was to change the MySql Connector/NET library from version 6.* to version 5.*. After the change my application connected successfully.

While this might not be a viable solution for everyone, it may work for some.

Answer 3

I met this problem today, but I'v fixed it as below:

SET old_passwords=FALSE;
SET PASSWORD = PASSWORD('new pwd here');

Well, maybe the server's connection version is lower than the client, so you have to run this:"SET old_passwords=FALSE;" Good Luck!