cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
FransBouma
Posts: 29
Joined: Fri 25 Nov 2005 10:43

cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by FransBouma » Thu 10 Jan 2019 09:40

Hi. (Using 8.12.1307, download an hour ago. .NET Full)

I'm the lead dev of LLBLGen Pro and we use devart dotconnect for mysql as the ado.net provider for MySQL. A customer of ours needs to connect to a MySQL 8.0 database using an account which has 'cached_sha2_password'. We don't specify an authentication plugin in the connection string (just server, user id, port nr). This results in:

Exception type: InvalidOperationException
Unknown authentication plugin 'caching_sha2_password'.

This is odd, considering https://www.devart.com/dotconnect/mysql ... nType.html
This option may be omitted because the client may receive the name of the authentication plugin when performing handshake, but you may use this option to shorten client/server negotiation and avoid a round trip in the protocol.
So I tried to reproduce this and I can with a default user having 'cached_sha2_password' as authentication type set in MySQL 8. I see there's a connection string argument 'Default Auth Plugin' (https://www.devart.com/dotconnect/mysql ... nType.html), so we tried to specify ";Default Auth Plugin=cached_sha2_password" in the connectionstring, but this gives:

Exception type: NotSupportedException
Keyword not supported: 'default auth plugin'.

so I'm at a loss how to use this at all. Is this even supported? Using 'Standard' as Authentication Type on the MySQL 8 user works fine.

Shalex
Site Admin
Posts: 8499
Joined: Thu 14 Aug 2008 12:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Shalex » Fri 11 Jan 2019 20:23

The authentication plugin caching_sha2_password is supported by dotConnect for MySQL starting from 8.12.1190 19-Jul-18. It works with v8.12.1307 as well on our MySQL Server v8.0.11.

1. Please run your code in the debug mode and make sure that the following Devart.* assemblies are loaded in the process of your application (via Debug > Windows > Modules):
* Devart.Data.dll v5.0.2099.0
* Devart.Data.MySql.dll v8.12.1307.0

2. Specify your connection string (roughly, without credentials).

3. Give us the full stack trace of the error with all inner exceptions.

4. Tell us your default_authentication_plugin value in my.ini.

FransBouma
Posts: 29
Joined: Fri 25 Nov 2005 10:43

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by FransBouma » Mon 14 Jan 2019 12:31

Hi

Here's some answers that might help finding what's going on. We use DbProviderFactories.GetFactory() to obtain the factory instance for the ADO.NET provider and create objects using that. It works fine if I switch the user's authentication type on the MySQL server to 'standard'. having it at 'cachingZ_sha2_password' fails with the exception below. The connection string given below does contain the right username/password (replaced with xxx) and if I add the Default Auth Plugin it gives the other error I mentioned so I removed that for this test.

1. I use Rider, this is the code to open the connection,

Code: Select all

DbConnection toReturn = this.GetDbProviderFactory().CreateConnection();
toReturn.ConnectionString = GetConnectionStringToUse();
When I then evaluate 'toReturn' when the execution point is on the second line:
toReturn.GetType().Assembly.FullName
gives:
Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

So I can only conclude, it uses the latest version.

2. Server=windows2008r2.sd.local;Port=3308;Database=mysql;User ID=xxxxxxx;Password=xxxxxxxxx

3. Exception message:
-------------------------------
Exception type: MySqlException
Can't connect to MySQL server on 'windows2008r2.sd.local' (10061): Authentication failed.

Inner exception message:
-------------------------------
Exception type: InvalidOperationException
Unknown authentication plugin 'caching_sha2_password'.

LLBLGen Pro version 5.5. Build 5.5.1
-----[Core exception]--------------------
at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
at Devart.Data.MySql.q.a(MySqlConnection A_0, String A_1, String A_2, String A_3, String A_4, Int32 A_5, Int32 A_6, MySqlProtocol A_7, Boolean A_8, Boolean A_9)
at Devart.Data.MySql.q..ctor(ao A_0, MySqlConnection A_1)
at Devart.Data.MySql.am.a(DbConnectionOptions A_0, Object A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, DbConnectionOptions A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0, DbConnectionBase A_1)
at Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.MySql.MySqlConnection.j()
at Devart.Data.MySql.MySqlConnection.Open()
at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.TestConnectionData(Boolean showSuccess) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Designer\Gui\Controls\WizardPages\MetaDataRetrievalWizard_Step_ConnectionData.cs:line 245
-----[InnerException]--------------------
at Devart.Data.MySql.l.a(c A_0)
at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)

