CODEWITHSUNDEEP
c#mysqlauthentication
user2237462
user2237462

Reputation: 231

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

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

Upvotes: 23

Views: 50977

Answers (9)

Fabio Guerrazzi
Fabio Guerrazzi

Reputation: 164

Forget about applying SET old_password=0 since if the server is configured to use new password there is no reason to change the flag on the server if your app does not access anymore. Investigate the connector NET that you are using.

Connector 6.5.4 is the right one to use old_password, rencent version are using the new one.

The best practice would be to avoid to install the connector on the machine, just leave the NET to handle the dll if you already have it or anyway try to find MySQL.Data.DLL version 6.5.4.0 (384 kbytes)

Upvotes: 1

user1618054
user1618054

Reputation:

Just wanted to add to @diangelisj answer and say the root of the problem is the version of PhpMyAdmin installed on your server. In order to use the latest MySQL Data Connector class, you have to have the latest PhpMyAdmin (or issues arise). In my case, I have PhpMyAdmin 4.0.10.7 and tried using MySQL Data Connector 6.7.9, which gave the error.

What I did:

  1. Download version 5.2.7 from https://dev.mysql.com/downloads/connector/net/6.9.html

  2. Add .dll as reference to application

  3. Presto!

Upvotes: 1

patitta
patitta

Reputation: 1

Today I found the same problem. I downloaded Workbench. There are some settings for workbench to connect database, as follows: 1.select tab SSL , select option 'no' in "Use SSL". 2. select tab advanced, select in Use the ole authentication Protocol. after that Workbench working. and follow tai1001' post. That are all I found all steps that work.

Upvotes: 0

Freestyle076
Freestyle076

Reputation: 1556

Had the same problem using MySqlClient package for C#. Let's break it down:

We need the database to not use old style passwords, thus we perform

SET old_passwords=0;

NOTICE there is no "SESSION" keyword. This is what allowed only your first query to work, "SESSION" implies temporary and any session settings are lost after a session ends.

Next we need to set the password of the user@host to the new password style

SET PASSWORD FOR user@host = PASSWORD('password');

This two should allow your MySqlClient

Upvotes: 4

diangelisj
diangelisj

Reputation: 167

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.

Upvotes: 4

Stanojkovic
Stanojkovic

Reputation: 1632

I have not managed to solve the problem as the others who have responded, although I tried out everything that has been proposed. In the end it turned out that the server on which stood my DB is not updated phpMyAdmin since version 3.5.5, and is currently being used everywhere version 4.1.14 and it is a problem. The solution is to update your host phpMyAdmin or your own mySQL back to an earlier version in order to continue working on remote DB. You may need this knowledge in order to solve some doubts that have arisen. :)

Upvotes: 0

yoyo
yoyo

Reputation: 79

I just solved my problem just like this:

1) Connect to the db with mysql workbench (select the "use old auth" and "send pass in cleartext"

2) Using the workbench, I ran the following several times (because it kept saying '0 rows affected'): (also change userID and password to your proper stuff)

SET SESSION old_passwords=0; 
SET PASSWORD FOR userID=PASSWORD('password');

SET SESSION old_passwords=false; 
SET PASSWORD FOR userID=PASSWORD('password');

3) Now go back to your app and it should run...

That is how I did it at least. I am using IX mysql and they do use old auth on their server so you must do something on your end...

Upvotes: 0

tai1001
tai1001

Reputation: 41

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!

Upvotes: 3

maxcnunes
maxcnunes

Reputation: 3027

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;"

Upvotes: 21

Related Questions