SSH Configuration

Hello,

I'm using HeidiSQL Portable 8.3.0.4694 with SSH to access MySQL databases. My connections are intermittent with the same credentials; sometimes it connects quickly and other times it fails with connection errors (10061). I've tried increasing the default timeout to 25 seconds, but it still fails.

I verified I can access MySQL on my Ubuntu Server with Putty. I know HeidiSQL uses the Putty PLINK. Could there be a problem with the private key file? I've cached it through Putty, but does HeidiSQL have to resolve it everytime?

Any direction to help stabilize my MySQL Connections to make them dependable would be greatly appreciated.

Thanks,

Brandon

Update:

I keep getting SQL Error (1045) in statement #0: Access denied for user 'root'@'localhost' (using password: YES)

My Hostname/IP is 127.0.0.1 which is the same as localhost.

[first post] what means " I've cached it through Putty" - how exactly did you do this ? are you using pageant ?
And yes, plink has to use the private key to authenticate every time it connects to your server via ssh; this cannot be changed.

[Update] using heidisql's user manager, check wether the 'user account' list on the left side contains an entry with Username = root, Host = localhost. It is NOT sufficient to have an entry root / 127.0.0.1; 127.0.0.1 is usually identical to localhost for routing, but not for mysql's authentication.

Thanks for your reply. Caching through Putty means when I've logged in and Putty prompts me yes or no to cache the key. I cached the key so my subsequent Putty logins don't ask this message.

Under settings, I will try changing Hostname/IP to localhost instead of 127.0.0.1 for MySQL authentication.

After changing to localhost and increasing the timeout too, I still get the same SQL Error (1045) in statement #0: Access denied for user 'root'@'localhost' (using password: YES)

INFO: My MySQL Server Bind Settings on my Ubuntu Server are the defaults created by the MySQL Server Ubuntu Install Package.

[Caching] that's what I thought, and you are in error that this would somehow cache your private key - it does not. it stores the server's public key in plink's configuration so that plink can check for men-in-the-middle attacks in future.

the private key file (.ppk) has to be specified in the heidisql session parameters (ssh tunnel tab), or via the -i switch to plink if you use it manually.

however, since you seem to be able to connect to the server sometimes, i do not think your problems ist is related to the .ppk - access would consistently never work if there were a problem with the private key.

1045 is "authentication error" from mysql server. are you sure you specified the correct password for user root in heidisqls configuration (session manager, 'settings' tab) ?

..AFAIK root / localhost combination often does not have a password; try by removing password form 'settings' password field.

In my HeidiSQL SSH tunnel parameters, I don't have a Private Key file defined. Should I generate one to attach there?

how do you connect to your server via ssh ? do you use plink ?

I use Putty which may use PLINK behind the scenes. Putty displays a message to cache the private key which I referenced earlier. Do I need to do the same in HeidiSQL with a private key file since it uses it every time?

In Putty, do you use a private key file (Connection / SSH / Auth) or just username and password ?
can you successfully connect to your server's shell with Putty ?

Yes I successfully login into my Unbuntu Server with Putty using just username and password.

Then you do not need to use a private key file within heidisql, either. Usernaem and password in the session manager's "SSH tunnel" tab should be sufficient.

back to 1045. when you do query

select Host,User,Password from mysql.user

is there something (long hex string, often prefixed by '*') in the Password column of the record with Host=localhost and User=root, or is this column empty ?

My password column has a hex string prefixed by '*'.

Thanks for your help!

if this is so, root from localhost has a password.
are you sure that you have the correct (mysql) password in the Password field of the 'settings' tab ? (below 'User' = 'root')
NB you probably know that the above mentioned username/password that you can use with Putty go into the Username/Password fields of the 'SSH tunnel' tab (below the name of your host in 'SSH host').

Yes, I understand the MySQL credentials are on the Settings Tab and the Server credentials are on the SSH tunnel.

I deleted my HeidiSQL Session Name and recreated the one I couldn't access. It now works, BUT I now can't access my other saved session name. I know I had the credentials entered correctly for both saved session names.

Does HeidiSQL only allow one open session at a time? Does it use the credentials of the previous open session when launching a new session?

you can try the query

SELECT u.Password=password('xxx') AS Ok FROM mysql.user u WHERE u.User='root' AND u.Host='localhost'

replacing xxx by the password that you use from heidisql. What is the value of 'Ok' ?

Does HeidiSQL only allow one open session at a time?
No.

Does it use the credentials of the previous open session when launching a new session?
No.

How do you try to access the second session ? From the 'File' menus 'connect to >' submenu ? what happens when you try to connect a second session ?

Same 1045 error message

The only way I can connect to another saved session is to first disconnect the one in use.

do both sessions refer to the same server ? if so, does connectinmg the first session succeed, attempt to connect to the same server again with same suth data fails with 1045 ?

can you check your mysqld servers config file (my.cnf) ? does it contain a line starting with

max_connections =

if so, what number behind the '=' ?
if not, try to add such a line to the [mysqld] section eg

max_connections = 10

Each session is a different server.

On both servers my max_connections is 100, but commented out. Why would that make a difference if I'm the only one connecting?