4.# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=mysql_native_password

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Fri 18 Jan 2019 15:45

You have the error when you use the "Default Auth Plugin" parameter in your connection string. And you have the "Unknown authentication plugin 'caching_sha2_password'." error when you do not add this parameter to your connection string. We think that the reason is that old Devart assemblies are used in your project, despite you get "Devart.Data.MySql, Version=8.12.1307.0" using reflection.

Please create a new console application:
I. Add Devart assemblies from the GAC or via the NuGet package (depending on how you usually add Devart assemblies).
II. Add the following code:

Code: Select all

MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "Server=windows2008r2.sd.local;Port=3308;Database=mysql;User ID=xxxxxxx;Password=xxxxxxxxx;Default Auth Plugin=cached_sha2_password;";

conn.Open();
Console.WriteLine(conn.ServerVersion);
conn.Close();
Just replace User ID and Password with corresponding values.

III. Does this application run successfully? If you get an error, then run this application in the debug mode and open the Modules window. You will see there Devart assemblies that are in use and their versions.


You can also perform the following reinstallation:
1. Uninstall dotConnect for MySQL (if it is currently installed)
2. Delete all Devart.* and policy.*.Devart.* files in the following folders:
* GAC 2.0 (\Windows\assembly\GAC_MSIL\)
* GAC 4.0 (\Windows\Microsoft.NET\assembly\GAC_MSIL\)

You can use any file manager other than Explorer (e.g.: Total Commander) in order to navigate to C:\Windows\assembly\GAC_MSIL\ .
If you don't have any file manager other than Explorer, then you can use Explorer with the GAC shell extension turned off. The GAC shell extension connects to Explorer using the Desktop.ini file located in the assembly folder. Rename the Desktop.ini file to display the content of the assembly folder like content of any other folder. For this, run the following three commands in Command Prompt:
1) Go to the GAC folder:
cd %windir%\assembly
2) Remove the system and hidden attributes for the Desktop.ini file:
attrib –s –h desktop.ini
3) Rename the file to disable the shell extension
ren Desktop.ini Desktop.ini.disabled
Delete all Devart folders.

3. Clear the directories:
* \Program Files (x86)\Microsoft Visual Studio x.x\Common7\IDE\PrivateAssemblies\Devart\
* \Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\PrivateAssemblies\Devart\
* \Program Files (x86)\Devart\
* \Program Files (x86)\Common Files\Devart\
* \Documents and Settings\All Users\Devart\EntityDeveloper\
* \Documents and Settings\All Users\Devart\dotConnect\
4. If there are no other Devart products installed, clean the registry from the rest of Devart records. Navigate to the root of the registry and search for the word Devart. Delete all the nodes found.
5. Install dotConnect for MySQL;
5.1. Run the installation 'As Administrator';
5.2. Make sure you have closed Visual Studio prior to performing the installation;
6. Delete all files from the bin and obj folders and rebuild the project.

FransBouma
Posts: 29
Joined: Fri 25 Nov 2005 10:43

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by FransBouma » Fri 18 Jan 2019 16:17

I create the connection through DbProviderFactories.GetFactory(), and that's the only way this code will work (as we don't have hard-coded references to ADO.NET providers). I assume that's working OK in your tests? If so, could you check how it's possible that when I use the factory of the latest version, it can load assemblies from an earlier version? I ask this as the amount of steps you post is pretty big, and if those steps are the same amount of steps our users have to go through, we'll reconsider using MySQL connector in the future.

For the record, the issue I posted here was brought forward by a customer of ours, we tested using the default connection settings hence it worked. So even I get it working the customer also has to do these steps and other customers who run into this as well. I'm sorry but isn't it better to have mysql connector work with the assemblies it should work with, namely the ones coming with the same version?

If the connection, created by the DbProviderFactory instance returned from DbProviderFactories.GetFactory() is from the new assembly, how can it still be the case 'old' assemblies are used? I've never had any problems like this with any ADO.NET provider where version mismatches occurred when the factory was used. Even with ODP.NET. Also, really, I have to cleanse the registry for all 'devart' data? Isn't it more appropriate for you to simply load the right assemblies belonging to the right version? If I get a reference to the DbProviderFactory of the new version, it creates instances of objects from the new version's assembly, not from the old version?

Anyway, I'll uninstall everything and re-install the latest and see what happens.

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Tue 22 Jan 2019 18:36

