Connection issue with SSH tunnel (localhost)

Hi! I've been using phpMyAdmin for managing databases on our server but needed another tool. I tried several I found HeidiSQL to be en great tool that suits our needs. Great!

I need to connect to our server using the SSH tunnel. This is working, but I have one issue:

We use a directadmin server to host sites and their db's. In the settings of a database we can set 'Access Hosts'. All databases have set 'localhost' as allowed 'Access Host'.

When I set up the SSH tunnel using 'localhost' this is somehow 'translated' to IP ::1. I believe this is the IPv6 equivalent to 127.0.0.1 (IPv4). But our database server does not allow '::1' to coneet. Only 'localhost.

So we get this error:

Access denied for user 'dbusername'@'::1' (using password: YES)

When I add ::1 to the 'Access Hosts' of a database the connection can be made. But this means I would need to add this IP to all our databases on the server. This is a lot of work.

Why is 'localhost' not send by HeidSQL? Or rather plink.exe? Does plink.exe change localhost to ::1?

I contacted ourr hosting provider to see if this might be an issue on our server but the say the server is expecting the hostnam 'localhost' but is send the ::1 IP.

Is there a way for me to use localhost without needing to add ::1 to all our databases 'Access Host' on our server?

You could have luck if your server variable skip_name_resolve was disabled. If you enable it, it may be the case this resolves the connection attempts to "localhost".

I would check if the variable is on or off:

SHOW VARIABLES LIKE 'skip_name_resolve';

If it's "ON", you can disable it on the server's my.ini or my.cnf:

[mysqld]
...
skip-name-resolve=OFF

Thanks ansgar. I will have a look at that.

But when we connect to MySQL using "localhost" as the hostname, I think MySQL knows that we are referring to the local machine without needing DNS resolution. Therefore, the skip_name_resolve option doesn't have any effect on connections made using "localhost."

I might be wrong...

I just can't seem to figure out why the default 'Acces Host' of the database (localhost) in Directadmin is not enough to allow the HeidiSQL database connection and why wee need to add ::1 to the 'Access Host'.

If have tried connecting usin plink outside of HeidiSQL. I can set up the SSH tunnel and after this connect to the database using the local host hostname. But withou adding ::1 to the 'Access Hosts' this won't work.

So somewhere 'localhost' is changed (resolved?) to ::1. Could this be happening in HeidSQL or is this happening on the server side?

We could add ::1 o all databases and be done with it, but weh have more then 100 database so I would prefer to fix this some oother way...

I can have a look at the connection code in HeidiSQL, but I'm nearly sure there is nothing special done with "localhost" or "127.0.0.1" or whatever.

I really recommend to have a look at the server documentation for skip-name-resolve: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve

Especially this triggers me here:

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with skip_name_resolve enabled.

I think we have located and fixed the issue on the server side. We did have the option 'skip_name_resolve' enabled. I think turning this off will fix our issues. Thanks for pointing is in this direction!