Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Failure connecting Heidi 9.5 to MySQL 8.0 due to missing plugin: caching_sha2_password #163

Closed
ebyrob opened this issue Feb 26, 2018 · 81 comments

Comments

@ebyrob
Copy link

@ebyrob ebyrob commented Feb 26, 2018

Steps to reproduce this issue

  1. Download and install MySQL 8.0 RC
  2. Verify MySQL up and running on default port.
  3. Download and install HeidiSQL 9.5
  4. Attempt to connect unencrypted to "localhost" / 127.0.0.1 (if IPv4)

Current behavior

---------------------
     Error
---------------------
Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found.
          [ OK ]
---------------------

Expected behavior

Successful connection.

Possible solution

Find or build missing plugin file: caching_sha2_password.dll
or perhaps plugin dll has slightly different name, I can't find it.

Environment

  • HeidiSQL version:
    9.5.0.5196
  • Database system and version:
    mysql Ver 8.0.4-rc for Win64 on x86_64 (MySQL Community Server (GPL))
  • Operating system:
    Windows 7 64-bit.

Thanks for a great program!!! I love HeidiSQL, but realized I can't build it without a Borland compiler I never bought, but I won't panic.

Thank you in advance for your help,
Robert Eby

@rentalhost
Copy link
Collaborator

@rentalhost rentalhost commented Feb 26, 2018

Could you try it again by using the last nightly version?

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 26, 2018

There is just no caching_sha2_password.dll in HeidiSQL's plugins folder. You need to copy it from the server distribution.

Not sure if I should add some more plugins to the installer, along with the both dialog.dll and auth_gssapi_client.dll

@ebyrob
Copy link
Author

@ebyrob ebyrob commented Feb 26, 2018

I have no idea what should be in HeidiSQL, I'm just glad it works so well.

I wanted to copy a file called caching_sha2_password.dll when I saw the error message but I couldn't find it anywhere in the pre-built binaries for MySQL. I also tried copying all pre-built plugins from MySQL 8.0 RC main release but that did not work either.

A trusted colleague thought perhaps it could have been repackaged under another name, but I thought it was likely that mysql.exe was using it from static include and didn't need the DLL.

@G3ph4z
Copy link
Contributor

@G3ph4z G3ph4z commented Feb 26, 2018

I think you should upgrade the libmysql.dll (MySQL Connector lib). I will check that.

@rentalhost
Copy link
Collaborator

@rentalhost rentalhost commented Feb 26, 2018

It was reported on forum too: https://www.heidisql.com/forum.php?t=24939

Seems related to MySQL 8.x, but I can't found where we can download it. Maybe just update libmysql.dll.

@G3ph4z
Copy link
Contributor

@G3ph4z G3ph4z commented Feb 26, 2018

@rentalhost Doesnt help. I tried with 8.0.3 version libmysql.
-> http://prtsc.denes.cloud/vmware_2018-02-26_22-42-08.png

If a client or connector is available that has been updated to know about caching_sha2_password, using it is the best way to ensure compatibility when connecting to a MySQL 8.0 server configured with caching_sha2_password as the default authentication plugin.

These clients and connectors have been upgraded to support caching_sha2_password:

The libmysqlclient client library in MySQL 8.0.4 or higher. Standard MySQL clients such as mysql and mysqladmin are libmysqlclient-based, so they are compatible as well.

MySQL Connector/J 8.0.9 or higher.

MySQL Connector/Net 8.0.10 or higher (through the classic MySQL protocol).

MySQL Connector/Node.js 8.0.9 or higher. 

But If I try with 8.0.4 libmysqlclient, I get this:
http://prtsc.denes.cloud/vmware_2018-02-26_22-48-13.png

DLL:
http://prtsc.denes.cloud/vmware_2018-02-26_22-51-25.png

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 27, 2018

@ebyrob you should install the latest built installer once, then you will get an additional libmariadb.dll in your HeidiSQL folder, which Heidi then uses. I added that lib after the last 9.5 release, so most likely you don't have it yet. It's from the MariaDB 10.2.12 GA release.
The remaining old libmysql.dll is still there and used as a fallback.

@ebyrob
Copy link
Author

@ebyrob ebyrob commented Feb 28, 2018

Could you try it again by using the last nightly version?

I did try this, and I'm amazed how easy grabbing nightly is, but no luck.

I think you should upgrade the libmysql.dll (MySQL Connector lib). I will check that.

I couldn't find RC for MySQL Connect/C, but I also didn't see a readily available "RC" version on the website. I may need to see if I can build it myself. And indeed, I was told on MySQL forums that the sever's copy of mysql.exe uses a static include so it isn't there either.

The libmysqlclient client library in MySQL 8.0.4 or higher.

