SSH Tunnel to Ubuntu 13.04/MariaDB

Trying to configure Heidi to connect from Win7 laptop to Ubuntu 13.04 server
with SSH Tunnel using Heidi (HeidiSQL 8.0.0.4396 32 Bit) and MariaBD (5.5.32)

Error message is:

SQL Error (2003) in statement #0: Can't connect to MySQL server on 'localhost' (10061)

I can connect to server over SSH link using PuTTY
I can connect to server using plink.exe with the command line: "C:\Program Files (x86)\PuTTY\plink.exe" -ssh username@servername
Port 3306 is open on the server
I can connect to MariaDb using the command line: mysql -h localhost -p -u root
I have increased the plink.exe timeout to 10 seconds


I have tried with Windows Firewall off
I have tried with following directives set and unset in /etc/mysql/my.cnf

# skip-networking
# bind-address = 127.0.0.1

Have googled etc and looked at user forums but still stumped

Any suggestions as to where to go next?

Thanks

try

plink -v -L 3307:servername:3306 username@servername

the messages should contain

Local port 3307 forwarding to servername:3306

if you see this, attempt to connect with heidisql to localhost port 3307 (or 127.0.0.1 port 3307 if you already have IPv6).

What happens ?

Thanks for prompt reply.

*************** plink stuff ****************************

C:\Program Files\Console2>"C:\Program Files (xS6)\PuTTY\plink.exe" -v -L 3307:192.163.1.73:3306 rcollins@192.163.1.73
Looking up host "192.168.1.73"
Connecting to 192.168.1.73 port 22
Server version: SSH-2.O-OpenSSH_6.lpl Debian-4
Using SSH protocol version 2
We claim version: SSH-2.0-PuTTY_Release_0.62
Doing Diffie-Hellman group exchange
Doing Diffie-Hellman key exchange with hash SHA-256
Host key fingerprint is:
ssh-rsa 2043 (deleted this bit)
Initialised AES-256 SDCTR client->server encryption
Initialised HMAC-SHA1 client->server MAC algorithm
Initialised AES-256 SDCTR server->client encryption
Initialised HMAC-SHA1 server->client MAC algorithm
Using username "rcollins".
rcollins@192.168.1.73's password:


************* ubuntu login messages confirming port forwarding 3307 >>> 3306 *******

Sent password
Access granted
Opened channel for session
Local port 3307 forwarding to 192.168.1.73:3306
Allocated pty (ospeed 38400bps, ispeed 38400bps)
Started a shell/command
Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-27-generic x86_64)
* Documentation: https://help.ubuntu.com/
System information as of Mon Aug 5 09:02:18 BST 2013
System load: 0.0 Processes: 122
Usage of /: 0.9% of 219.03GB Users logged in: 1
Memory usage: 1% IP address for eml: 192.168.1.73



Last login: Hon Aug 5 08:41:51 2013 from rcollins-pc.home
rcollins@serverl: ~rcollins@serverl:~$



************* back to Heidi *****************************

Settings for Heidi were:

Settings tab:
Network type : MySQL (SSH tunnel)
Hostname/IP : localhost *** tried this with 192.168.1.73 as well - same result
User : root
Password : my root password for MariaDB
Port : 3306

SSH tunnel tab:
plink.exe loc : C:\Program Files (x86)\PuTTY\plink.exe
SSH host+port : server1
Username : rcollins
Passowrd : my ubuntu server password for user rcollins
Plink timeout : 8
Private key : as default
Local port : 3307


The result was same error message:

SQL Error (2003) in statement *0: Can’t connect to MySQL server on 'localhost' (10061)


****************** continue with plink session from windows command line **********
**** cleaned up output to get rid of raw control codes

rcollins@server1:~$ mysql -h localhost -p -u root
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 5.5.32-MariaDB-1~raring-log mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select databases
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databases' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ohs1 |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)


MariaDB [(none)]>

***** end ***************

Sorry this is so verbose but you have everything I can think you might need.

Thanks

