SSL connection to MySQL 8.0 server

I'm struggling to connect to a MySQL 8.0 server with enforced SSL connections. I have the key, client certificate and ca certificate. I have not entered any options for the SSL cipher on the "Advanced" settings tab.

When I attempt to connect, I get the error:

SSL connection error: unknown error number

Please select a different library in your session settings. (Current: "...")

It doesn't matter which library is selected (libmysql.dll or libmysql-6.1.dll). The connection won't work. One minor exception is that if libmariadb.dll is selected, I get the error:

SSL connection error: . no cipher match. Error 0x80090331(SEC_E_ALGORITHM_MISMATCH)

Please elect a different library in your session settings. (Current: "libmariadb.dll")

I've copied the odd punctuation on the first line as carefully as I can.

I have previously connected to this server before enabling SSL connections. My PHP app is able to connect to the server with the SSL enforced. I'm connecting via an SSH tunnel, but it's the same tunnel that I used previously to connect without SSL. The SSH tunnel is to allow a "local" root login.

I have tested this with a direct login to the server and I get the same error message.

Disabling SSL connections on the client (this is not an option on the server since it is being accessed by remote web apps) yields this (expected) error:

Connections using insecure transport are prohibited while --require_secure_transport=ON.

I've seen a few other threads on this theme where the suggested advice was to download a new version of the libmysql.dll, but the only one I can see on mysql.com is 6.1.11 which was last released in 2017.

I will be grateful for any suggestions to resolve this issue.

Some additional info: the server log error contains:

2020-12-22T15:21:45.254359Z 337 [Note] [MY-010914] [Server] Bad handshake

Just to be sure, you're using HeidiSQL v11.1, or even a newer build?

Related stuff, probably helpful, although some specific to MariaDB:

• https://github.com/HeidiSQL/HeidiSQL/issues/519#issuecomment-581800460
• https://jira.mariadb.org/browse/MDEV-13492
• https://www.heidisql.com/forum.php?t=27158#p27220

Just to be sure, you're using HeidiSQL v11.1, or even a newer build?

Using latest nightly.

I'm not sure how to interpret the links provided given that I'm not connecting to a MariaDB database. I'll investigate them more thoroughly tomorrow.

From your first link, I downloaded the most recent version of the MariaDB driver (3.1.9) and added that to my HeidiSQL setup. On connection with that driver, I get the following (same) error:

SSL connection error: no cipher match. Error 0x80090331(SEC_E_ALGORITHM MISMATCH)

Please select a different library in your session settings. (Current: "libmariadb-3.1.9.dll")

I've googled that particular error code and can't make head nor tail of the responses - I don't understand the encryption terminology.

The second link refers to either a bug in MariaDB connector or MariaDB itself - I don't think that there will be valid solutions here for me considering I doubt I should be using the MariaDB connector to connect to a MySQL 8 database.

The third link, to this forum, suggests checking the DLL version numbers.

