Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SSH Configuration

behofmann's profile image behofmann posted 6 years ago in HeidiSQL portable Permalink
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
behofmann's profile image behofmann posted 6 years ago Permalink
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.
jfalch's profile image jfalch posted 6 years ago Permalink
[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.
behofmann's profile image behofmann posted 6 years ago Permalink
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.
behofmann's profile image behofmann posted 6 years ago Permalink
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)
behofmann's profile image behofmann posted 6 years ago Permalink
INFO: My MySQL Server Bind Settings on my Ubuntu Server are the defaults created by the MySQL Server Ubuntu Install Package.
jfalch's profile image jfalch posted 6 years ago Permalink
[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.
jfalch's profile image jfalch posted 6 years ago Permalink
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) ?
jfalch's profile image jfalch posted 6 years ago Permalink
..AFAIK root / localhost combination often does not have a password; try by removing password form 'settings' password field.
behofmann's profile image behofmann posted 6 years ago Permalink
In my HeidiSQL SSH tunnel parameters, I don't have a Private Key file defined. Should I generate one to attach there?
jfalch's profile image jfalch posted 6 years ago Permalink
how do you connect to your server via ssh ? do you use plink ?
behofmann's profile image behofmann posted 6 years ago Permalink
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?
jfalch's profile image jfalch posted 6 years ago Permalink
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 ?
behofmann's profile image behofmann posted 6 years ago Permalink
Yes I successfully login into my Unbuntu Server with Putty using just username and password.
jfalch's profile image jfalch posted 6 years ago Permalink
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.
jfalch's profile image jfalch posted 6 years ago Permalink
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 ?
behofmann's profile image behofmann posted 6 years ago Permalink
My password column has a hex string prefixed by '*'.

Thanks for your help!
jfalch's profile image jfalch posted 6 years ago Permalink
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').
behofmann's profile image behofmann posted 6 years ago Permalink
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?
jfalch's profile image jfalch posted 6 years ago Permalink
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' ?
jfalch's profile image jfalch posted 6 years ago Permalink
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 ?
behofmann's profile image behofmann posted 6 years ago Permalink
Same 1045 error message
behofmann's profile image behofmann posted 6 years ago Permalink
The only way I can connect to another saved session is to first disconnect the one in use.
jfalch's profile image jfalch posted 6 years ago Permalink
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 ?
jfalch's profile image jfalch posted 6 years ago Permalink
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

behofmann's profile image behofmann posted 6 years ago Permalink
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?
jfalch's profile image jfalch posted 6 years ago Permalink
since max_connections default is 151, commented out is ok.

do you connect to both servers via ssh ?
behofmann's profile image behofmann posted 6 years ago Permalink
Yes, both HeidiSQL sessions have the same configuration except of course for the Server ID/Password.
jfalch's profile image jfalch posted 6 years ago Permalink
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.
behofmann's profile image behofmann posted 6 years ago Permalink
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
jfalch's profile image jfalch posted 6 years ago Permalink
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 ?
jfalch's profile image jfalch posted 6 years ago Permalink
+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.
behofmann's profile image behofmann posted 6 years ago Permalink
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
behofmann's profile image behofmann posted 6 years ago Permalink
BTW, I'm using HeidiSQL with Windows 7 Ultimate over a Wireless Connection.
jfalch's profile image jfalch posted 6 years ago Permalink
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.
behofmann's profile image behofmann posted 6 years ago Permalink
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?
jfalch's profile image jfalch posted 6 years ago Permalink
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.
behofmann's profile image behofmann posted 6 years ago Permalink
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?
jfalch's profile image jfalch posted 6 years ago Permalink
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.
behofmann's profile image behofmann posted 6 years ago Permalink
Will do; thanks for agreeing that HeidiSQL should present the user with PLINK's message for cache acceptance.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.