I did find libmysqlclient.lib in the server binaries, it seems likely I could static link to that but I wasn't sure how that would plug in to HeidiSQL.

you should install the latest built installer once, then you will get an additional libmariadb.dll...

I'm not 100% but libmariadb.dll seems to get exactly the same error. This is very interesting. Notes: I renamed libmysql.dll to avoid loading it. My other upgrade test machine is happily running on latest MariaDB though comparing MariaDB release to MySQL RC probably isn't fair.

@ebyrob
Copy link
Author

@ebyrob ebyrob commented Mar 14, 2018

Sorry for the long delay, I finally had time to look at this again and this time I'm getting the following:

Error

SSL connection error: error:00000001:lib(0):func(0):reason(1)

[OK]

When trying to connect. This only happens after I copy the 3 necessary dll's from MySQL 8.0 RC build to replace libmysql.dll. I'm suspicious the problem is I need to re-build Heidi, but I'm not sure where to get the right Pascal compiler. (I do have a test application connecting just fine but I can rebuild that all I need to)

@G3ph4z
Copy link
Contributor

@G3ph4z G3ph4z commented Mar 14, 2018

@ebyrob You only can compile with RAD Studio XE5 or newer versions of RAD Studio.

Delphi XE5 is required for building HeidiSQL. Older Delphi versions will most likely fail; newer Delphi versions may work or fail. Unfortunately, Larazus or one of the other free compilers cannot currently compile HeidiSQL.

@ebyrob
Copy link
Author

@ebyrob ebyrob commented Mar 14, 2018

So Embarcadero RAD Studio for $2,200 would work? It seems XE5 isn't available at any price, but I didn't exactly go digging for it.

https://www.lazarus-ide.org/ won't work? Ok got it. Ty. Just want to be clear here.

@G3ph4z
Copy link
Contributor

@G3ph4z G3ph4z commented Mar 14, 2018

You have right. But yes, its so sad, the Embarcadero dont want to release free version (like Visual Studio) for OpenSource Projects, so.... I think you can use trial version for compile, the 10.x.x working fine too.

Btw, the Lazarus is good enough if you want to create some applications in Pascal.

@SpyroTEQ
Copy link

@SpyroTEQ SpyroTEQ commented Mar 20, 2018

It won't fix the HeidiSQL issue, but until then and for development environment, one can still set their MySQL 8.0 to use the previous SQL password plugin.

Either by altering/creating a user with elder credentials and then use it in Heidi (ALTER USER ... IDENTIFIED WITH mysql_native_password BY password that can be done command-line using bin/mysql --user=...) or by setting up the MySQL 8.0 instance to use the old hashing plugin ([mysqld]default_authentication_plugin=mysql_native_password + bin/mysqld --initialize-insecure so you can then connect to your freshly setup MySQL 8.0 instance using Heidi)

Still, that's just a workaround for those (like me!) who would like to try MySQL8.0 new features in their development environment (using old hashing system in production is not what I would recommend :/ )

@ebyrob
Copy link
Author

@ebyrob ebyrob commented Mar 21, 2018

@littleearth
Copy link

@littleearth littleearth commented Apr 27, 2018

From some testing I have done, there does not seem to be a version of libmysql.dll for 32bit for Version 8 of Mysql

@binarymaster
Copy link

@binarymaster binarymaster commented Apr 28, 2018

https://stackoverflow.com/a/49935803/4381493

This answer helped me to overcome the issue.

@littleearth
Copy link

@littleearth littleearth commented Apr 29, 2018

Thanks, yes I have also worked around but I was trying to implement the new security in my own application and it so far does not seem possible for 32bit and the libmysql.dll as there is no version 8 of it. 64bit works fine so in theory should for heidisql but I have not tested.

@mlocati
Copy link

@mlocati mlocati commented May 3, 2018

I've been able to configure a development machine so that we can connect to in with HeidiSQL:

  1. I added this line to the the MySQL Server configuration file (and restarted it)
[mysqld]
default_authentication_plugin=mysql_native_password
  1. With the MySQL command line client (bin/mysql) I updated the password of the user I use with HeidiSQL:
ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
@natachabox
Copy link

@natachabox natachabox commented Jun 25, 2018

I was able to connect by using the command line :
Change the auth method for your passbolt user on your mysql 8 installation:
ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jul 22, 2018

I had just luck with libmysql.dll from the MySQL 8.0.11 server release, after also synchronizing some structures from mysql.h into HeidiSQL. With that dll both above errors are fixed (caching_sha2_password missing + ssl connection error).
But I have problems in finding a 32bit version of that libmysql.dll. HeidiSQL normally supports both 64 + 32 bit Windows versions. On the download page I can just find a 64bit version. Can someone point me to the right place please? Or has MySQL stopped support for 32bit systems in the Community Edition?

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jul 22, 2018