• libmariadb.dll = 3.1.7
• libmariadb-3.1.9.dll = 3.1.9 (this version I downloaded, installed and copied the DLL into the HeidiSQL folder.
• libmysql.dll = 5.6.6.0
• libmysql-6.1.dll = 6.1.11

I'm not sure if its relevant, but the OpenSSL library in use (libssl-1_1-x64.dll) reports version 1.1.1d. I downloaded the most recent version (1.1.1i) and renamed the existing DLL and inserted the new versions. This has not made any difference to the error messages or my inability to connect.

I do want to restate the point that my "SSL Ciphers" option is blank. I don't know what to fill in. I did run an SQL query from an existing encrypted connections to get a list of the ciphers and the following was returned, but pasting this in has had no effect:

TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES256-SHA:CAMELLIA256-SHA:CAMELLIA128-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA

The server that I'm connecting to is an Ubuntu 20.04 LTS machine with an installation of MySQL 8.0 from the default apt repository. The version number (mysql -V) is reported as:

mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

Attempting from the command line, the following using the mysql client provided by a local install of MariaDB:

mysql -h <hostname> -u <username> -p --ssl --ssl-capath="<path>\ca.pem" --ssl-cert="<path>\client-cert.pem" --ssl-key="<path>\client-key.pem"

The error returned was: ERROR 2026 (HY000): SSL connection error: no cipher match (SEC_E_ALGORITHM_MISMATCH , 0x80090331).

(Note that this connection does not access the server via SSH - the server is currently internet facing... IP restrictions will be imposed later, once I get this working).

If I try the login from WSL (Ubuntu) I get the following error:

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

The version being reported in WSL is: mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

If I specify this command from a true Ubuntu box (i.e. the one on which my PHP app resides and which can currently connect), it is unsurprising, perhaps that it also works from there.

I really don't know where to turn next or what else to try. I'd be grateful for any suggestions.

I have tried specifying SSL ciphers. Given that there are OpenSSL DLLs in the install folder, I used the list of ciphers from the OpenSSL site.

I reformatted them and tried the TLS 1.2 ciphers: TLS_RSA_WITH_NULL_SHA256:NULL-SHA256:TLS_RSA_WITH_AES_128_CBC_SHA256:AES128-SHA256:TLS_RSA_WITH_AES_256_CBC_SHA256:AES256-SHA256:TLS_RSA_WITH_AES_128_GCM_SHA256:AES128-GCM-SHA256:TLS_RSA_WITH_AES_256_GCM_SHA384:AES256-GCM-SHA384:TLS_DH_RSA_WITH_AES_128_CBC_SHA256:DH-RSA-AES128-SHA256:TLS_DH_RSA_WITH_AES_256_CBC_SHA256:DH-RSA-AES256-SHA256:TLS_DH_RSA_WITH_AES_128_GCM_SHA256:DH-RSA-AES128-GCM-SHA256:TLS_DH_RSA_WITH_AES_256_GCM_SHA384:DH-RSA-AES256-GCM-SHA384:TLS_DH_DSS_WITH_AES_128_CBC_SHA256:DH-DSS-AES128-SHA256:TLS_DH_DSS_WITH_AES_256_CBC_SHA256:DH-DSS-AES256-SHA256:TLS_DH_DSS_WITH_AES_128_GCM_SHA256:DH-DSS-AES128-GCM-SHA256:TLS_DH_DSS_WITH_AES_256_GCM_SHA384:DH-DSS-AES256-GCM-SHA384:TLS_DHE_RSA_WITH_AES_128_CBC_SHA256:DHE-RSA-AES128-SHA256:TLS_DHE_RSA_WITH_AES_256_CBC_SHA256:DHE-RSA-AES256-SHA256:TLS_DHE_RSA_WITH_AES_128_GCM_SHA256:DHE-RSA-AES128-GCM-SHA256:TLS_DHE_RSA_WITH_AES_256_GCM_SHA384:DHE-RSA-AES256-GCM-SHA384:TLS_DHE_DSS_WITH_AES_128_CBC_SHA256:DHE-DSS-AES128-SHA256:TLS_DHE_DSS_WITH_AES_256_CBC_SHA256:DHE-DSS-AES256-SHA256:TLS_DHE_DSS_WITH_AES_128_GCM_SHA256:DHE-DSS-AES128-GCM-SHA256:TLS_DHE_DSS_WITH_AES_256_GCM_SHA384:DHE-DSS-AES256-GCM-SHA384:TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256:ECDHE-RSA-AES128-SHA256:TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384:ECDHE-RSA-AES256-SHA384:TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256:ECDHE-RSA-AES128-GCM-SHA256:TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384:ECDHE-RSA-AES256-GCM-SHA384:TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256:ECDHE-ECDSA-AES128-SHA256:TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384:ECDHE-ECDSA-AES256-SHA384:TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:TLS_DH_anon_WITH_AES_128_CBC_SHA256:ADH-AES128-SHA256:TLS_DH_anon_WITH_AES_256_CBC_SHA256:ADH-AES256-SHA256:TLS_DH_anon_WITH_AES_128_GCM_SHA256:ADH-AES128-GCM-SHA256:TLS_DH_anon_WITH_AES_256_GCM_SHA384:ADH-AES256-GCM-SHA384:RSA_WITH_AES_128_CCM:AES128-CCM:RSA_WITH_AES_256_CCM:AES256-CCM:DHE_RSA_WITH_AES_128_CCM:DHE-RSA-AES128-CCM:DHE_RSA_WITH_AES_256_CCM:DHE-RSA-AES256-CCM:RSA_WITH_AES_128_CCM_8:AES128-CCM8:RSA_WITH_AES_256_CCM_8:AES256-CCM8:DHE_RSA_WITH_AES_128_CCM_8:DHE-RSA-AES128-CCM8:DHE_RSA_WITH_AES_256_CCM_8:DHE-RSA-AES256-CCM8:ECDHE_ECDSA_WITH_AES_128_CCM:ECDHE-ECDSA-AES128-CCM:ECDHE_ECDSA_WITH_AES_256_CCM:ECDHE-ECDSA-AES256-CCM:ECDHE_ECDSA_WITH_AES_128_CCM_8:ECDHE-ECDSA-AES128-CCM8:ECDHE_ECDSA_WITH_AES_256_CCM_8:ECDHE-ECDSA-AES256-CCM8

This changed the errors that I got from both the libmysql-6.1.dll driver:

SSL connection error: Failed to set ciphers to use

Please select a different library in your session settings. (Current: "libmysql-6.1.dll")

... and also from the libmysql.dll driver:

SSL connection error: SSL_CTX_new failed

Please select a different library in your session settings. (Current: "libmysql.dll")

The libmariadb*.dll libraries give errors consistent with those above.

I can connect to the server from this Windows machine via PHP (7.4) running on Windows Apache using the SSL connection options with the same certificate files.

Given that I can't connect from the command line using MySQL, I'm guessing that this is likely to be a broader issue with my setup. But then I am confused about why the PHP connection works... Any suggestions on where to look next?

I can connect to the server from my windows machine via PHPStorm. This would imply, I guess, that there is some environmental impact from the machine setup, but one which some software can work around?

This changed the errors that I got from both the libmysql-6.1.dll driver: ... and also from the libmysql.dll driver:

I realise that this is because my list of ciphers included the left-hand column from the OpenSSL page which are not the actual cipher names and, as such, are not valid. Removing the left-hand column results in the same previous non-specific errors again.

My current workaround is to disable "required" SSL connections to the server. My other clients create SSL connections, so they work, and my desktop setup will use SSH to avoid unencrypted data over the internet.