MySQL Connection over SSL

smcpeck posted 3 years ago in General
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 posted 3 years ago
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.
e0steven posted 3 years ago
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.
e0steven posted 3 years ago
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 posted 3 years ago
Related: Issue #2336.

Just diving into the code and former issues I had with Heidi/SSL.
e0steven posted 3 years ago
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 posted 3 years ago
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.
jweeshgst posted 3 years ago
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.
ajbattrick posted 3 years ago

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 posted 2 years ago

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.

shinnra posted 2 years ago

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.

gutto posted 2 years ago

Having the same problem.

uve posted 1 year ago

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.

trgfree posted 1 year ago

rasmush was right

solved my problem

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

MiSAKACHi posted 1 year ago

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)

MiSAKACHi posted 1 year ago

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.

gutto posted 1 year ago

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

Chroma posted 11 months ago

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.

jomamano1 posted 11 months ago

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 posted 11 months ago

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.

gutto posted 11 months ago

Awesome to hear that ansgar. Thank you.

Chroma posted 11 months ago

Happy New Year!

Thank you, that would be fabulous Ansgar.

ansgar posted 11 months ago

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 posted 11 months ago

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

ansgar posted 11 months ago

Did you use the installer, as mentioned above?

pravallikavm1 posted 11 months ago

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

ansgar posted 11 months ago

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.

pravallikavm1 posted 11 months ago

can u share it in step by step process

ansgar posted 11 months ago
  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.

bmumma posted 8 months ago

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

sytse posted 8 months ago

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

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