Btw, MySQL Workbench had the same problem some months ago, and Mike Lischke posted a relevant comment on StackOverflow.

@sjd-lab
Copy link

@sjd-lab sjd-lab commented Jul 22, 2018

I installed the latest version of Mysql 8.1 community edition just for sha2 encryption. But most answers suggests using the old encryption. Is there a solution yet? I'm also trying to connect using Heidisql and getting the error "caching_sha2_password cannot be loaded on connect screen itself. Heidisql 9.5.0.5280 . updated today.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jul 22, 2018

I'm just a tick away from a solution here, see my previous comments. I just need the 32bit version of the libmysql.dll v8.0.11.

@danitxo
Copy link

@danitxo danitxo commented Jul 22, 2018

It seems that the ZIP package is x64 only ("x86, 64-bit", that text is a little confusing). The MSI package, however, is 32 and 64 bit (it puts "x86, 32 & 64 bit").

Here is the link for the MSI package, and here is the direct link for the 8.0.11 release. Microsoft Visual C++ 2015 Redist are needed.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jul 22, 2018

I already tried that 32bit .msi installer, but ended up with a 64bit server version afterwards. Also visible during the install - there is just a 64bit server to install, no 32bit available.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jul 23, 2018

Too bad. Probably MariaDB also has support for the same stuff. I'll check that.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 6, 2018

Ok, that was too quick :) I get

Plugin caching_sha2_password could not be loaded: The module was not found. Library path is 'lib/mariadb/plugin/caching_sha2_password.dll'

For MySQL plugins, HeidiSQL already sets its own plugin path:

mysql_options(FHandle, MYSQL_PLUGIN_DIR, 'c:\path\to\heidisql\plugins\');

But that seems to be ignored for MariaDB plugins. Instead, MARIADB_PLUGINDIR is a non-overridable constant in mariadb_version.h. Is there some way to change that?

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 6, 2018

@mlocati : I never compiled the dlls by mysqlf, as HeidiSQL is written in Delphi, not VC. Perhaps the server distribution has these dlls without debug information?

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 9, 2018

Now if only I can figure out how to change the lib/mariadb/plugin/ sub-folder under running executable where it is looking for caching_sha2_password.dll

@ebyrob It should be as easy as setting the MARIADB_PLUGIN_DIR environment variable to C:\Path\To\HeidiSQL\plugins (see https://github.com/MariaDB/mariadb-connector-c/blob/9e1fef0bf2c47b6f75b68fc6ce3ed15495222da9/client/ma_plugin_info.c#L114)

@mlocati Does that mean I have to set a system environment variable to tell libmariadb.dll where it has to look for plugins? Sounds strange. Or does getenv look elsewhere as well?

@mlocati
Copy link

@mlocati mlocati commented Dec 10, 2018

@ansgarbecker It should be enough to set the environment variabile for the process that's loading the DLLs.
I'm sorry but I don't know Pascal (last time I wrote something in it was about 20 years ago), so I can't give you a function name.

BTW you can check where the app is looking for the DLLs by using Process Monitor.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 10, 2018

I only wonder why it has to be an environment variable, and why it cannot be set via mysql_options, like I do that for other things:

mysql_options(FHandle, MYSQL_PLUGIN_DIR, PAnsiChar(PluginDir));
@mlocati
Copy link

@mlocati mlocati commented Dec 10, 2018

@ansgarbecker yes, also setting the MYSQL_PLUGIN_DIR option should work

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 10, 2018

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 10, 2018

Setting MYSQL_PLUGIN_DIR via mysql_options() only works with the 32bit version of libmariadb.dll, while in 64 bit mode I always get:

Plugin caching_sha2_password could not be loaded: Das angegebene Modul wurde nicht gefunden. Library path is 'lib/mariadb/plugin/caching_sha2_password.dll'

I'll go and try with mysql_optionsv instead, but I suspect it does nothing else than mysql_options.

Also, I just see they removed the v3.0.8 connector from the download page: https://downloads.mariadb.com/Connectors/c/

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 10, 2018

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 10, 2018

@mlocati
Copy link

@mlocati mlocati commented Dec 11, 2018

In the meanwhile I recompiled the auth_gssapi_client.dll and caching_sha2_password.dll plugins against the 3.1 branch of https://github.com/MariaDB/mariadb-connector-c

It required these patches:

You can build these 2 DLLs by using https://github.com/mlocati/auth_gssapi_client but for your convenience I published the compiled binaries at https://github.com/mlocati/auth_gssapi_client/releases/download/1.0.2/binaries.zip

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 11, 2018

Thanks, @mlocati !
I'm afraid your 64bit binary of caching_sha2_password.dll still has that hardcoded lib/mariadb/plugin directory. At least it does not care if I set it via mysql_options.

@mlocati
Copy link

@mlocati mlocati commented Dec 12, 2018

@ansgarbecker I'm not an expert of the MariaDB source code, I can only compile it.

BTW I tried to inspect what's happening with Process Monitor.
Open Process Monitor and set these two filters:

  1. Process Name -> contains -> heidisql then include
  2. Path -> contains -> plug then include

That way you'll only see the operations of heidisql that involve paths containing plug.

Enable the Process Monitor capture, open latest HeidiSQL (heidisql64.r5391.exe) and try to connect to a MySQL 8 server.

Here's the only row I can see in Process Monitor:
immagine

It means that the application is trying to load caching_sha2_password.dll from C:\Program Files\MariaDB 10.2\lib\plugin, and it fails because the DLL is not there.
So it seems that caching_sha2_password.dll is not even loaded.

But please don't ask me more details, I really don't know the source code of MariaDB...

@ansgarbecker ansgarbecker added this to the v10 milestone Dec 17, 2018
@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 27, 2018

@9EOR9 happy Christmas! :)

While the Connector v3.0.8 is available again now, it still seems to ignore any value for MYSQL_PLUGIN_DIR set via mysql_options() in the 64bit version. The 32bit libmariadb.dll works. Or is the 64bit version of the plugin somehow incompatible? Is this addressed in some bug report, as I can't find one on jira.mariadb.org, except for the initial one ?

It should be sufficient to set MYSQL_PLUGIN_DIR or?

mysql_options(FHandle, MYSQL_PLUGIN_DIR, 'c:\path\to\heidisql\plugins');

Would be nice to shed some light on this quite "popular" issue. Thanks for any help!

Here's a diff of the both 32/64bit versions of the plugin, which looks ok to me:

grafik

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 27, 2018

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 28, 2018

Hi Ansgar,

I just downloaded the 64-bit msi and tested it, it works as expected with
a) mysql_options(mysql, MYSQL_PLUGIN_DIR, "....")
b) via environment variable MARIADB_PLUGIN_DIR

