Force 127.0.0.1 when host is 127.0.0.1 with ssh tunnel

[guestisp]

For no apparent reason, even by setting 127.0.0.1 as Host in the connection pane, Heidisql is still trying to connect to localhost

localhost and 127.0.0.1 are the same host, but MySQL refuses to authenticate due to grants set as myuser@127.0.0.1 and myuser@127.0.0.1 is different from myuser@localhost

This only happens when using a certificate file as identity. Direct access with ssh user/password (without the key), doesn't show the issue.

[oven8Mitts]

Seconding this;

Using mariadb on a Debian 10 system, using unix_plugin as an auth method for local host enables ease of use in the terminal, but requires a second user such as user@127.0.0.2 to be made for SSH tunnel access.

HeidiSQL for me as well, only SSH tunnels to localhost rather than 127.0.0.2 at the end of the tunnel, making it difficult to use both built in authentication and remote access.

Bump?

[ansgarbecker]

Could you examine the plink command line, which should show up in the bottom SQL log panel. And how to modify it so "localhost" is not used. There is no obvious part in that command line using "localhost".

[oven8Mitts]

Upon further investigation, it appears this is user error on my part. I tested this by setting Hostname / IP option to various things, such as my FQDN, hostname, random false names, and various localhost addresses. This all worked ok, it all connects to the correct address. There's nothing wrong with it.

I believe this is an older misconception where changing where the hostname would change the user in when running the mysql command.

For example:
mysql -u lee -h 127.0.0.150 --protocol=TCP -p
mysql -u lee -h 127.0.0.1 --protocol=TCP -p
Authenticated as lee@127.0.0.150 and lee@127.0.0.1 respectively back in mysql version 5.1.73.
This is no longer the case today, as the mariadb version 10.3 mysql command does not create this affect anymore. The host flag simply points where the connection goes, rather than having an affect on how the server handles authentication of the hostname.

Now a days, I believe mariadb is strictly using the source host, wherein SSH probably opens up a source local socket from 127.0.0.1 regardless of the destination. I disabled mysql reverse dns lookups service to confirm this.

There does not appear to be an easy way to set a source IP in an SSH tunnel from what I have googled.