Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Bad handshake when user SSH Tunnel with SSL

Jelmer's profile image Jelmer posted 9 months ago in Creating a connection Permalink

Hi there,

When I make a connection to a server using SSH Tunnel and SSL, I get the error "Bad Handshake". A connection to the same server can be made directly with SSL, or through a SSH Tunnel without SSL.

Also, it is not possible to change the SSL checkbox when the connection type is SSH Tunnel.

We usualy use direct access using SSL for an elevated user. Only when we need root access (i.e. when working with permissions and users) we use SSH tunnel and "localhost" access for root. Allthough the tunnel itself will be encrypted, the local connection between SSH and Mariadb will not, and can be snooped, for example by a user script on a shared hosting server.

Any idea what could be wrong? I tried all available mysql.dll versions...

Regards, Jelmer

cBell's profile image cBell posted 8 months ago Permalink

I am experiencing a similar, if not same problem. This is an existing connection that worked fine ~20 days ago, but now I get the "Bad Handshake" error. It is using an SSH tunnel and require_secure_transport = ON. If I run plink.exe separately with the same arguments that HeidiSQL is using and then create a new "local" connection that connects to localhost over the port being redirected with plink, it works fine as long as the "Use SSL" checkbox is checked. Mysql error log isn't much help. Every time I connect, I get:

2019-03-04T18:55:45.614892Z 1016213 [Note] Got timeout reading communication packets
2019-03-04T18:55:45.614964Z 1016214 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.507653Z 1016218 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.507701Z 1016219 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.507816Z 1016224 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.509848Z 1016220 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.510792Z 1016222 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.510822Z 1016223 [Note] Got timeout reading communication packets
2019-03-04T18:55:57.510800Z 1016221 [Note] Got timeout reading communication packets
2019-03-04T18:56:02.688560Z 1016226 [Note] Bad handshake
cBell's profile image cBell posted 8 months ago Permalink

I should also probably mention I upgraded MySQL about 12 days ago to Ver 14.14 Distrib 5.7.25. It's entirely possible this is related to the upgrade, as I may not have tried to connect until today.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Reiner030's profile image Reiner030 posted 6 months ago Permalink

Hi, I had till now the same problem started together with plink error mentioned in issue https://github.com/HeidiSQL/HeidiSQL/issues/521#issuecomment-483363129 .

Today I had time to play with different MySQL variants/versions and connection types and found out, that my "probably special server :)" is a common problem ;)