Do you get any error message?

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 28, 2018

And of course you need to escape backslashes in mysql_options call.

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 28, 2018

Hey Georg,

thanks for your quick input.

Yes, I am still getting this error in the 64bit version:

Plugin caching_sha2_password could not be loaded: Das angegebene Modul wurde nicht gefunden. Library path is 'lib/mariadb/plugin/caching_sha2_password.dll'

Did you also try a connection to MySQL 8 via that caching_sha2_password plugin? I guess you did, but just in case.

I ran a test by copying the 64bit caching_sha2_password.dll to "C:\path\to\heidisql\lib\mariadb\plugin\" the relative path mentioned in the above error message. This works! But I'd like to keep the plugins where they belong.

I did not escape the backslashes yet, as the 32bit version works without, and also I was thinking that escaping is just required in C applications due to the special meaning of a backslash. In Delphi applications, a backslash does not have a special meaning, so you normally don't need to double it for escaping. But probably you mean that libmariadb also wants an escaped string. I will give that another test round.

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 28, 2018

@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 28, 2018

Indeed, my call to mysql_options returns -1 on 64bit and 0 on 32bit (zero = success, non-zero = failed). I have no clue why.

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 28, 2018

ansgarbecker added a commit that referenced this issue Dec 28, 2018
@ansgarbecker
Copy link
Collaborator

@ansgarbecker ansgarbecker commented Dec 28, 2018

Solved it! I just had to ensure that the second parameter to mysql_options is a 32bit integer, not some Delphi enumeration which is internally handled as a lower numeric type like Byte or Smallint:

TMySQLOption = (
  MYSQL_OPT_CONNECT_TIMEOUT,
  ...,
  MYSQL_PLUGIN_DIR,
  );
mysql_options(FHandle, Integer(MYSQL_PLUGIN_DIR), 'c:\path\to\heidisql\plugins\');

That casting to Integer did the trick.
Delphi enumerations seem to be Byte or Smallint, while C enum's seem to be Int32 or UInt32.

@9EOR9
Copy link

@9EOR9 9EOR9 commented Dec 28, 2018

@AngelusGi
Copy link

@AngelusGi AngelusGi commented Jan 29, 2019

Hi guys, is there any way to fix this error about caching_sha2_password running HeidiSQL from Microsoft Store on Windows? I have any permission to write in the windows store path where heidiSQL is installed.

@trgfree
Copy link

@trgfree trgfree commented Jan 6, 2020

@ansgarbecker
there is a bug. in 10.3.0.5771.
1、when i the first time connect to a mysql8 server use libmysql.dll, it report an error,access denied for user。
2、then i try that use libmariadb.dll,it success
3、then i switch back to libmysql.dll,it success

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet