C# 不再支持旧密码认证，使用 4.1 风格的密码

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

提问by user2237462

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:

我的网站带有 hostgator，我想使用 C# windows 应用程序访问 mysql 数据库，但是当我尝试连接时收到此消息：

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

“不再支持使用旧密码进行身份验证，请使用 4.1 样式密码”

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.

第一个查询成功执行，但在执行第二个查询时出现错误“用户@主机拒绝访问”。我不明白为什么会出现这个问题。我正在使用 MySQL-connecter-net 6.6.5。

I successfully connect my database with MySql workbench 5.2.47.

我成功地将我的数据库与 MySql 工作台 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

我已经联系了我的托管站点，他们对 my.cnf 文件进行了更改以使用 4.1 样式的密码。我可以连接 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.

但是当我尝试使用 mySQL 连接器网络 6.6.5 连接 C# 时，我再次收到此错误。

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

我在 Windows 8 64 位上使用带有 MySQL 连接器 6.6.5 的 Visual Studio 2012。这是我用来连接的代码

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

我无法理解问题出在哪里。请帮我

回答by maxcnunes

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

我有同样的问题。在我的情况下，只需运行以下通过 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:

之后，我可以在 c# 代码中使用 MySql Connector 6.6.5 与常见的 mysql 连接字符串连接：

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

回答by diangelisj

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.

今天我在我的 C# 应用程序中遇到了类似的问题。我尝试连接到 hostgator.com (mysql) 上的服务器并连接到 localhost。在阅读了许多帖子并进行了许多更改后，按照每一步操作，但我无法连接到我的 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.

我的解决方案是将 MySql Connector/NET 库从版本 6.* 更改为版本 5.*。更改后我的应用程序连接成功。

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

虽然这可能不是每个人都可行的解决方案，但它可能适用于某些人。

回答by tai1001

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!

好吧，可能服务器的连接版本比客户端低，所以你必须运行这个："SET old_passwords=FALSE;" 祝你好运！

回答by yoyo

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"

1）用mysql工作台连接数据库（选择“use old auth”和“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)

2）使用工作台，我运行了几次（因为它一直说'0行受影响'）：（还将用户ID和密码更改为适当的内容）

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...

3）现在回到你的应用程序，它应该运行......

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...

至少我是这样做的。我正在使用 IX mysql 并且他们确实在他们的服务器上使用了旧的身份验证，所以你必须在你的最后做一些事情......

回答by Stanojkovic

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. :)

尽管我尝试了所有建议的方法，但我还没有像其他人那样设法解决问题。最后，事实证明，我的数据库所在的服务器自 3.5.5 版以来没有更新 phpMyAdmin，并且目前在 4.1.14 版中到处都在使用，这是一个问题。解决方案是将您的主机 phpMyAdmin 或您自己的 mySQL 更新回早期版本，以便继续处理远程数据库。您可能需要这些知识来解决出现的一些疑问。:)

回答by Freestyle076

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

在 C# 中使用 MySqlClient 包时遇到了同样的问题。让我们分解一下：

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

我们需要数据库不使用旧式密码，因此我们执行

SET old_passwords=0;

SET old_passwords=0;

NOTICEthere 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.

注意没有“SESSION”关键字。这是只允许您的第一个查询工作的原因，“SESSION”意味着临时的，会话结束后任何会话设置都将丢失。

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

接下来我们需要将user@host的密码设置为新密码样式

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

为 user@host 设置密码 = PASSWORD('password');

This two should allow your MySqlClient

这两个应该允许你的 MySqlClient

回答by patitta

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.

今天我发现了同样的问题。我下载了工作台。工作台连接数据库有一些设置，如下： 1.选择选项卡SSL，在“使用SSL”中选择选项“否”。2.选择tabadvanced，在Use the ole authentication Protocol中选择。在工作台工作之后。并关注tai1001'帖子。这就是我发现所有有效步骤的全部内容。

回答by James M

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.

只是想添加到@diangelisj 答案并说问题的根源是您的服务器上安装的 PhpMyAdmin 版本。为了使用最新的 MySQL 数据连接器类，您必须拥有最新的 PhpMyAdmin（否则会出现问题）。就我而言，我有 PhpMyAdmin 4.0.10.7 并尝试使用 MySQL Data Connector 6.7.9，但出现错误。

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!

1. 从https://dev.mysql.com/downloads/connector/net/6.9.html下载 5.2.7 版本

2. 添加 .dll 作为对应用程序的引用

3. 快！

回答by Fabio Guerrazzi

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.

忘记应用 SET old_password=0 因为如果服务器配置为使用新密码，如果您的应用程序不再访问，则没有理由更改服务器上的标志。调查您正在使用的连接器 NET。

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

Connector 6.5.4 是正确的使用 old_password 的，rencent 版本使用的是新的。

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)

最好的做法是避免在机器上安装连接器，如果你已经有了它，就让 NET 来处理 dll，或者无论如何尝试找到 MySQL.Data.DLL 版本 6.5.4.0 (384 kbytes)