SSL and MySQL 8 Access denied

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.

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":

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

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

(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

Any news?

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.

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")

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

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

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.

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?

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

Same here, as soon as I log in via mysql client command line, HeidiSQL connects fine until restart. Where can we get logs? I tried /var/log/syslog, running wine heidisql.exe, still there's no details.

Probably your hosting database connection from outside world is blocked by server by default,

to solve this problem you have to either allow your static ip or give wildcard ‘%’ entry.

(you will find setting in cPanel))

This issue is still not solved. Quite frustrating. Can anyone please help?

Heidi 11.3, MySQL 8.0.28

tam did you try with the latest build? It contains some promising changes for issue #1539 which potentially fixes some issue.. but I don't want to shout too loud.

Do not just update to the latest build, however, better

• take the last installer: https://www.heidisql.com/installers/HeidiSQL_11.3.0.6424_Setup.exe
• then update to the latest build through Help > Check for updates

ansgar,

thanks for the hint. I tried with 6295, 6433 seems to fix this problem. What a great news. Thanks a lot.