We still can't reproduce the issue.
FransBouma wrote:I create the connection through DbProviderFactories.GetFactory(), and that's the only way this code will work (as we don't have hard-coded references to ADO.NET providers).
1. What dotConnect for MySQL version is installed on your computer?
2. What versions of Devart.Data.MySql.dll are present in GAC?
3. The edition of dotConnect for MySQL.
4. Please create a new simple console application with the following code:

Code: Select all

DbProviderFactory fact = DbProviderFactories.GetFactory("Devart.Data.MySql");
DbConnection conn = fact.CreateConnection();

conn.ConnectionString = "Server=windows2008r2.sd.local; Port=3308;Database=mysql; User ID=xxxxxxx; Password=xxxxxxxxx; Default Auth Plugin=cached_sha2_password;";
Console.WriteLine(conn.GetType().Assembly.FullName);
conn.Open();
Console.WriteLine(conn.ServerVersion);
conn.Close();
What is the results of execution of this code?
FransBouma wrote:(Using 8.12.1307, download an hour ago. .NET Full)
Where did you get this dotConnect for MySQL? Did you download and install the dcmysql812pro.exe file or did you just get assemblies from NuGet packages?

FransBouma
Posts: 29
Joined: Fri 25 Nov 2005 10:43

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by FransBouma » Wed 23 Jan 2019 09:46

What I did in mysql workbench 8: Users and Privileges -> Add account -> Add account for '%' (as we're accessing it over the network!), give it a name and password and specify caching_sha2_password as authentication type. So e.g. add the user 'Frans', password abc, for %, choose caching_sha2_password and that's it.

Perhaps you have multiple connection specifications for a given test user and you connect over one which doesn't have that caching_sha2_password set? (e.g. ::1 instead of %)
Pinturiccio wrote:
Tue 22 Jan 2019 18:36
We still can't reproduce the issue.
FransBouma wrote:I create the connection through DbProviderFactories.GetFactory(), and that's the only way this code will work (as we don't have hard-coded references to ADO.NET providers).
1. What dotConnect for MySQL version is installed on your computer?
8.12.1307. There's no other dotConnect installed, as the installer uninstalls the old one.

The machine.config file of Framework64 for .NET 4 contains 1 line for dotconnect:

Code: Select all

      <add name="dotConnect for MySQL" invariant="Devart.Data.MySql"
        description="Devart dotConnect for MySQL" type="Devart.Data.MySql.MySqlProviderFactory, Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
2. What versions of Devart.Data.MySql.dll are present in GAC?
Just 8.12.1307

Code: Select all

C:\Windows\assembly>dir /s deva*
 Volume in drive C has no label.
 Volume Serial Number is 1C2A-8A0E

 Directory of C:\Windows\assembly\GAC_MSIL

10-01-2019  10:00    <DIR>          Devart.Data
10-01-2019  10:00    <DIR>          Devart.Data.MySql
               0 File(s)              0 bytes

 Directory of C:\Windows\assembly\GAC_MSIL\Devart.Data\5.0.2099.0__09af7300eec23701

10-01-2019  10:00           287.912 Devart.Data.dll
               1 File(s)        287.912 bytes

 Directory of C:\Windows\assembly\GAC_MSIL\Devart.Data.MySql\8.12.1307.0__09af7300eec23701

10-01-2019  10:00           602.280 Devart.Data.MySql.dll
               1 File(s)        602.280 bytes

     Total Files Listed:
               2 File(s)        890.192 bytes
               2 Dir(s)  36.012.822.528 bytes free

C:\Windows\assembly>
3. The edition of dotConnect for MySQL.
The express one
4. Please create a new simple console application with the following code:

Code: Select all

DbProviderFactory fact = DbProviderFactories.GetFactory("Devart.Data.MySql");
DbConnection conn = fact.CreateConnection();

conn.ConnectionString = "Server=windows2008r2.sd.local; Port=3308;Database=mysql; User ID=xxxxxxx; Password=xxxxxxxxx; Default Auth Plugin=cached_sha2_password;";
Console.WriteLine(conn.GetType().Assembly.FullName);
conn.Open();
Console.WriteLine(conn.ServerVersion);
conn.Close();
What is the results of execution of this code?
I moved the line to obtain the type name above the connect, as it crashes:

Code: Select all

Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

