SSL Connection to MySQL 8.0.16 fails

mf's profile image mf posted 6 years ago in Creating a connection Permalink

We updated our MySQL-Server to 8.0.16 and I am not able to establish an SSL connection to the server. I am getting the following error message:

SSL connection error: The message received was unexpected or badly formatted

With MySQL-Server 8.0.15 I was able to connect. They made some SSL-related updates, see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html#mysqld-8-0-16-security

It is noteworthy that I had the same issue with the MySQL Workbench 6.3 CE, there was also some SSL-handshake-related error when connecting but after updating to the latest version 8.0 CE I can establish the connection again.

HeidiSQL Official release 10.1.0.5464

mf's profile image mf posted 6 years ago Permalink

I investigated this issue with our database administrator and the issue is the TLS version and has most likely nothing to do with the MySQL version.

Formerly we had no specifics about the TLS version. Now the server is configured with tls_version=TLSv1.2 and HeidiSQL is not able to build up a TSL 1.2 connection. After disabling this configuration I was able to connect again. But since our administrator does not want to disable this configuration in production (in fact he would force TLS 1.3 if MySQL would support it without additional software) I am in desparate need of HeidiSQL supporting TLS 1.2.

ansgar's profile image ansgar posted 6 years ago Permalink

Please look up in your HeidiSQL application directory, e.g. c:\Program Files\HeidiSQL\ - whether you have libmariadb.dll and libmysql.dll in there, and via rightclick on them, then click on "Details", find the version of both. Please post these version numbers here, so I can verify they were updated by your installation. libmariadb.dll should have v3.0.8, while libmysql.dll should have v5.6.6.0 .

Normally, HeidiSQL uses libmariadb.dll, even when connecting to MySQL. libmysql.dll is just left for backwards compatibility.

In the meantime there is libmariadb.dll v3.0.10, but the changelogs don't say a word about TLS 1.2 support, and I can't believe it's the library which is outdated. There must be some setting, maybe one which I can set via mysql_options()

ansgar's profile image ansgar posted 6 years ago Permalink

I found they seem to work on TLS 1.2 support in MariaDB's connector: https://github.com/MariaDB/mariadb-connector-c

So you could give it a try and delete libmariadb.dll or rename it before you start heidisql.exe. Then HeidiSQL should fall back to use libmysql.dll and you will see if there is support for TLS1.2.

But I'm unsure whether there is a todo for me in HeidiSQL. There is the MARIADB_OPT_TLS_VERSION setting, which I probably have to set when connecting.

Code modification/commit 51da7c8 from Ansgar Becker <anse@heidisql.com>, 6 years ago, revision 10.1.0.5573
Set MARIADB_OPT_TLS_VERSION to define which TLS protocol versions are allowed. See https://www.heidisql.com/forum.php?t=27158
ansgar's profile image ansgar posted 6 years ago Permalink

I just pushed 51da7c8e which probably support TLS up to v1.3. Please update to the next build and report back if that works for you.

mf's profile image mf posted 6 years ago Permalink

Thanks for your quick help.

Now I can connect with TLS 1.2 but connecting to the "old" server without the configuration is not possible anymore. It given the error message

SSL connection error: The message received was unexpected or badly formatted

Your above tip with using the fallback libmysql.dll did not work for the Official release version. I still was not able to connect with TLS 1.2.

ansgar's profile image ansgar posted 6 years ago Permalink

Ok, then what TLS version is your old server running? Heidi now allows "TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3" - while I read of another writing for "TLSv1", which is probably the cause.

mf's profile image mf posted 6 years ago Permalink

SHOW SESSION STATUS LIKE 'Ssl_version' yields TLSv1.1

