SSH tunnel to MySQL running on FreeBSD (Heidi SQL)

Question

I'm trying to use a SSH tunnel to access a MySQL-server running on a FreeBSD 10.1 server. These attempts are done with Heidi SQL (http://www.heidisql.com/).

The SSH daemon on the FreeBSD server does not permit root login. We must first use an account member of wheel group, then SU over to root.

I think the question is: Do the SSH daemon need to permit root login to make it possible to connect to the MySQL server? Because no matter what I try, the MySQL server is responding with "Access denied".

Output from HeidiSQL:

/* [SRV03 Live (SSH)] Connection to 127.0.0.1 closed at 2015-07-16 13:20:00 */
/* Connecting to 127.0.0.1 via MySQL (SSH tunnel), username ******, using password: No ... */
/* Attempt to create plink.exe process, waiting 4s for response ... */
/* C:\Program Files\Putty\plink.exe -ssh [email protected] -pw "******" -P 12345 -N -L 3307:127.0.0.1:3306 */
SELECT CONNECTION_ID();
/* Connected. Thread-ID: 1913 */
/* Characterset: utf8mb4 */
SHOW STATUS;
SHOW VARIABLES;
/* Entering session "SRV03 Devel (SSH)" */
/* Access is denied */

Any thoughts on this would be much appreciated.

Thanks,

Answer 1

Your problem is most likely related to MySQL. Less so HeidiSQL and least FreeBSD. You give precious little details but we can say the following:

Misconceptions:

• The FreeBSD system "root" and the MySQL "root" users are not related!
• You do not need to log into the FreeBSD system as "root" to work within the MySQL database as MySQL "root"
• You do not need to use an account which is member of wheel (because you do not need to su to "root")
• Any FreeBSD user can connect to MySQL using any MySQL username. Access to MySQL is restricted on named pipes or TCP. MySQL does not care which system (FreeBSD) user is connecting.

You are trying to bind 3 technologies together: MySQL, SSH tunnel and HeidiSQL.

For troubleshooting you need to confirm that each component is working individually. For now it seems that you try to fix everything at once. But we need to establish the root cause (Not to be confused with either the FreeBSD "root" or MySQL "root" :-))

FreeBSD

When you try to connect to the MySQL server this happens through the network stack. If you have no firewall setup then you should be good to go.

But if you have a firewall (typically "pf") then you may need to allow access on port 3306 (MySQL) for localhost (127.0.0.1). This is especially true if you run in jails. I assume that you are using a firewall because you are trying to tunnel the traffic. And I noticed that you have SSH running on port 12345 rather than 22.

If you are in doubt of proper firewall setup and you are working on a trusted network then disable "pf" while you are testing. This will make your life a lot easier!

MySQL

Verify that you have a working MySQL user and that you can connect to the database using a local network connection.

1. SSH to the FreeBSD server using any account

2. Access MySQL database using the MySQL client

   mysql -u USERNAME –h 127.0.0.1 –p

The -h is important as you then use a TCP connection and not just a named pipe. Verify that you can access the schema you want to use

mysql> USE databasename
mysql> SELECT * FROM tablename LIMIT 10

HeidiSQL

If you could connect via the MySQL Client then we can start testing HeidiSQL. If you have no firewall on the FreeBSD server then verify that you can do a direct connect from HeidiSQL using the MySQL username and password.

If you do have a firewall then I would try to open for port 3306 from your IP for testing. If you use "pf" then use one of the following:

pass in on $ext_if proto tcp from any to any port 3306

pass in on $ext_if proto tcp from YOURIPADDRESS to any port 3306

With this you can again confirm that you can connect correctly to MySQL from HeidiSQL without a tunnel. You can remove this rule when you are done testing.

Finally you can test by starting the tunnel manually. Quit HeidiSQL and enter the following on a command prompt:

"C:\Program Files\Putty\plink.exe" -ssh [email protected] -pw "******" -P 12345 -N -L 3307:127.0.0.1:3306

This should connect you to your FreeBSD server and establish a tunnel. If that is successfull you can start HeidiSQL and try to do a normal connection to MySQL (but on port 3307)

The above are the steps I would follow myself to debug an issue such as your.