Phpstorm. Can't access to mysql server through SSH tunnel (connection is closed by foreign host)

Question

Sorry for the mistakes I've made, I'm not Englishman.

I use vagrant to deploy remote virtual machine as my web server which is configured to use :private_network, ip: "192.168.10.10". On the machine is installed Nginx, PHP, MariaDB etc.

Recently, I start to use phpstorm as my main IDE. Now I'm trying to configure access to remote database server, but I fail. Please, see the screens for additional information.

To check the ssh and mysql server access I tried the following:

$ ssh [email protected] #ok
$ mysql -h localhost -P 3306 -u root -p #ok (it's run on the remote machine)
$ telnet -l root localhost 3306 #also run on the remote machine,
the output:
5.5.5-10.0.7-MariaDB-1~precise-log%how)b3s?�O|G*=63=yi#qmysql_native_password
Connection closed by foreign host.

MariaDB configuration file seems to be ok. I only changed bind-address in /etc/mysql/my.cnf to 0.0.0.0.

I tried to set different configuration data, like: localhost:8080 instead of 169.168.10.10:80, double check password in SSH tunnel window, but nothing. Please, help me..

Solution 1

In my case I had to enable TCP forwarding in the ssh configuration of the remote host:

AllowTCPForwarding yes

And also I had to grant all privileges on users connection from 127.0.0.1 (instead of just from 'locahost'):

grant all privileges on some_db.* to [email protected] identified by 'secret';

Solution 2

You don't have to use the Vagrantfile for port forwarding, vagrant will take care of it itself. In this case, once the VM is up and running (using vagrant up) use the command vagrant ssh-config to ascertain which SSH port you should use, as running multiple VMs will mean port clashes (which vagrant takes care of, but you may not be aware of which port to use).

In PHPStorm 6 to connect to a vagrant hosted VM's MariaDB/MySQL, open the Database Window (View > Tool Windows > Database) and add a MySQL connection using the wizard. The trick is what to put in where.

If it helps, permit me to explain in full how I connect, using an identity file (also shown in the vagrant ssh-config utility):

1. Ensure that the VM has AllowTCPForwarding (as mentioned above) set up in /etc/ssh/sshd_config and that the ssh service has been restarted, or the VM rebooted since that change was made
2. Once the VM is up and running, run vagrant ssh-config
3. Note the Port number
4. Note the IdentityFile being used

Once you have that, start PHPStorm, open your project and then the database window (View > Tool Windows > Database). You've probably already got a connection listed, but if not, click on the + icon, then Data Source > MySQL.

When the Data Sources and Drivers dialogue box opens, complete as outlined below:

General Tab

• Host : 127.0.0.1
• Port : 3306
• Database : Name of database required to connect to
• User : the MariaDB/MySQL user you'd normally connect with
• Password : the MariaDB/MySQL users password

SSH/SSL Tab

• Use SSH tunnel : enabled
• Proxy host : 127.0.0.1 (typically it's 127.0.0.1 but you can confirm this as it's outlined in ssh-config's HostName)
• Port : (as outlined in ssh-config's Port)
• Proxy user : (the name of the account that you'd normally use to SSH into the VM with. Typically this is "vagrant" for vagrant boxes)
• Auth type: Key pair (OpenSSH)
• Private key file : (the path ssh-config's IdentityFile)

If you understand that the SSH connection is handled first, and then the database connection, then the settings start to make a little more sense. For example, you'll note that nowhere do you have to type in the IP of the vagrant box itself, that's all handled through vagrant's (automatic) port forwarding.

Solution 3

you must forward the ssh port (22) by editing Vagrantfile

config.vm.network "forwarded_port", guest: 22, host: 22

and Reload vagrant box.

then you go to the SSH/SSL tunnel tab and check Use SSH tunnel

Proxy host: localhost
Proxy user: vagrant
Auth type: Password
password: vagrant

Then go back to Database tab and hit Test Connection!!!

Comments

[Timur Fayzrakhmanov]

I can't check this answer now, but still) just thanks)

[Souvik Ghosh]

I was indeed providing wrong Private key file : (the path ssh-config's IdentityFile), fixing that it fixed my connection problem. Thank you for the detailed step by step answer.

[Jonathan]

does not work anymore

[Jonathan]

ERROR: Base64-encoded string must have at least four characters, but length specified was 3.