since max_connections default is 151, commented out is ok.

do you connect to both servers via ssh ?

Yes, both HeidiSQL sessions have the same configuration except of course for the Server ID/Password.

please check wether both sessions also have the same value inthe 'SSH tunnel' tab 'Local port' field; if so, change it to be different - ie make one 3307, the other 3308, or such.

Sorry for a late reply. I tried changing the port numbers and I can't access my saved sessions. What's odd now is that none of my sessions work.

Has anyone else had SSH problems? Are there any known issues?

Thanks,

Brandon

yes, there is a known issue: lack of patience.

what exactly means "i can't access" ? what exactly happens when you try to open a saved sesion with only the local port changed from before ? what error message (if any) is shown ?

please do also try

plink.exe -v -pw password -L3307:127.0.0.1:3306 user@host

and look at the messages. Anything strange there ? does it open to a shell on your host or not ?

+yes. i must ascertain that this post exchange has been going on for quite a while now and that none of the things i have asked or suggested has helped. you seem to have a particular problem that i have not heard of from any other users of heidisql; especially changing only the local port should not have blocked the existing sessions. so i try to debug this remotely, which is slow and frustrating. sorry about that.

First, I apologize, I meant no offense in my previous posting. I simply was wondering if there are any known issues that are being researched and fixed by the HeidiSQL Team.

Second, thank you for your quick responses and suggestions helping me. This reinforces my loyalty to use HeidiSQL.

Third, as we try different things, I'm looking for patterns on behavior.

I ran "plink.exe -v -pw password -L3307:127.0.0.1:3306 user@host" for all my HeidiSQL Sessions from the command prompt and was able to log into each with no problems.

AFTER using command prompt, I was able to log into my first SSH HeidiSQL Session. Leaving the first session open, I tried my other two saved sessions with all three having the SAME SSH tunnel local port of 3307. These two sessions failed with MySQL Error 1045 "Access denied for user user@localhost using password YES".

BUT then I changed the ports of the last two saved sessions to 3308 and 3309 with the default 4 second timeout and HeidiSQL was able to access ALL saved sessions at the same time. I can switch between connections by clicking the session name.

So the pattern I see is I can use HeidiSQL AFTER running PLINK from the command prompt. When I did this some sessions, but not all asked me to cache the key. This is the only pattern I see.

I would appreciate your thoughts and input.

Thanks,

Brandon

BTW, I'm using HeidiSQL with Windows 7 Ultimate over a Wireless Connection.

two remarks upon your experience.

a) it is principally not possibly to use a tcp port for more than one connection; that's why you have to use different local port numbers in sessions that you plan to use at the same time. (there was a feature suggestion some time ago to automatically set the local port field to some free port's number upon session creation, which would handle this problem automatically; but it has not been implemented yet.)

b) "HeidiSQL AFTER running PLINK" - yes, i should have thought of that earlier... plink, when contacting a server for the first time, always queries the user wether to cache the server's key in registry, as you have seen.
if you do not answer this question, plink will silently fail after a time; that's most probably what happens when it is called internally from heidisql.
once you have answered this question with "yes", it will not again be asked, and further plink calls will succeed from within heidisql.
although heidisql's handling of text responses from plink has been improved considerably, it still does not always work; thus, you generally should call plink manually once for each new server to answer the "caching" question, and store its answer in the windows registry. after that, connecting to that server from heidisql via ssh should generally work.

Do you know if caching expires in the Windows Registry? I have answered yes before from the command prompt and today it asked me again for the same server.

Is it a good practice to simply run PLINK first from the command prompt before starting a saved session from HeidiSQL even if its not a new server?

in general:

- AFAIK the caching does not expire. It lives at HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\SshHostKeys, key being the server's name, and the question will only be asked again if a server's public key should change (which is really rare in practice).

- so I dont think plink-before-heidisql is necessary; in most of the cases it should only be done once for a new server.

But: I do not have an explanation why you were asked again for your server (unless you did it as different windows users, which i do not assume). I suggest you observe the behaviour for a while, possily yours is a special case for reasons unknown.

you could also write to ansgar (the developer) and plead for upgrading heidisql's handling of text responses from plink, so that the "cache this key" question, if it occurs, is related to the heidisql user not as a simple message dialog, but as a yes/no or text input dialog.

THANK YOU for your excellent suggestions and your time! I will use HeidiSQL and watch its behavior. I feel confident I have a solution to access my servers through my saved sessions now.

I agree it looks like the only "issue" with HeidiSQL is the ability to allow the user to respond to PLINK's Cache question. How do I contact the developer to suggest an enhancement?

make a post in this forum. (a previously existing issue tracker has been deprecated.)
mentioning his name (ansgar) might help.

also, except for the long run of experiments necessary to come to this conclusion, the real problem found - the somewhat strange necessity to run plink once manually for each new server accessed via ssh - has flummoxed quite a number of people before; i think it really should be addressed (in plink response handling) to prevent repeated support requests for ssh problems in the first case.

Will do; thanks for agreeing that HeidiSQL should present the user with PLINK's message for cache acceptance.