I have setup - like you - SSL certificates for my MySQL servers (else SSL can't be used).

Because there was yet no IP check if IP address and no hostname is used in certificate I forgot about the possibility that the "Bad handshake" can be caused by certificate CN mismatch which is here the cause of connection failure because localhost/127.0.0.1 is not the same public IP/hostname given in certificate.

  • Perhaps someone can check this out and if MySQL/MariaDB/Percona servers and their clients have the possibility to use/check for alternative certificate subjects like webservers ("Multi-Domain certificate").
  • HeidiSQL should give better error message which should be possible like here found: https://aws.amazon.com/de/premiumsupport/knowledge-center/rds-error-2026-ssl-connection/

    ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure


Else there is already for new connections the SSL box unchecked.

But this can be an security problem when connecting through a ssh tunnel between ssh server over a remote network to a dedicated server or an ssh server connection to localhost where unwanted people could perhaps listen with tcpdump even on localhost interface.

So it would be much better for security to let SSL connection parameter active even for SSH tunnels but it should be allowed to uncheck this option.

Old connections are setup with SSL and deactivated checkbox.

=> Workaround to uncheck the SSL connection:

  • Copy your MySQL username to clipboard
  • switch from MariaDB or MySQL (SSH tunnel) to (TCP/IP)
  • go to Advanced tab and uncheck Use SSL
  • switch back from MariaDB or MySQL (TCP/IP) to (SSH tunnel)
  • copy back MySQL username to User field

Bests

Reiner

Reiner030's profile image Reiner030 posted 6 months ago Permalink

btw. if someone want to test certificate setup this tutorial is nice and even in German easy to understand (the openssl calls can be copy + pasted **:

https://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln

** Editor bug - I cannot write an ampersand character?

Jelmer's profile image Jelmer posted 6 months ago Permalink

Hi Reiner,

This is different from my problem, where the SSL certificates had a valid CN. A direct mysql client connection using ssl worked nicely.

For now the problem seems to be over. It occured mostly when connecting to older Centos machines running mysqld. With newer versions of the OS and mariadb, the problem does not occur.

Jelmer

Reiner030's profile image Reiner030 posted 6 months ago Permalink

Hi,

from your inital behavior posting it's the same type of problem so I added my post here also to have it as solution path for other users with same problem.

Initial idea was for me also that like last years there might be a problem with not matching encryption algorithms but since the direct connection is working this couldn't be the case.

Has your valid CN the "FQDN.external.hostname" AND "localhost" as CN /Alternative Subject ? Or did you connect to "FQDN.external.hostname" over SSH tunnel?

If not the CN isn't matched. In my case (we are using Debian Stretch and MariaDB repository) I have proofed the ssh tunnel settings on my test instance with connect to:

  • localhost => "Bad handshake"
  • 127.0.0.1 => "Bad handshake"
  • <public IP> => "Host 'test-instance' is not allowed to connect to this MariaDB server"

The "Bad handshake" error is to unclear what happens so it would be nice that HeidiSQL could do more debug/error message output for this case if it's possible (but it seems to be the only MySQL client error output?).

For my production server there seems to be a different problem; I got there for all cases the error:"Can't connect to MySQL server on '127.0.0.1' (10061)" but it also works with manual called plink.exe like in log line given.

Jelmer's profile image Jelmer posted 6 months ago Permalink

Well, one might think that's the case. We use self signed certificates with either the full host name as CN, or a special CN ("mysql:<hostname>"). No localhost on the cert.

I experimented, and even on newer servers (MariaDB 10.2) it just does not work:

  • Direct connection using SSL with only the CA cert given works for any certificate that is signed by that CA, no matter what the CN
  • Tunnelled connection using SSL with the same arguments (only the CA cert given in HeidiSQL) always gives a bad handshake, even if both the host for the tunnel and the host for the mysql connection match the CN on the server cert.
  • Connections from the same pc using MySQL client and the same CA-cert as an argument always work
  • If I remember correctly (not checked this time) a tunnelled connection from another server, tunnelling through ssh and than connection to the localhost port with the mysql CLI and the same CA-cert, works.

The only thing that does not work (and never seems to work) is connecting through a tunnel using SSL. This is also an option that is greyed out when choosing to use a tunnel. But the last setting stays on. So, changing this setting, one has to change the connection type to TCP/IP, then change the advanced settings, then change th connection type back to SSH Tunnel.

Why do we need SSL when tunnelling?

  • We need tunnelling when connecting as root (only needed once in a while), because we want only localhost access for root
  • We need SSL because we don't want port readers on shared servers to see what's happening between the exit of the tunnel and the MariaDB socket.
Reiner030's profile image Reiner030 posted 6 months ago Permalink

The interesting part is that MySQL Workbench 8.0.13 - 8.0.16 works fine with SSL (using setting: "If available" and connection shows "SSL: Using ECDHE-RSA-AES128-GCM-SHA256") so this must be an issue with HeidiSQL or it's used MySQL/MariaDB client library.

From connection info window of HeidSQL with TCP/IP connect I have:

Server OS:                      debian-linux-gnu
Server version:                 10.2.23-MariaDB-10.2.23+maria~stretch-log mariadb.org binary distribution
Compressed protocol:            Yes
Unicode enabled:                Yes
SSL enabled:                    Yes
Client version (libariadb.dll): 10.3.6

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