Mariadb 10.6 - unable to connect via SSL

[mSys-mislav]

Preconditions

• HeidiSQL version: 11.3.0.6337
• Database type and version: Mariadb 10.6
• OS: Windows 10

Describe the bug

We're unable to connect via Use SSL option under windows 10 to mariadb 10.6 server. It's complaining about cipher. We've tried using different libraries, however no luck.
Under linux, ubuntu 20.04 LTS, snapd installation of heidisql 11.2 this is working. We've tried to downgrade to heidisql on windows to 11.2.0, however without luck.

To Reproduce

1. Go to connection SSL tab, browse correct certificates
2. Try to connect to mariadb 10.6 server

Screenshots or Crash reports

I've noticed in the server logs the following entries:
2021-08-11 21:27:54 175 [Warning] Aborted connection 175 to db: 'unconnected' user: 'unauthenticated' host: 'xx' (This connection closed normally without authentication)

MariaDB [(none)]> show variables like '%tls%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tls_version   | TLSv1.2,TLSv1.3 |
+---------------+-----------------+
1 row in set (0.002 sec)

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| have_openssl        | YES                                           |
| have_ssl            | YES                                           |
| ssl_ca              | /etc/mysql/encryption_ssl_tls/ca-cert.pem     |
| ssl_capath          |                                               |
| ssl_cert            | /etc/mysql/encryption_ssl_tls/server-cert.pem |
| ssl_cipher          |                                               |
| ssl_crl             |                                               |
| ssl_crlpath         |                                               |
| ssl_key             | /etc/mysql/encryption_ssl_tls/server-key.pem  |
| version_ssl_library | OpenSSL 1.1.1d  10 Sep 2019                   |
+---------------------+-----------------------------------------------+
10 rows in set (0.002 sec)

MariaDB [(none)] STATUS;
--------------
mysql  Ver 15.1 Distrib 10.6.4-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          7
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.4-MariaDB-1:10.6.4+maria~buster mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 19 sec

Threads: 2  Questions: 78  Slow queries: 0  Opens: 71  Open tables: 64  Queries per second avg: 4.105

---

[ansgarbecker]

Could you please

1. install the latest built installer: https://www.heidisql.com/installers/HeidiSQL_11.3.0.6414_Setup.exe
2. then update to the even newer nightly build through help > check for updates

There's a good chance this is working now. The installer will update libmariadb.dll and plugins. The new build activates a client option required by some SSL setups. See #1539 for details.

[Lotendan]

Hello,

Bump.
I'm having the exact same problem after I have upgraded from Mariadb 10.9.3 to Mariadb 10.9.4.
With the latest version of Heidi (12.3.64) the UI sends the exact same error with no cipher match.
A Wireshark capture shows that this is the client that is interrupting the connection abruptly, hence the Aborted connection message in the server logs. For no apparent reason. This clearly points to a problem on the client.

I have tried to replace the openssl libraries in the application folder to the latest 1.1.1s with no success.
Also tried to run openssl s_client, which does not find any error in the server's SSL and terminates successfully.
I have tried to input a different TLS cipher in the "List of permissible ciphers to use for SSL encryption" but this does not change anything either.

@mSys-mislav it would be interesting to know whether you solved this issue and if yes how you did it.

@ansgarbecker Maybe can you please try to bump the libmariadb and openssl versions?

Thanks a lot for the help.

[ansgarbecker]

HeidiSQL's libmariadb.dll is v3.2.5 which is roughly one year old.
@Lotendan can you please test whether a newer one from MariaDB 10.10 works for you?

[Lotendan]

Actually I realized that the libmariadb that is shipped with HeidiSQL is linked with Windows-SChannel instead of OpenSSL (verified with ProcessExplorer). There are probably some incompatibilities between the two.
Turning on SChannel event logging in the event viewer is not helpful. The log only says that there is an unrecoverable exception and doesn't say why...
The Wireshark capture shows that the client and server are able to find a common encryption algorithm.
However the client does not advertise the hash algorithm SHA256WithRSAEncryption that is used to sign the CA certificate, the server certificate and the client certificate. Maybe the client is unable to understand the server certificate and therefore aborts the connection.
I am trying to re-issue CA/server/client certificates that are signed with a hashing algorithm that the client supports.

@ansgarbecker maybe it would make sense to use in Heidi a version of MariaDB library linked with OpenSSL (since MariaDB uses that) instead of SChannel?

[ansgarbecker]

@Lotendan yes I can exchange libmariadb.dll in the installer, I'm only not having such a server for testing if that would work. Would be nice if you download the MariaDB server-zip for Windows from https://dlm.mariadb.com/browse/mariadb_server/214/1565/winx64-packages/ , and copy the contained libmariadb.dll into your HeidiSQL folder to check that. Note that HeidiSQL expects the regex ^lib(mysql|mariadb).*\.dll$ for the filename, so feel free to name it "libmariadb10.10.dll" or whatever.

[Lotendan]

Nope, still does not work :(
The library is still linked with SChannel, which seems incompatible with OpenSSL on those aspects.

Even the mysql.exe client seems to fail connecting anyway:

C:\Users\xxx\Desktop> .\mysql.exe -u xxx --ssl-key .\client.key --ssl-cert .\client.crt
ERROR 2026 (HY000): TLS/SSL error: no cipher match. Error 0x80090331(SEC_E_ALGORITHM_MISMATCH)

[ansgarbecker]

@Lotendan I just see that the upstream issue is closed (fixed?): openssl/openssl#20138
Does that mean libmariadb.dll now works for your SSL setup?
Do you still recommend using a different libmariadb.dll?

[Lotendan]

Hello, the issue helped to clarify a few things; the problem comes from the upgrade to OpenSSL v3.0 which is more restrictive on the algorithms used. This is expected and is working as designed and there is nothing wrong about it, hence we closed the issue. Instead we think SChannel is not picking the proper algorithms. This is either a bug in SChannel or a misusage of it by the connector.
I need to take the time to sit down and write a bug report on MariaDB Connector now.
If the investigation leads to a new version of the connector delivered, I will ping it here as soon as I get the info.
Until then there is nothing we can do, except to apply the workaround which is about lowering the security level of the server temporarily and as described in the OpenSSL issue.

[Lotendan]

Note: Jira issue created here: https://jira.mariadb.org/browse/CONC-639