Unhandled Exception: System.NotSupportedException: Keyword not supported: 'default auth plugin'.
   at Devart.Data.MySql.MySqlConnectionStringBuilder.a(String A_0)
   at Devart.Data.MySql.MySqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Devart.Data.MySql.MySqlConnectionStringBuilder..ctor(String connectionString)
   at Devart.Data.MySql.MySqlConnection.a(String A_0)
   at Devart.Data.MySql.MySqlConnection.set_ConnectionString(String value)
   at MySQLTester.Program.Main(String[] args) in C:\Users\frans\Documents\Visual Studio 2017\Projects\MySQLTester\MySQLTester\Program.cs:line 17
Press any key to continue . . .
I've looked at the Debug -> Modules window, and it lists 1 dll from DevArt: Devart.Data.MySql.dll, from the GAC, version 8.12.1307, timestamp 9-1-2019, 22:53.

When I remove the 'default auth plugin' name-value pair from the connectionstring (as according to your docs, that shouldn't be necessary!) I get (after a delay of 10 seconds or so)

Code: Select all

Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

Unhandled Exception: Devart.Data.MySql.MySqlException: Can't connect to MySQL server on 'windows2008r2.sd.local' (10061): Authentication failed. ---> System.Inv
alidOperationException: Unknown authentication plugin 'caching_sha2_password'.
   at Devart.Data.MySql.l.a(c A_0)
   at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
   --- End of inner exception stack trace ---
   at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
   at Devart.Data.MySql.q.a(MySqlConnection A_0, String A_1, String A_2, String A_3, String A_4, Int32 A_5, Int32 A_6, MySqlProtocol A_7, Boolean A_8, Boolean A
_9)
   at Devart.Data.MySql.q..ctor(ao A_0, MySqlConnection A_1)
   at Devart.Data.MySql.am.a(DbConnectionOptions A_0, Object A_1, DbConnectionBase A_2)
   at Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, DbConnectionOptions A_1, DbConnectionBase A_2)
   at Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
   at Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
   at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
   at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
   at Devart.Common.DbConnectionBase.Open()
   at Devart.Data.MySql.MySqlConnection.j()
   at Devart.Data.MySql.MySqlConnection.Open()
   at MySQLTester.Program.Main(String[] args) in C:\Users\frans\Documents\Visual Studio 2017\Projects\MySQLTester\MySQLTester\Program.cs:line 18
Press any key to continue . . .
When I look at the Modules window when the exception above is thrown (wrapped the code with a try/catch) I see 2 DevArt dlls loaded this time, one the aforementioned Devart.Data.MySql.dll and now also Devart.Data.dll, from the GAC, v 5.00.2099.0, timestamp 9-1-2019 22:53, so looks like the correct one.
FransBouma wrote:(Using 8.12.1307, download an hour ago. .NET Full)
Where did you get this dotConnect for MySQL? Did you download and install the dcmysql812pro.exe file or did you just get assemblies from NuGet packages?
From the download page of mysql connect on the devart site, I don't use nuget for dbproviderfactory related material as it makes no sense: I need these providers be reachable from machine.config (as we load the ado.net providers on the fly using DbProviderFactories.GetFactory). I already have specified this a couple of times.

I don't know what else is going on, I can't debug it further from here as you use an obfuscated dll, so this is the info I can dig up for you. The repro is as simple as your own piece of code above, so that's not it.

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Thu 24 Jan 2019 14:35

We have reproduced the issue with the Express edition. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Wed 13 Feb 2019 13:40

We have supported connection to MySQL 8.0 in the Express edition. Please note that Default Auth Plugin connection string parameter is not supported in the Express edition. cached_sha2_password will be used as default one for MySQL 8.0. We will post here when the corresponding build of dotConnect for MySQL is available for download.

FransBouma
Posts: 29
Joined: Fri 25 Nov 2005 10:43

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by FransBouma » Wed 13 Feb 2019 13:43

Thanks :)

From the docs, as described earlier in the thread, it should be automatic tho so no connection string element is required? So if the account uses cached_sha2_password, that's chosen by the connection otherwise the default?

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Wed 13 Feb 2019 14:00

Just omit using Default Auth Plugin in your connection string. If default_authentication_plugin is mysql_native_password and the user is created with caching_sha2_password, connection will be successful.

Pinturiccio
Devart Team
Posts: 2327
Joined: Wed 02 Nov 2011 09:44

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Post by Pinturiccio » Fri 15 Feb 2019 09:58

New build of dotConnect for MySQL 8.12.1328 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=38377


Post Reply