Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SSL and MySQL 8 Access denied

tam's profile image tam posted 5 months ago in Creating a connection Permalink

Hello Ansgar,

depending on the password plugin HeidiSQL doesn't connect to the MySQL 8 server when using a SSL connection. I think this is a bug.

To reproduce create 2 users:

DROP USER IF EXISTS 'test1'@'192.168.%';
CREATE USER 'test1'@'192.168.%' IDENTIFIED WITH mysql_native_password BY 'test';
GRANT ALL PRIVILEGES ON *.* TO 'test1'@'192.168.%';

DROP USER IF EXISTS 'test2'@'192.168.%';
CREATE USER 'test2'@'192.168.%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* TO 'test2'@'192.168.%';

This is how it looks in the user table:

select user, host, plugin from mysql.user where user like "test%";
+-------+-----------+-----------------------+
| user  | host      | plugin                |
+-------+-----------+-----------------------+
| test1 | 192.168.% | mysql_native_password |
| test2 | 192.168.% | caching_sha2_password |
+-------+-----------+-----------------------+

In HeidiSQL I connet to the server with user test1 using SSL or not, but with user2 I can only connect without SSL. With SSL I get ## Access denied for user 'test2'@'192.168.14.2' (using password: YES)

With the MySQL 8 client I can connect with both users with SSL:

mysql.exe --host=serv8 -u test1 -ptest --ssl-mode=r -e "show status like 'ssl_cipher'" 
Variable_name   Value
Ssl_cipher  TLS_AES_256_GCM_SHA384

mysql.exe --host=serv8 -u test2 -ptest --ssl-mode=r -e "show status like 'ssl_cipher'" 
Variable_name   Value
Ssl_cipher  TLS_AES_256_GCM_SHA384
  • Heidi 11.0.0.5972 (64 Bit)
  • Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Can you please check? Thanks in advance.

ansgar's profile image ansgar posted 5 months ago Permalink

Which libarary did you use?

Many of these SSL issues can be solved by using the "right" library. Which one it is, depends on the server. I would guess you should use libmysql-6.1, but please try yourself. See also issue #722 .

If none of the libraries work, you can even put your own libmysql into C:\Program Files\HeidiSQL\ - the dropdown lists them as long as they start with "libmysql" or "libmariadb":

Description

tam's profile image tam posted 5 months ago Permalink

No matter which dll I use. Even if I use the libmysql.dll shiped with the Windows Version auf MySQL 8.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
tam's profile image tam posted 5 months ago Permalink

libmaria: Access denied for user 'test2'@'192.168.14.2' (using password: YES) libmysql-6.1: SSL connection error: unknown error number libmysql: SSL connection error: unknown error number libmysql8: Bibliothek libmysql8.dll konnte nicht geladen werden. Bitte eine andere auswählen. Interner Fehler 126: Das angegebene Modul wurde nicht gefunden

tam's profile image tam posted 5 months ago Permalink

(better readability)

  • libmaria: Access denied for user 'test2'@'192.168.14.2' (using password: YES)
  • libmysql-6.1: SSL connection error: unknown error number
  • libmysql: SSL connection error: unknown error number
  • libmysql8: Bibliothek libmysql8.dll konnte nicht geladen werden. Bitte eine andere auswählen. Interner Fehler 126: Das angegebene Modul wurde nicht gefunden
tam's profile image tam posted 4 months ago Permalink

Any news?

ansgar's profile image ansgar posted 4 months ago Permalink

I suspect that your libmysql8 depends on one or more further libraries, which you need to find and copy as well to the HeidiSQL directory. Probably Dependencies is able to tell you which these are.

tam's profile image tam posted 4 months ago Permalink

Hello Ansgar.

I also copied vcruntime140_1.dll, now I get the following error, even if I try to connect without SSL.

SSL connection error: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed
Bitte wähle eine andere Treiberbibliothek für diese Sitzung aus. (Aktuell: "libmysql8.dll")
ansgar's profile image ansgar posted 4 months ago Permalink

But doesn't that say the server has an issue? I have no clue.

tam's profile image tam posted 4 months ago Permalink

See my first post, with the mysql client it works.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 4 months ago Permalink

Perhaps there is a plugin missing? There is the plugins folder in HeidiSQL, containing some popular ones. You could copy files from the servers lib/plugin folder there. Just take the right 32/64 bit version.

tam's profile image tam posted 4 months ago Permalink

Doesn't help. I even tried with a fresh MySQL 8 server on Windows. I give up.

I just wonder if I'm the only HeidiSQL user with a MySQL 8 server?

khessels's profile image khessels posted 3 months ago Permalink

Just to throgh my 5 cents in... I downloaded navicat trail for linux, ran it, connected to my db and then exited, After this all of a sudden heidi worked as normal until i restarted my system...

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.