"Host key verification failed" when trying to set up SSH tunnel for MySQL

[sKunKbad]

I see all over the internet tutorials that are basically saying that setting up the ssh tunnel for mysql is easy, but I get an error, and no joy:

 

Host key verification failed

 

This error is in a log file that I created. I am attempting to use PHP's shell_exec on my Ubuntu desktop:

shell_exec('ssh -p 2233 -f -L 3307:127.0.0.1:3306 acct@remote-server.com sleep 60 >> ./ssh.logfile 2>&1');

So, pretty standard according to the internet, but it's not working for me.

 

1) The remote server is a hosted website. It's a "semi-dedicated" plan, and just a glorified shared hosting account.

2) I can already do a passwordless SSH connection to the remote server by using the terminal. So my key based authentication is working for me.

3) I use SQLyog (MySQL tunneling through SSH) to this remote server. It's not key based, but the tunnel is there.

4) The host was not helpful. They were trying (I think), but nothing worked.

5) Yes, the remote server requires SSH connections on port 2233.

 

Why is this failing? I need somebody to walk me through this. I saw somewhere online that the error message may mean that apache was not able to check a known_hosts file. I created an .ssh directory at /var/www/.ssh, and I put a known hosts file in there. Chowned these to www-data:www-data. Permission set at 600.

 

Don't know what else to do or check.

[Jacques1]

Why are you running a PHP script on your PC to connect to a database on some castrated server? What are you even trying to do?

[sKunKbad]

PC is just a development environment, but production server is also Ubuntu, so figured if it works on dev it should work on production.

 

I'm supposed to create an application on that production machine that can do mysql queries on that "castrated" server. I cannot make a secure connection with MySQL because that castrated server is not set up for that. I think this might be my only option.

[fema3]

This is one of those things that only you can trouble shoot since there aren't any detailed logs provided. 

 

A logfile should have more detail as to why it is being rejected.  Some service providers have you log in through their console.  This is logging in through ssh, but you still need to setup ssh to log directly into the system.

 

It sounds like you are loggin into their system and using some kind of passwordless login. If that is true, you still need to set up ssh to actually login to the server.

 

Barring that,

Host key verification means exactly that. 

 

Check that:

on your server:

1-Your public key is in the authorized_keys file on your server, (~/.ssh/authorized_keys) ( you should have ssh-copy-id to the server or the server host should have given you a public and private key initially if that's how they roll)

2- the authroized keys file is in the correct format. (look at the keys, each on a newline)

3- your authorized keys file has the correct permissions 0600

 

on your local box:

4- check that your private key and .ssh folder have the correct permissions

5- check that there are no conflicts in known_hosts ( you would probably see this error though)

6- use the i switch ( ssh -p 2233 -i ~your-private-key -f...)

 

When you start talking about /var/www/.ssh, that's crazy.  You login to whatever account you have on the server with ssh.  It should be the account you log into, I use root, you might use whatever account that has sudo access, or just a local one with no special access.  Look into your /home directory for a list of users if you aren't sure. Your not logging in to apache, and there is no way that apache should be logging in remotely.  You are merely creating a tunnel.  There is no way that your ssh folder or keys should be owned or readable by www-data.  I'd delete that crap out of there asap.

 

Being that they tried to help you get going, I'm going to say that it is likely they don't disallow ssh logins.  It sounds to me like you either need to set it up, or you need to use the i switch to make sure you are using your correct key, or you need to check the permissions on your private key. 

 

Re-reading.. your 3rd statement, it appears that you need to ssh into your box in the same method as you do your SQLylog.  They might not allow key based ssh.  if that is the case, you might want to use a password file, or however you log in normally through your SQLylog. 

 

There are a lot of if's here, sorry.

[sKunKbad]

I did figure it out. The user running shell_exec, in my case www-data, needed its own self-created rsa key. I guess when I initially created it I didn't create it as www-data, so it didn't work. All is well now.

[kicken]

You can tell ssh which ID to use by using the -i parameter. You'll also want to provide the host key through the known hosts file.

 

So your command would be something like:

ssh -i /path/to/id_rsa -o "UserKnownHostsFile /path/to/known_hosts" -p 2233 -f -L 3307:127.0.0.1:3306 acct@remote-server.com sleep 60 >> ./ssh.logfile 2>&1

The id_rsa file would contain your user private key and the known_hosts file would contain the server's name and public key, for example:

remote-server.com ssh-rsa theserverpublickey

Provided the remote server has the user's public key in it's authorized_hosts file then the connection should be successful.

 

On a related note, why are you trying to establish the tunnel via a PHP script rather than just establishing one through a normal ssh session either when needed or persistently?

 

When I need to open a tunnel for various reasons I ssh into where I need to create the tunnel and use a command like:

ssh -L 3307:127.0.0.1:3306 -fNT -p 2233 acct@remote-server.com

That establishes a persistent tunnel that just runs in the background. It'll remain open until either the process is killed or some network failure occurs. Edited February 19, 2017 by kicken

[sKunKbad]

You can tell ssh which ID to use by using the -i parameter. You'll also want to provide the host key through the known hosts file.

 

So your command would be something like:

ssh -i /path/to/id_rsa -o "UserKnownHostsFile /path/to/known_hosts" -p 2233 -f -L 3307:127.0.0.1:3306 acct@remote-server.com sleep 60 >> ./ssh.logfile 2>&1

The id_rsa file would contain your user private key and the known_hosts file would contain the server's name and public key, for example:

remote-server.com ssh-rsa theserverpublickey

Provided the remote server has the user's public key in it's authorized_hosts file then the connection should be successful.

 

On a related note, why are you trying to establish the tunnel via a PHP script rather than just establishing one through a normal ssh session either when needed or persistently?

 

When I need to open a tunnel for various reasons I ssh into where I need to create the tunnel and use a command like:

ssh -L 3307:127.0.0.1:3306 -fNT -p 2233 acct@remote-server.com

That establishes a persistent tunnel that just runs in the background. It'll remain open until either the process is killed or some network failure occurs.

 

 

I was thinking that being able to start and stop the ssh tunneling would be nice to do with php, but after investigation I'm just going to use autossh and keep the ssh tunnel open permanently. Since the server will be using the connection at all hours of the day and night, I don't think it could hurt to keep it open.