MySQL Connection over SSL

[expired user #8565]'s profile image [expired user #8565] posted 8 years ago in General Permalink
I am getting the following error when trying to connect over SSL to a MySQL server: SSL connection error: ASN: bad other signature confirmation

I can connect via the command line in Linux from the server and also from a remote machine.
I can connect in Windows (from the same machine where Heidi fails) via MySQL Workbench 6.3.3.0 (the latest) using the exact same 3 PEM files.

This all leads me to believe that Heidi is doing something odd when trying to establish the connection.

Any thoughts?
ansgar's profile image ansgar posted 8 years ago Permalink
No clue. One poster in this thread left the CA certificate away to solve the error. You could try that.

Probably this thread is helpful for you.
[expired user #9315]'s profile image [expired user #9315] posted 8 years ago Permalink
Those links are kinda helpful, however considering that literally anything except Heidi works points me in the direction that something in the SSL implementation in HeidiSQL doesn't play nice.

I've tried connecting using mysql command line on the machine and on a remote machine both work.

I've tried HeidiSQL inside the firewall and from outside the firewall it throws the same error. I've re-created the certs several times, no dice.

If it helps at all I'm running the latest version of CentOS, OpenSSL, and Percona Server though the 'standard' command line and workbench connect just fine.

Thanks.
[expired user #9315]'s profile image [expired user #9315] posted 8 years ago Permalink
Yep, this is still an issue. Messing with the CA doesn't seem to do anything. I can only think it's a mismatch or mess up with the latest versions of OpenSSL and whatever implementation HeidiSQL uses. The error is still SSL connection error: ASN: bad other signature confirmation
it works perfectly fine using the built in mySQL Workbench (which is pretty crappy) or using the command line. Clearly a Heidi issue...shame since we had a green light to standardize to this from Navicat :/

ansgar's profile image ansgar posted 8 years ago Permalink
Related: Issue #2336.

Just diving into the code and former issues I had with Heidi/SSL.
[expired user #9315]'s profile image [expired user #9315] posted 8 years ago Permalink
Alright I'm not sure this helps or not, but I went to one of our older servers and was able to pull the certificates (CA-Cert/CA-Key/Server-Cert/Server-Key/Client-Cert/Client-Key) and was able to put them on the new machine and Heidi connected. So I'm guessing it's the certs being generated by newer versions of OpenSSL. I haven't examined the differences between the two, but I was able to get it to work with those 'old' files.

Thanks
-Eric
ansgar's profile image ansgar posted 8 years ago Permalink
In that case it's most probably an issue with the version of libmysql.dll, which is located in the HeidiSQL folder. You can play with it, using a newer one than the one shipped with the installer.
[expired user #9228]'s profile image [expired user #9228] posted 8 years ago Permalink
I can verify that a similar issue was resolved by swapping out the libmysql.dll provided by the installer. I did not verify whether there was an upstream issue against the version that ships with Heidi.
[expired user #9695]'s profile image [expired user #9695] posted 8 years ago Permalink

I fixed this same problem also, by swapping libmysql.dll from MySQL Workbench 6.3, C:\Program Files\MySQL\MySQL Workbench 6.3 CE to the HeidiSQL install folder

rasmush's profile image rasmush posted 7 years ago Permalink

I can confirm that this problem still exists.

I "fixed" it, as mentioned above, by not providing the CA-cert, but only the two client-certs.

[expired user #10766]'s profile image [expired user #10766] posted 7 years ago Permalink

I think this problem lies with libmysql.dll cause I have replaced that file from the MariaDB installation and MySQL Workbench and HeidiSQL connects to server with CA, Cert and Key files specified.

libmysql.dll from HeidiSQL 9.4.0.5173 (Compiled on: 2017-05-18 13:19:47)
Version: 5.6.6.0
MD5: 8ad4da6dd06f1dea86eff2129b2a2b38
SHA256: afdf4976351dd147419dbd3e00206b17a776714261f025d7542f4ae3c4497901
Result: Fails to connect with CA file specified
libmysql.dll from MariaDB 10.1
Version: 10.1.17.0
MD5: 227245c1d738984a543fe148de4b39b1
SHA256: c6c9728a8c43a2cd5a90444a13e0a548c28dc1f810ef76fb34a7cf13d0f2eb1a
Result: Connects without any errors
libmysql.dll from MySQL Workbench 6.3.8 build 1228 CE
Version: 5.7.12.0
MD5: 1777dd0ab994e771ffda0b96f747c84e
SHA256: 50bbf342e2c2a532069dad96589c2e3937ad9a56680f9e4a12d8b79ad843b08a
Result: Connects without any errors

IMHO best solution is to use more recent libmysql.dll file in HeidiSQL.

All hashes computed using Windows certutil.

[expired user #10783]'s profile image [expired user #10783] posted 7 years ago Permalink

Having the same problem.

[expired user #10801]'s profile image [expired user #10801] posted 7 years ago Permalink

I have a new maria database. It is set to use TLSv1.2 (openSSL). Normally I use workbench, but it does not support TLSv1.2 - only TLSv1.1. Same for Navicat - but comes in the next version. I am a new user of HeidiSQL. It's super good, brilliant to move data between databases. But it also does not support TLSv1.2. Any plan for support TLSv1.2 - are there any schedules? Right now I'm back on TLSv1 - where everything works.

[expired user #10810]'s profile image [expired user #10810] posted 7 years ago Permalink

rasmush was right

solved my problem

I "fixed" it, as mentioned above, by not providing the CA-cert, but only the two client-certs.

[expired user #10293]'s profile image [expired user #10293] posted 7 years ago Permalink

Hi all,

For reference, a possible fix to this issue is to use an updated libmysql.dll.

If you are using MariaDB, consider using the bundled libmariadb.dll, rename it to libmysql.dll then copy over to the HeidiSQL installation directory.

The location of the said file would be on the installation directory of MariaDB if you choose to install the 'Development Components' during installation. (Windows)

[expired user #10293]'s profile image [expired user #10293] posted 7 years ago Permalink

In addition to this, I also found out that certificates using the sha256 as the signing hash causes this problem to pop out. I tried generating certificates with a signing hash of sha1 and the problem does not show up.

[expired user #10783]'s profile image [expired user #10783] posted 7 years ago Permalink

I've used the newer version of libmysql.dll as well and it worked well, but I posted here so perhaps developers would consider using new libmysql version in future releases.

Not likely but don't wanna to run in some corner case where my database got suddenly deleted just because HeidiSQL hasn't been tested against that particular libmysql version I'm using. ;)

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

Using 9.5 and trying to connect securely to Azure, I have the same error.

The Azure instructions at docs(dot)microsoft(dot)com/en-us/azure/mysql/howto-configure-ssl use the BaltimoreCyberTrustRoot.crt.pem certificate as referenced in the doc.

It only seems to use this ONE certificate to work, or not as the case may be.

P.S. Sorry about the dots in the microsoft link, but I cannot submit with URLs.

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

Using 9.5.0.5916, getting same error: "SSL connection error: ASN: bad other signature confirmation". Trying to use only the pem file (in the SSL CA certificate slot).

NOTE: AM able to connect to the same (Azure) database with HeidiSQL when SSL is NOT enabled.

ansgar's profile image ansgar posted 6 years ago Permalink

I should go and update libmysql.dll from a current MariaDB release, as stated above by shinnra. I could have done that before releasing v9.5, which is too late now. So I'll probably release a v9.6 so everybody gets the updated library with the installer.

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

Awesome to hear that ansgar. Thank you.

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

Happy New Year!

Thank you, that would be fabulous Ansgar.

Code modification/commit d385628 from Ansgar Becker <anse@heidisql.com>, 6 years ago, revision 5217
Update libmysql.dll to libmariadb.dll from the current 10.2.12 GA release. Leave support for libmysql, for users which don't yet have the new file in their Heidi directory. Should fix non working SSL connections, like described here: https://www.heidisql.com/forum.php?t=19494
ansgar's profile image ansgar posted 6 years ago Permalink

I have just pushed a brandnew libmariadb.dll from the current v10.2.12 GA release of MariaDB.

You will need to download the nightly built installer of HeidiSQL to get these, not just the updated heidisql.exe.

I added a fallback for users which have libmysql.dll but not yet the new libmariadb.dll, so there should not be too many issues.

I guess this finally breaks connections to pre-4.1 servers, or servers with old-passwords setting. At least in the v9.0 release there were several complaints about that.

rasmush's profile image rasmush posted 6 years ago Permalink

Just updated to the new release, but I'm still getting the same error... ???

ansgar's profile image ansgar posted 6 years ago Permalink

Did you use the installer, as mentioned above?

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

Did you use the installer, as mentioned above? Hi ansgar he used the installer

ansgar's profile image ansgar posted 6 years ago Permalink

If you still get the above SSL error with the installer of HeidiSQL r5217 or newer, you should verify you have a libmariadb.dll in your HeidiSQL folder, or still the old libmysql.dll.

If it's libmariadb.dll, then we have different issues here.

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

can u share it in step by step process

ansgar's profile image ansgar posted 6 years ago Permalink
  1. go to the download page and download the latest build "32/64bit installer" from the nightly builds section
  2. install it
  3. open the folder where you installed HeidiSQL via Explorer
  4. watch out for files: there should be a libmariadb.dll, but no libmysql.dll

The newer HeidiSQL build handles both dlls, but prefers libmariadb.dll. For the discussed SSL issue, some users mentioned that a newer library fixed their problems. So I expect the new libmariadb.dll to fix the issue as well.

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

hi all! I just tried the steps that ansgar posted, and I still can't seem to connect to my server on Azure. The SSL certificate works from my local dev environment, so that isn't the issue, but I am now getting a "Certificate Signature Check Failed" error. Any ideas on what that issue might be/has anyone else ran into that and resolved it?

Thanks! Ben

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

hi bmumma and ansgar(hi every one XD), i just ran the steps of ansgar and it worked for me, I do not get the error annymore SSL connection error: ASN: bad other signature confirmation i worked with a centos machine as server and a windows 10 machine as client.

Greatings,

Sytse

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

Hi,

the problem is not solved. I'v just downloaded latest version of HeidiSQL (9.5.0.5196 also portable) and SSL connection can not be established.

The file libmariadb.dll is present in portable version, but missing in installer version. The connection is impossible in both ...

I can connect with mysql command line.

H

ansgar's profile image ansgar posted 5 years ago Permalink

Install the latest installer from "nightly builds" section of the download page. Not the release on top of it.

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

OK, thanks. Now it works :-).

When do you expect to get it in stable release?

ansgar's profile image ansgar posted 5 years ago Permalink

I released 10.0 yesterday, short after my posting from above :)

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

Hi unfortunately the fix doesn't work always an all environments.

I'v installed the 10.0.0.5460 (and all later versions) on my laptop and again, I am not able to connect with following error (see attachment).

image description

The laptop and desktop are running both on Win 7 Pro x64.

1 attachment(s):
  • HeidiSQL-SSL
ansgar's profile image ansgar posted 5 years ago Permalink

Please have a look at the SSL issues in the tracker. If you can't find a matching one, then file a new one, posting that error message and whatever you think is required for reproducing that.

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

OK. see issue

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