thank your for complete infrormation. it wsa important to see the line confirming port forwarding 3307 >>> 3306, to ascertain that port forwarding was not disallowed (or simply not switched on) by the server´s administrator.

now please retry the above, but before you do, please change the following on the "Settings" tab of heidsql´s session manager:
a) change the "network type" to "MySQL (TCP/IP)", and
b) change the port to 3307.
(if you use an explicitly run plink to establish the tunnel, the additional ssh tunnel attempt by heidisql must be switched off.)

Same error

Settings for Heidi were:

Settings for Heidi were:

Settings tab:
Network type : MySQL (TCP/IP)
Hostname/IP : server1 *** tried this with 192.168.1.73 and localhost - same result
User : root
Password : my root password for MariaDB
Port : 3307


Thanks

em - to make sure, did you first run

plink -v -L 3307:servername:3306 username@servername

up to the linux server´s prompt before starting heidisql with the above parameters ?
(Network type : MySQL (TCP/IP)
Hostname/IP : server1 *** tried this with 192.168.1.73 and localhost - same result
User : root
Password : ...
Port : 3307)

please also try 127.0.0.1 as Hostname/IP in heidisql.

Great - this worked:

plink -v -L 3307:127.0.0.1:3306 rcollins@server1

Enter password for rcollins and at system prompt on server go back to Heidi and use following settings:

Heidi Settings tab:
Network type : MySQL (TCP/IP)
Hostname/IP : 127.0.01
User : root
Password : my root password for MariaDB
Port : 3307

How can I set this up combo in the Heidi Session Manager?

in Heidisql session manager,
on Settings tab leave everything as-is, except for changing
- Network type to "MySQL (SSH tunnel)", and
- Port back to 3306.

In the now visible "SSH tunnel" tab,
- set plink.exe location (if not already done);
- set SSH host to your server´s name (server1),
- leave SSH port at 0 (for using default port 22).
- enter your Username on your server (rcollins), and your SSH Password for it.
- set private key file only if your server has/supports public/private key auth instead of providing password. (you could also use putty´s companion pageant in this case.)
- set Local port to 3307

that should do it.
(NB: there really should be some kind of FAQ for this, many people using SSH tunnel initially have problems with it, also with the special first-time only plink "store server key in registry?" problem.)

Fails with error message:

SQL Error (2003) in statement #0: Can’t connect to MySQL server on 'localhost' (10061)

Had a look at what the Heidi connection string. Set up same string in .bat file with following contents:

@echo off
"C:\Program Files (x86)\PuTTY\plink.exe" -ssh -N -L 3307:127.0.0.1:3306 -pw "xxxxxxxx" rcollins@server1

If I run this bat file and then connect using Heidi with TCP/IP, hostname 127.0.0.1 and port 3307 is works consistently.

It is no hassle connecting this way. I can't see where the difference is.

Many thanks for thorough and prompt responses

Robert Collins

one last thing you could try is to increase the "plink.exe timeout" in "SSH tunnel" tab, e.g. to 10. AFAIK, this is the time that heidisql waits for plink to establish the ssh tunnel before it attempts to connect to the mysql server.

Tried extending timeout to 10 - no difference.

I also re installed putty to a directory with no file spaces - no change.

I'm up and working okay even if it is a two step process.

Let's close this thread and if I solve the issue, I'll get back to you.

Thanks for your help

Finally cracked it.
There was an entry in the HOSTS file on my windows7 client pointing localhost to some long forgotten IP

reinstated

127.0.0.1 localhost

and all worked as expected.
Going back to your "there really should be an FAQ for this" - I'd be willing to draft something if you think it would help.

Let me know and thanks

I just had a similar problem. It turned out that the SSH connection to the server was established for the first time, so that plink.exe was prompting to accept the new fingerprint.
One-time execution of the mentioned plink command in the console solved the problem.

I was having same issue. After reading all the conversation above, I increased timeout to 20 and it started working.


my suggestion is to set default timeout at least 10.