[expired user #12504]'s profile image [expired user #12504] posted 6 years ago Permalink

Hi,

I have same problem, but MySQL has not updated (5.7.26), only HeidiSQL has updated about 3 days ago.

[expired user #12504]'s profile image [expired user #12504] posted 6 years ago Permalink

Test with MariaDB 10.3.15 is perfect.

SHOW SESSION STATUS LIKE 'Ssl_version' TLSv1

ansgar's profile image ansgar posted 6 years ago Permalink

Ok, so lascjr has "TLSv1" running, while HeidiSQL tries to support "TLSv1.0" - I should maybe remove that trailing ".0" or add "TLSv1" to the comma separated list.

[expired user #12504]'s profile image [expired user #12504] posted 6 years ago Permalink

Hi ansgar, problem still with revision 5579

Code modification/commit 3d76db0 from Ansgar Becker <anse@heidisql.com>, 6 years ago, revision 10.1.0.5580
Fix wrong writing of TLS protocol version. See https://www.heidisql.com/forum.php?t=27158#p27211
ansgar's profile image ansgar posted 6 years ago Permalink

I just fixed the wrong writing to "TLSv1". Please update to the next nightly build (5580) and report back if that works now.

[expired user #12504]'s profile image [expired user #12504] posted 6 years ago Permalink

In revision 5580, the error message has changed, now is: Unknown SSL error (0x80090308)

ansgar's profile image ansgar posted 6 years ago Permalink

Hm shit. So to summarize things: you formerly said

Test with MariaDB 10.3.15 is perfect

So what exactly does not work yet? Is it probably the same as mf posted:

Now I can connect with TLS 1.2 but connecting to the "old" server without the configuration is not possible anymore.

And if so, what server version is it exactly? I cannot reproduce that here, on various servers with no tls_version configuration. So we need to be most exact here, with as many details as you have.

mf's profile image mf posted 6 years ago Permalink

I tested the following HeidiSQL versions (all 64 Bit):

  • 10.1.0.5464 - latest official release
  • 10.1.0.5572 - 1 revision before MARIADB_OPT_TLS_VERSION
  • 10.1.0.5573 - MARIADB_OPT_TLS_VERSION fix
  • 10.1.0.5579 - 1 revision before TLSv1
  • 10.1.0.5580 - TLSv1 fix

MySQL server 5.7.22-log (variable tls_version: TLSv1,TLSv1.1, status Ssl_version: TLSv1.1)

  • 5464 connectable
  • 5572 connectable
  • 5573 SSL connection error: The message received was unexpected or badly formatted
  • 5579 SSL connection error: The message received was unexpected or badly formatted
  • 5580 Unkonown SSL error (0x80090308)

MySQL server 8.0.16 (variable tls_version: TLSv1.2, status Ssl_version: TLSv1.2)

  • 5464 SSL connection error: The message received was unexpected or badly formatted
  • 5572 SSL connection error: The message received was unexpected or badly formatted
  • 5573 connectable
  • 5579 connectable
  • 5580 connectable

With libmysql.dll instead of libmariadb.dll I get on all Heidi versions:

  • MySQL 5.7.22-log connectable
  • MySQL 8.0.16 SSL connection error: unknown error number

We created you a user on our test databases. I will send you the credentials via e-mail.

[expired user #12504]'s profile image [expired user #12504] posted 6 years ago Permalink

With 5580

MariaDB 10.3.15 - is perfect work MySQL 5.7.26 - Unknown SSL error (0x80090308)

ansgar's profile image ansgar posted 6 years ago Permalink

@mf - thanks a lot for consistent testing results! I'm sure this helps. Also thanks for the credentials you sent me.

Code modification/commit 3a891b8 from Ansgar Becker <anse@heidisql.com>, 6 years ago, revision 10.1.0.5581
Use full path when loading libmariadb.dll or libmysql.dll, so we don't accidentally prefer a libmariadb.dll somewhere on the users harddisk over the local libmysql.dll. Only if that also fails, fall back to load it from somewhere else. This hopefully helps for debugging SSL connection problems. See https://www.heidisql.com/forum.php?t=27158#p27219
ansgar's profile image ansgar posted 6 years ago Permalink

Ok, found out a few things:

  • removing the MARIADB_OPT_TLS_VERSION option call fixes connecting to 5.7 with SSL, but fails for 8.0 (this is what you already found out with your tests, mf)
  • it makes no difference to additionally set MYSQL_OPT_TLS_VERSION, with any library.
  • removing libmariadb.dll did not necessarily cause HeidiSQL to load libmysql.dll from its directory. Instead - if you have another libmariadb.dll on your harddisk and in your path (like me), that one was preferred. This was an issue I just fixed for the next build: the local libmysql.dll now has precedence over another libmariadb.dll or libmysql.dll on your system.
  • you did not encounter that issue, so you likely do not have a MariaDB server installed locally
  • finally my testing results are consistent with yours - and we still have a problem with a default installation connecting with SSL on 5.7
  • I had the idea to offer a new advanced setting for the session, where the user can select the library to use. But that would be awful, as most users won't know what to select here.
pantantrollo's profile image pantantrollo posted 6 years ago Permalink
  • I had the idea to offer a new advanced setting for the session, where the user can select the library to use. But that would be awful, as most users won't know what to select here.

It's not such a bad idea. In summary, there are only two libraries.

I could leave it with a checkbox automatically, and the option, distilling the checkbox in manual, to choose the library.

ansgar's profile image ansgar posted 6 years ago Permalink

I found there is a parallel discussion in issue #519, so let's please proceed there with comments. The reporter closed the issue, but that was probably by mistake.

Also, there is a ticket on https://jira.mariadb.org/browse/MDEV-13492 about that "Unknown SSL error". Probably that will also be helpful to solve this here.

[expired user #13026]'s profile image [expired user #13026] posted 5 years ago Permalink

How to resolve hp error code 0xc0000185? if you have any idea then, please share.

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