Page 1 of 1

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

Posted: Thu 10 Jan 2019 09:40
by FransBouma
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.

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

Posted: Fri 11 Jan 2019 20:23
by Shalex
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.

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

Posted: Mon 14 Jan 2019 12:31
by FransBouma
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

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

Posted: Fri 18 Jan 2019 15:45
by Pinturiccio
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.

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

Posted: Fri 18 Jan 2019 16:17
by FransBouma
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.