I've been desperately trying to get authentication via unix sockets to work over SSH. I have been testing FreeBSD and OpenBSD the past couple of days and did finally manage to connect to a FreeBSD VM by supplying the socket path in HeidiSQL's Hostname / IP field (which I'm not even sure if that's intended to work?). However, trying the same with an OpenBSD VM, it seems I've now hit a dead end.
These are the steps I've followed:
- Installed mariadb
mysql_secure_installation(enabled unix_socket authentication upon prompt)
- Created a "regular" user:
CREATE USER username@hostname IDENTIFIED VIA unix_socket;
- Created a database:
CREATE DATABASE db;
- Granted user access to database:
GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA unix_socket;
- Flushed privileges:
- Restarted mysqld
I am able to access the OpenBSD machine via PuTTy (both Plink.exe and PuTTy.exe) and open a MySQL client prompt without problems. But for some reason HeidiSQL just doesn't seem to be able to! I've checked firewalls, I've tried changing port numbers, I've tried all of the different library DLLs, even re-installing MariaDB -- nothing seems to work! The only thing that currently does seem to work is using password authentication.
Here are the errors I get for my respective input:
- Hostname/IP =
- Access denied for user '<username>'@'localhost'
- Hostname/IP =
- Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 0
That second error message is also returned for basically any "incorrect" hostname/ip value. So my guess is the server-end doesn't even recognize what HeidiSQL sends over as a socket path (yes, the path is correct).