Connecting to Remote MySQL Server Error 1045
I have successfully configured HeidiSQL to connect to my local instance of MySQL and to the local network copy of MySQL with no problems (using TCP/IP). I have successfully configured MySQL Workbench (latest version) to connect to the remote servers MySQL with no problem yet I am unable to get HeidiSQL to connect to those remote MySQL servers. I really like HeidiSQL to manage tables and data much more so than MySQL Workbench but I am at impasse because it refuses to connect.
I have downloaded the latest version of plink (release 0.62) and using the path specified I can connect to the remote server (using plink username@domainname and entering the SSH password) and from there run mysql -u username -p (enter the password) and voila, connected.
I had gone through a fairly length description of what I was able to find (HeidiSQL really could use some better logging) where just before I was ready to post as I had drafted I had found the following (and thank you but left it just a little confusing) http://www.heidisql.com/forum.php?t=6765
Here is my rendition of this thread's description:
- Use PuTTY to connect to the remote server and accept the key.
- Configure a new session as follows:
Network type: MySQL (SSH tunnel)
Hostname/IP: localhost (note: use localhost or an IP address relative to the remote connection)
User: username (MySQL username)
Password: password (MysQL username's password)
Port: 3306 (the port number that MySQL is using on the remote server)
SSH tunnel tab:
plink.exe location: C:\Program Files (x86)\HeidiSQL\plink.exe
SSH host+ port: domain.com 22
Username: username (SSH server username)
Password: password (SSH server password)
plink.exe timeout: 4
Private key file: empty
Local port: 33001 (this value needs to be an unused local port and to allow you for multiple concurrent remote sessions make this unique for each session)
This definitely works for me (one difference is the author of the above thread suggested to save the PuTTY session and use it at SSH host + port but I didn't have any trouble using domain and port 22 but that is an option and it did work).
Make sure to verify SSH settings by using PuTTY (or from the command line PLink) and verify the username and password. Also, and not certain if it is a version of MySQL or what and at least it is my experience, you need to setup the MySQL server user and set hosts to localhost (setting hosts to any does not seem to work) otherwise you will get SQL Error (1045) in statement #0: Access denied for user 'user'@'localhost' (using password: YES).
Two comments for HeidiSQL:
- how come there isn't any documentation on HeidiSQL (at least that I could find)? There is documentation on SQL but it appears to be SQL language and nothing about HeidiSQL? Even something online would be helpful.
- The plink timeout determines the delay in connecting. If I set the timeout to 30 seconds it will not connect until those 30 seconds have expired.
how come there isn't any documentation on HeidiSQL
Any former involved developer and me as the only current one is preferring to write a comprehensive documentation, most probably because that's not as exciting as writing new features and not even as much as fixing bugs. Feel free to write one, I'll gladly put that here on heidisql.com in some directory.
For my own part, I believe in an intuitive, self explanatory GUI. If HeidiSQL is not intuitive at some point, let's work on it, find better phrasing and labels for it.
The plink timeout determines the delay in connecting. If I set the timeout to 30 seconds it will not connect until those 30 seconds have expired.
Yes, that's intentionally, and that's why it's not set to 30s by default but 4s. HeidiSQL is not able to catch the successful call to plink.exe, which is the reason for why it waits until these 4s are reached even if the connection was established after 1s.
And I get it that writing help isn't as fun as adding features. I have most everything from embedded to websites, 8-bit micros to mainframes. Not trying to beat you up but I was a tad frustrated after spending about 6 hours (plus a previous attempt that I was unsuccessful at).
So, a couple of suggestions:
How about a help category on the forum that is stickied at or near the top of the forum categories and a link under help that takes us there? Then add items under that where you create the top level help item and then users can comment to it to clarify (similar to the PHP help). When someone has a "complaint" about missing or confusing items you can kick it back to them and ask them to write a help description and send it (post it whatever makes the most sense and easiest) to the forum and then you add it as a new item to the help. I would recommend that you monitor and post the initial help to that category so it doesn't get cluttered with bad help. People can comment to the help items once the item is added but it keeps it from getting to difficult for someone to follow (my general complaint about forums).
The second suggestion would be to modify the session manager as follows:
- I think the easiest change but not as "cool":
When the network type is MySQL (SSH tunnel), somewhere add to Hostname/IP (relative to remote host, typically localhost)
- Make the session manager box a little taller and when MySQL (SSH tunnel) is selected, merge the SSH tunnel tab and settings tab in to one with the SSH tunnel parameters first (look at MySQL Workbench as that is how they do it, in fact they have little comments to the right of the input boxes that really do help when setting up). And in any of the cases maybe the associated suggestion shows up when you mouse over the box or put a (?) close to the prompt or anything that you have done before or you feel is intuitive.
Sorry to have carried on but since this is the form of communication I like to be as helpful and informative as possible.
A couple more questions related to this subject:
- what is it that determines 'username'@'localhost' (I mean the 'localhost' side of that) and why when the user is defined as hosts - '%' or any does that user not work (i.e. the SSH connections I setup I had created the user george and set hosts to any (%) but it failed with 1045. First, can I do something that changes 'localhost' (I am thinking not but it is a question) and second is the fact that some MySQL servers allow me to define the user with any host (%) or that it has to be a specific host (localhost) relative to the version of MySQL or perhaps a MySQL setting? I connected to one machine both directly and via SSH with a user designated as hosts any and no problem authenticating to MySQL (it is MySQL 5.6.6). The other remote servers are running MySQL 5.1.61 on CentOS and I have to set the user to hosts localhost to authenticate. The question is if you know or have an idea not wanting you to investigate beyond your personal knowledge base, I can do that.
- when using plink, is the timeout only used during the initial connection or does it apply going forward? It doesn't appear to be affect the general function of HeidiSQL after connected but thought I would ask. And yes, I could in fact download the source code (and at some point I will).
- are there any libraries out there that could be used to replace plink for SSH connectivity? If I didn't have two jobs with very demanding people behind them wanting my time pretty much 24/7, I would have an interest in helping (perhaps a few months in to the future).
- is there a means to capture and log the actual plink command line and response? Could it be added to HeidiSQL?
Any of the above items that could be tasks I would be happy to open up issues and with any feedback you would provide. Also, if I have specific ideas for improvements to HeidiSQL, where is the best place to put them? I wouldn't mind posting them to the forum for a bit of vetting before having it submitted to the issue database. Having used MySQL Workbench (and there are a couple of things there that are nicer than HeidiSQL), HeidiSQL users could benefit from them.
A final note - HeidiSQL is much nicer to use than MySQL Workbench (and web based MySQL tools can't even be considered to be in the same class). I truly appreciate what HeidiSQL does for me as far as having to manage MySQL databases and even debugging SQL statements. I want you and the other developers to know that this is greatly appreciated.
Please login to leave a reply, or register at first.