Cant connect to Remote MySQL Server

[expired user #2154]'s profile image [expired user #2154] posted 17 years ago in Creating a connection Permalink
Hi, I cant connect to a Remote MySQL Server with HeidiSQL.

First of all, the target server is from a Hosting-Provider. I have access via PHPMyAdmin, so I suppose, I dont have a username oder password problem. I'm always getting the following error message:

Could not establish connection! Details:

SQL Error: Access denied for user: webXXX@p15152122.dip0.t-ipconnect.de (Using password:YES)


webXXX is the same username for the login via PHPmyAdmin
In the field Hostname, im using directly the Server-IP-Address


In PHPMyAdmin I found this information:

Server: Localhost via UNIX socket
Benutzer: webXXX@localhost

skip external locking ON
skip networking OFF

I only have access to my default user database in PHPMyAdmin.

I find some similiar postings, but they didnt help me.

Are there something specfic values, that needed to be added in the connection windows ?!


Thanks a lot!
[expired user #1821]'s profile image [expired user #1821] posted 17 years ago Permalink
you should check with your host that remote access is allowed

PHPMyAdmin runs (most likely) on the same server and therefor connects through the localhost
when you connect from your computer however, you connect from outside which can cause all sorts of problems. two most likely ones:
1. the server's firewall blocks your ip from port 3306 (the database port)
2. mysql simply doesn't allow remote connections for your username

but this is something only your host can tell you
ansgar's profile image ansgar posted 17 years ago Permalink

SQL Error: Access denied for user: webXXX@p15152122.dip0.t-ipconnect.de (Using password:YES)



Did you check this: http://dev.mysql.com/doc/refman/5.0/en/access-denied.html ?
ansgar's profile image ansgar posted 17 years ago Permalink
I'm nearly 100% sure your user has to be changed to allow connections from host "%", not only from "localhost" or "127.0.0.1". If you have the needed privileges to do so you can do that by using the existing phpMyAdmin. Locate your username in the table mysql.user, change the hostname to "%" (without quotes) and fire a "FLUSH PRIVILEGES". After that you should be able to use HeidiSQL on this server.
[expired user #1821]'s profile image [expired user #1821] posted 17 years ago Permalink
And should you have the privileges to do so, complain to your hostingprovider about the security.
If you can do this, any other customer can do it too ;)

In short: it's very unlikely you'll be able to do this. :)

p.s.
when granting a user permissions to connect from outside (yourself in this case) and you have a fixed ip-address, it's best to use that instead of %
[expired user #2287]'s profile image [expired user #2287] posted 17 years ago Permalink
This is what the lycos admin pages look like

To access your database, you can use our MySQL Manager. From your PHP scripts, you have to use the following parameters:

Database Type: mysql

Database HostName: localhost (you can not connect to your database from outside of your website)


Presumably HeidiSQL cant be used with Lycos servers???
ansgar's profile image ansgar posted 17 years ago Permalink

... you can not connect to your database from outside of your website) [/i]

Presumably HeidiSQL cant be used with Lycos servers???



Exact. The server has to allow external connections as HeidiSQL is normally not installed on the server itself.
[expired user #2287]'s profile image [expired user #2287] posted 17 years ago Permalink
Thats a shame it looks like a really nice bit of kit. Congratulations on it by the way and thanks for your help and your time.
[expired user #2287]'s profile image [expired user #2287] posted 17 years ago Permalink
Whats annoying is that I cant get phpmyadmin to work on the lycos servers and cant find any other software that I can use to import an old database. Have you any suggestions?

Failing that can you recommend an ISP that would let me use HeidiSQL to set up a fairly big Simple Machines Forum??
[expired user #1821]'s profile image [expired user #1821] posted 17 years ago Permalink

Whats annoying is that I cant get phpmyadmin to work on the lycos servers and cant find any other software that I can use to import an old database. Have you any suggestions?


PHPMyAdmin is just a PHP script. So if you follow the instructions in the readme, it should just work. I don't have that much experience with the program myself, but i can't think of any reason why it wouldn't.
Anyways, the best place to ask help would be at the PHPMyAdmin Help Forum
[expired user #2830]'s profile image [expired user #2830] posted 16 years ago Permalink
Hello, congratulations for a great piece of software. Using HeidiSQL on my machine works great, but connecting to a remote site fails.

I am not sure if this is a bug or not. My ISP told me it should be possible to connect to their mysql machines from outside.

However I get an "Access denied error". What's interesting is that the error message says something like "SQL Error: Access denied for user: db.mydomain.c@sql.myisp.com (Using password:YES)" BUT the username I filled in in the connection dialog in HeidiSQL is db.mydomain.com.

So I'm wondering - is the username being cut off after a certain number of characters in HeidiSQL [1], is it done by MySql [2] or is it just shortened for the error message [3]. If it's [1] I think that would count as a bug :-)


Best regards
Tom
[expired user #1821]'s profile image [expired user #1821] posted 16 years ago Permalink
Access for a user is always tied to a domain. When your grant access to a user you define this as follows:
GRANT ALL ON database.* TO user@domain IDENTIFIED BY 'password'

That's why the error message is formatted as user@domain - this is just the format that mysql uses. But, in reality everything before the @ is the username and everything after it the domain.

See also this sticky:
http://www.heidisql.com/forum/viewtopic.php?t=247

My ISP told me it should be possible to connect to their mysql machines from outside.



That's a pretty vague answer coming from people that should be able to tell you yes or no ... :?
ansgar's profile image ansgar posted 16 years ago Permalink

What's interesting is that the error message says something like "SQL Error: Access denied for user: db.mydomain.c@sql.myisp.com (Using password:YES)" BUT the username I filled in in the connection dialog in HeidiSQL is db.mydomain.com.

So I'm wondering - is the username being cut off after a certain number of characters in HeidiSQL [1], is it done by MySql [2] or is it just shortened for the error message [3]. If it's [1] I think that would count as a bug :-)



I just tested that by creating a user "db.mydomain.com" on my local mysql server which let's me connect via HeidiSQL - no problem.

Then I deleted the user from the priv tables again and - what a surprise - could no longer connect via HeidiSQL. The username in the error message is not cut. However, if I try to create a user with a longer username than 16 characters, the server replies with an errormessage

String 'db.mydomain.comdb.mydomain.com' is too long for user name (should be no longer than 16)


So I created a username with exactly 16 characters: "1234567890123456". I can connect with HeidiSQL and this username.

My guess is - as siMKin already said - that your provider has set the "skip-networking" option in the my.cnf or my.ini on his server.
jan's profile image jan posted 16 years ago Permalink
to follow up the topic, i do not recommend, to allowed any user from other than localhost. Better, i recommend you, to add the port 3306 to your firewall and deny connections.

All other information [than the password] is transferred as text, and can be read by anyone who is able to watch the connection.
http://dev.mysql.com/doc/refman/5.0/en/security-against-attack.html






but the proper solution is, to ask putty for help.

PuTTY is a free implementation of Telnet and SSH for Win32 and Unix platforms, along with an xterm terminal emulator.
http://www.chiark.greenend.org.uk/~sgtatham/putty/[/quote:18d80f8c62]
with putty, you can connect to your server with ssh (it's like an commandline). but throu this connection, you can access your mysql! but your server must allow you "shell access".

first, connect to your server:

enter your ip and "ssh" and press open. then enter your username and passwort (this is an special login for ssh, not your mysql!). if you get an command-prompt, you succeed this step. close the connection with typing "exit" and press enter, or ask your provider for help.


second step is, to show putty your mysql. enter your ip and "ssh" and then go to "connection->ssh->tunnel". enter this information and press "add":

now, you can connect to your server and log in. if you succeed, the connection is established.


step three: use your heidi and make an new connection.

important is the server and port. you have to enter the "putty", thus localhost and port 3307. with the correct mysql username and password (imagine, for the database you connect from localhost) you should succeed.

remember, that all your transfer is encrypted. so your and your servers cpu is used. also think of the bandwith of your server and your dsl.

yours
jan

ps: admin, anse, feel free to correct my english.

[expired user #2801]'s profile image [expired user #2801] posted 16 years ago Permalink
Great stuff Jan, I've been using Putty for ages for simple shell access but I didn't know about the SSH tunneling. Thanks for the writeup!
[expired user #3549]'s profile image [expired user #3549] posted 16 years ago Permalink
when ever im done typing in all the info needed i press open like im supposed to then it comes with a error msg sayin Network error: Connection timed out



can u pls help

BTW this is on puTTY
Torsten's profile image Torsten posted 16 years ago Permalink
Some MySQL frontends (MySQL Front, SQLyog) can connect to an external MySQL database via HTTP tunneling. You have to copy a certain PHP file onto your server, using an FTP client. Then the MySQL client can connect over this PHP file, because it is on the same domain like the MySQL database ("localhost").

I'd like HeidiSQL to have this functionality too, maybe in the near future.
ansgar's profile image ansgar posted 16 years ago Permalink
http://code.google.com/p/heidisql/issues/detail?id=368
http://www.heidisql.com/forum/viewtopic.php?p=2020
Torsten's profile image Torsten posted 16 years ago Permalink
Illegal, you say? But then I wonder why some commercial front-end programs feature HTTP-tunneling. In my opinion, it's easier and quicker than using phpMyAdmin on the web server.

Anyway, I respect your opinion.
ansgar's profile image ansgar posted 16 years ago Permalink
Well, to be honest there is a second reason why there is no HTTP tunnel in Heidi: It's a lot of effort and noone on the developers list who likes to implement it :)
[expired user #6278]'s profile image [expired user #6278] posted 12 years ago Permalink
Hi, I have my project hosted on a virtual dedicated server. (Linux). Followed the instructions mentioned above but no luck :(
I get SQL error (2013) Lost connection to MYSQL server at "reading initial communication packet"
[expired user #8430]'s profile image [expired user #8430] posted 9 years ago Permalink
I have a similar problem. I cannot connect to a remote server. I am sure though the external server allows remote mysql acces. On my mac I use an application much like heidisql (Sequal Pro) and that connects fine.

I als get the message "SQL Error (1045) in statement #0: Access denied for user 'p3917@095-097-025-242.static.chello.nl' (using password:YES).
The ip-like adress after the @sign is unknown to me, and it is certainly not the ip address I entered in the connect form. (I used 89.188.22.181)
kalvaro's profile image kalvaro posted 9 years ago Permalink
@gerard.vogels Just to make it clear: the IP in the error message is not supposed to be the server address at all.
[expired user #1821]'s profile image [expired user #1821] posted 9 years ago Permalink
Gerard,
When you grant access to a user, the user specification is always (explicitly or implicitly) as follows: [username]@[hostname].
So, in mysql it is possible that a username has access coming from one ip, but not from another. Same as that different users may or may not have access coming from the same ip.
This is why mysql always gives the error message in that form: you are user p3917, connecting from 095-097-025-242.static.chello.nl.

This is not a Heidi problem, it is just mysql telling you that this combination of user and ip does not have access.

If you don't care about opening your mysql database to any kind of connection you could simply grant access to p3917@% . That way it doesn't matter where you connect from.
But of course, that has security implications as well
achakrab's profile image achakrab posted 8 years ago Permalink

I was having similar issues but these commands helped me to solve the error. I'm now able to connect using HeidiSQL.

(For this solution - user=userone ; password=passwordone ; database=databaseone)

I was getting the following error:

/ SQL Error (1044): Access denied for user 'userone'@'%' to database 'databaseone' /

Here are the steps to solve the issue:

$mysql -uroot

mysql>grant all privileges ON databaseone.* to 'userone' identified by 'passwordone';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

And voila! I was able to connect and use the database named 'databaseone'.

Hope this helps.

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