I'm trying to connect the PhpED DB Client to a remote MySQL server on a shared hosting account. So I tried to do a similar setup with PuTTY since that's the SSH client I use, and I'm getting this error when I try to connect using the PhpED DB Client:



I assume the IP address it's showing is the server's IP address, because it's not mine.

In PuTTY I went to Connection->Tunnels and added a "Local" forwarding (forwards any local requests to a remote address) with these settings:
Source port: 3306
Destination: mydomainname:3306
and kept all other settings default.

In PhpED I have these settings:
Hostname or IP address: localhost
Port: 3306
Initial database: mydbname
Use compression is unchecked
Login Name: mysql_username
Password: mysql_password
Save password is checked

I haven't touched any of the Proxies/Firewalls or Keys settings.


Could somebody who uses PuTTY try out my PuTTY tunneling settings and help me figure out if that's the source of my problem? I don't have Cygwin SSH, and I'd rather continue using PuTTY if possible. Is there a way for me to check if my tunnel is properly connected without using DB Client?

I'm a little confused. Are you trying to connect to a MySQL database, or are you trying to establish an SSH connection to the command line? From looking at the command and the fact that you're using Putty, it looks like you're trying to connect to the command line, but you're also trying to connect to port 3306 which is usually used by the MySQL database server.

Perhaps you are trying to create an encrypted connection to the MySQL database? If that's the case then your approach is incorrect, but I'll go into more detail later if that's what you're trying to do.

I'm trying to connect to a MySQL DB on a remote server (web hosting account) using the DB Client in PhpED. dmitri said that in order to do this I needed to tunnel via SSH first to redirect any local MySQL requests from my machine to the server's address, which is what I was trying to do with PuTTY. I've never tried to tunnel through PuTTY so I don't know how it works exactly, but I would guess that at the same time that I establish an SSH connection and get a shell prompt from the remote server, PuTTY tries to establish a port forwarding tunnel if I specify one in the settings, which I've listed above. So right now I'm getting the error I quoted when I try to connect to the DB via DB Client in PhpED. The base SSH connection is fine, since when I login using PuTTY I can do all the usual shell prompt stuff and get into MySQL from a prompt.

nothsa,

There is nothing incorrect. Using ssh tunnels is a common way to connect to many databases using secured protocol. MySQL is no an exception.

MentatYP,
from the error message you shown, you got connected to the database but authentication has failed.
There are two possible reasons for this:
1) check if user mysql_username@some_ip_address is granted with appropriate rights. By default MySQL distinguishes the pair username+IP address and when you connect from another point, it checks its users table to see if that user+address are allowed. To grant a user right to connect from any address you need to specify % as address in GRANT statement. Please read MySQL manual for further details on GRANT.
2) some_ip_address is other than mysql server's own address, you probably didn't setup ssh tunnel correctly or gateway you connected to is not the server running mysql. In this case you may want to run 2nd tunnel on the far end (between gateway and host running mysql) or provide some_ip_address with grants as specified per p.1. If gateway you connected with ssh is the server running mysql, you may want to specify localhost in the tunnel settings. In this case your user wil be recognized as username@localhost and probably it's what you already have in the users table.

Dmitri:
I use tunnels myself, but that SSH command he quoted was incorrect which was throwing me off. I thought he was trying to login to SSH through his MySQL server =)

MentatYP:
Something else to check:

Do you already have a MySQL server running on your local machine? If so, it might not be setting up the tunnel on local port 3306 as it's already in use. You might accidentally be trying to connect to your local machine because it hasn't set up the tunnel, which might account for the user/pass not working. Try using a different source port (something like 9999) and connect to that instead.

starting with version ~4 or about, openssh provides support for local address binding and therefore -L localhost:blah blah blah is correct since that. For earlier versions and in case of Putty in particular, the command line may be completely different. For example a correct example of -L option for openssh would be -L 3306:localhost:3306 (or -L 3306:127.0.0.1:3306)

I think the problem is that he established tunnel to ethernet's IP (see "Destination: mydomainname:3306")
and therefore mySQL saw it as a root's connection from mydomainname (see denied for user 'mysql_username'@'some_ip_address')
I think if he ran tunnel to localhost, he would succeeded.

Huh! Learn something new every day. Thanks =)

Something useful in Putty is the event log. Right-click on the title bar and choose Event Log. Do this on the actual terminal window after it's done its connection sequence. Scroll down past the authentication stuff, and you'll see a list of what Putty did with the port forwards. I found it very useful a few months back when I had to set up a complicated sequence of multi-machine tunnels, and it remains useful on those occasions when a tunnel mysteriously doesn't work (typically because a Win app is styll tying it up).

as far as I see putty was okay and I would not expect anything interesting in its logs
From the error message, it's mysql authentication did not pass and I explained why.

It's still a useful tip, though, especially to someone new to Putty (like I was - I am new to Windows with its lack of a real command line). Someone could easily stumble on this thread in the future looking to solve a similar problem, and the Putty log might help them solve it.

no doubts

Thanks for all the help, everybody. I've been out of town the past couple days, so hadn't had a chance to try the suggestions.

dmitri,
Your suggestion to tunnel to localhost instead of mydomainname indeed did the trick! Seems counter-intuitive to me, but since I'd never done this before that doesn't mean much For anybody who finds this thread in the future, this is how I setup PuTTY:

For whatever saved session you're using:
- Load the saved session. Don't connect yet; just load the session.
- Go to Connection -> SSH -> Tunnels
- Add new forwarded port with Source port set to 3306, and Destination set to localhost:3306. My mistake here was setting this field to mydomainname:3306, which still seems like it should be the right way, but obviously it's not.
- Leave the Local and Auto radio buttons selected.
- Save session, then connect.

And next time you use this saved session it'll remember your tunnel settings. Thanks again. Now that I've got the DB Client working, maybe I'll fiddle with remote debugging next. Real debugging in PHP instead of a series of echo statements? What will these crazy kids think up next?!