Skip navigation links

User Comments

Posted by Andrew Eigus on May 2 2003 12:19am[Delete] [Edit]

essential information for Microsoft Access MySQL users:

sometimes, when you use Microsoft Access to update tables, you get unexpected "access denied for user: '@YOUR_IP_ADDRESS'" errors, which is to be solved in the following way:

Microsoft Access 2000/2002 Jet/ODBC database engine uses default anonymous logins by default, and you should tweak the following registry key so that the ODBC DSN's options were used, instead:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]
"TryJetAuth"=dword:00000000

hope this helps,

Andrew Eigus
Astros Information Technologies - Riga

Posted by [name withheld] on May 4 2003 4:19am[Delete] [Edit]

I have a small network in my home. My RH8.something server has DHCP and DNS for my small domain (.home.tesmer.org). I do not have DNS names for the hosts on DHCP.

When trying to run MySQLCC in WinXP on a DHCP'd host, I continually got "[gummy] ERROR 2013: Lost connection to MySQL server during query" (gummy is the dns name of the host running the MySQL instance).

I added skip-name-resolve under [mysqld] in my.cnf and restarted the mysqld using the init script, and viola, it worked.

Posted by [name withheld] on July 30 2003 11:58pm[Delete] [Edit]

when you are simply trying to:
C:\mysql\bin>mysql -uroot -p mysql

and you get:

ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'

Here is what I do. The key is to supply your real ip address for the -h (host) parameter. On windows, from the command prompt type 'ipconfig' to see your ip address. Once you have that, do the following:

C:\mysql\bin>mysql -h 192.168.0.1 -u root -p mysql
Enter password: ****************

// then I explicitly add root@127.0.0.1 to the user table, so after this I can log in as you would expect

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'root-password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO root@127.0.0.1 IDENTIFIED BY 'root-password' WITH GRANT OPTION;

// delete anon accounts

DELETE FROM mysql.user WHERE password='';

FLUSH PRIVILEGES;

It works for me

One other way is when you set the initial password for the root user, also set it on 127.0.0.1:

SET PASSWORD FOR root@localhost=PASSWORD('new_password');
SET PASSWORD FOR root@127.0.0.1=PASSWORD('root-password');
FLUSH PRIVILEGES;

Posted by David Morris on December 13 2003 9:16am[Delete] [Edit]

+++SIMPLE CPANEL SOLUTION++++
If you get this error message
"Access denied for user: 'root@localhost' (Using password: YES)"

Then this may work for you.

When connecting to the MySQL server from a remote location You have to specify the "hosts"(remote location IP's) that you will be accessing the database from.

You must have access to CPANEL and have MySQL features enabled for this solution to work. A simple way to check if you have these features is to go into the CPANEL and look for the MySQL Databases Section.

If you have it listed, go into it.

Down towards the bottom of the page that loads you will see a heading called "Access Hosts".

By default you will only see "localhost" listed below this heading. This means only database access from the localhost(yourserver) is allowed.

You can add more hosts to this list by using the text box provided with the title "Host:"

For Example: if you wanted to give access to the MySQL server to a remote computer with this static IP address: 211.233.2.24 all you would simply do is type in the IP address in the textbox and hit the "add host" button.

If you had an entire network that you needed to give access to the MySQL server simply use the wildcard "%" symbol eg. 211.233.2.%

SECURITY WARNING: If you do not have a static IP address, using a wildcard "%" may open a security hole that can lead to unauthorized access to the MySQL server from a remote address. Make certain you delete all added hosts if you are not intending on using them and only set up wildcards if no one outside your network has access to the IP range you have specified.

Hope this helps - I know it did for me. :)

Posted by [name withheld] on March 2 2004 1:19am[Delete] [Edit]

I had problems connecting to MySQL over tcp/3306
on debian stable, localhost. It was not enabling networking
in my.cnf, but it was a missing entry in /etc/hosts.allow

I added the following to /etc/hosts.allow:

mysqld 127.0.0.1

I discovered the problem when trying to connect from Java
using the latest stable J/Connector. The error given
was "Communication link failure: null" which was not so
specific.

Since Java only allows you to connect over tcp, you have
no other alternative. If you experience similar problems
from Java, please test to do the following:

telnet localhost 3306

If you are connected and immediately after that disconnected
before the mysqld answers, it might be the missing entry in
hosts.allow .

You can also see if your tcp connection works by forcing
the mysql client to connect over tcp/3306:

mysql -u root -h 127.0.0.1 -P 3306 -p

The error given if you have a network problem, is:
ERROR 2013: Lost connection to MySQL server during query

If you run into this, using debian, then my advice is
to add a line accordingly to /etc/hosts.allow

The reason for this happening on debian, is that
the apt-get package for mysql-server is compiled
with tcp-wrapper support. This is not mentioned
in the file README.debian, so it may come as a surprise
to you. It did to me, to say the least.
Rikard

Posted by Dan Williams on October 23 2004 11:58pm[Delete] [Edit]

I was setting up MySQL from the instructions from the O'Reilly PHP and MySQL book and encountered "ERROR 1045: Access denied for user: 'username@localhost' (Using password: YES)". Well the instruction said to add the 'username' with the SQL statement as:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestores.* TO username@127.0.0.1 IDENTIFIED by 'password';

After doing this as root, I quit and tried to log back in with the new user. I got the ERROR 1045.

I fixed it by logging back in as root and reissuing the SQL statement again except with 'username@localhost'. It worked!

I don't know why? Maybe it is the difference between IPs of '127.0.0.1' and 'localhost'???

Hope this helps!

Posted by Ryan Carter on December 9 2004 7:01pm[Delete] [Edit]

I edited the "TryJet..." registry setting which did help, and I also found that the problem for me had to do with the ODBC driver not retaining the port# (which happened to not be 3306, the default) once the ODBC connection settings dialog was closed. I went to "HKEY_LOCAL_MACHINE\Software\Microsoft\ODBC\MyConnectionName" and added a string value for PORT with the new port number value. This has worked ever since.

Posted by Dave Pawson on January 27 2005 12:31pm[Delete] [Edit]

Moving from a windows testbed, 4.0.18 to Redhat Enterprise with
MySQL 4.1.9, I repeatedly failed to connect, getting not allowed errors,
when connecting from Tomcat 5.0.28

This was resolved when I swapped the order in /etc/hosts
for the localhost.

Now reads
127.0.0.1 localhost localhost.localdomain

Posted by Johannes Ullrich on February 15 2005 3:42pm[Delete] [Edit]

I narrowed this down to the 'wait_timeout' setting. Interestingly, if I do a 'show variables' , the wait_timeout does always show up as 28800 seconds, even if it is setup differently using 'set global' or the respective setting in my.cnf.

Little test script to see if this is the issue:
use DBI;
$dbh=DBI->connect("DBI:mysql...(insert your paramters)");
$sth=$dbh->prepare("select now()");
$sth->execute();
($start)=$sth->fetchrow_array();
$sth->execute;
($now)=$sth->fetchrow_array();
print "$start $now\n";
sleep 70;
$sth->execute;
($now)=$sth->fetchrow_array();
print "$start $now\n";

just keep changing the number of seconds in the 'sleep' line, and see at what point the error comes up.

Posted by Warwick Hehir on January 14 2006 6:20am[Delete] [Edit]

There has been several posts on
Essential information for Microsoft Access MySQL users:
ERROR: "access denied for user: '@YOUR_IP_ADDRESS'" errors, which is to be solved in the following way:
WITH SOLUTION:
Microsoft Access 2000/2002 Jet/ODBC database engine uses default anonymous logins by default, and you should tweak the following registry key so that the ODBC DSN's options were used, instead:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]
"TryJetAuth"=dword:00000000

OTHER OPTIONS - PEOPLE THOUGHTS
I have another option and wondering if others could test as well please

Put in the code of the form before doing a database connection the following
DBEngine.SetOption "TryJetAuth", 0

Based on the Setoption command format:
DBEngine.SetOption parameter, newvalue

Cheers

W

Posted by Ajaz Ahmed on June 23 2006 7:40am[Delete] [Edit]

By the way ..... although many /or all of the above comments are valid ...

It is also important to note that the host_name (used in GRANT PRIVILEGES) does NOT USE THE CIDR (/24, /29 etc...) notation for specifying subnets !!! - therefore you must use the full 255.255.255.x subnet notation for specifying host ip addresses !!!

Took me a alot of time and digging to figure this one out ! and this was all it was to get remote connections working - even after making sure all the other points mentioned above were correct and in-place !

Posted by Tim Hecktor on October 25 2006 10:49am[Delete] [Edit]

If you have Problems connecting to an MySQL Server running on a different port (or socket), this may be helpful:

I ran into Problems connecting to a MySQL5 server i have setup on port 3307 in addition to a MySQL4 Server running on 3306.

kimiko:~ # mysql -P3307 -ptest
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This is not possible, as the client always uses a socket to connect to "localhost" (which is the default host). For a sucessful connection either the combination of localhost + socket or 127.0.0.1 + port must be specified.

kimiko:~ # mysql -h127.0.0.1 -P3307 -ptest
kimiko:~ # mysql -S/path/to/mysql.sock -ptest

Posted by Matthew Schroebel on October 28 2006 3:23pm[Delete] [Edit]

If you are having trouble trying to connect to a remote server, then make sure your reverse lookup of the IP address matches the hostname on the machine you are connecting from.

If your hostname on the local machine is my.domain.com and has ip of 200.1.1.0, then check the reverse lookup on the remote server of 200.1.1.0. If the name doesn't match my.domain.com, then add a host table entry to resolve the ip to my.domain.com.

The hint that this is a problem, is if you try to connect from my.domain.com, and the access denied message returns a different host name.

Don't be tempted to change host names you're using on the grant statements to match the value the reverse lookup returns on the remote server. It won't work since the forward lookup name is being provided to the remote server.

Posted by adam b on December 11 2006 4:24pm[Delete] [Edit]

If you're experiencing the error:
ERROR 1045 (28000): Access denied for user 'some_user'@'ip_addr_of_remote_client' (using password: YES)

when you're trying to connect to mysql via a remote client, this thread is helpful:

http://www.snort.org/archive-5-1686.html

Even after you "grant" privileges for specific IP addrs, mysql may still lock you out. The solution this guy found - and also worked for me - is to reset the password for your new user/ip entries with the following command:

mysql>SET PASSWORD FOR some_user@ip_addr_of_remote_client=PASSWORD('some_password');

I performed this when logged into mysql as root. It reset the hash for the password and all my remote connections started "magically" working.

Posted by briosky N/A on February 21 2007 11:00am[Delete] [Edit]

ERROR 1045 (28000): Access denied for user
'root'@'localhost'
and/or
'ODBC'@'localhost'

On my box with XP pro sp2 (behind a router/NAT) the following worked:
in /windows/system32/drivers/etc/hosts
over than 127.0.0.1 localhost
add:
0.0.0.0 localhost
or
255.255.255.255 localhost

best wishes

Posted by Dave Stoddard on May 24 2008 3:41pm[Delete] [Edit]

I recently upgraded my OS from FreeBSD 5.5 to FreeBSD 7.0, and upgraded MySQL from 5.0.12 to 5.0.51a. I have a batch job in /etc/crontab that runs mysqlcheck each night to verify the database. The mysqlcheck program uses a userid and password provided through a .my.cnf file under the user account that runs the job. After the upgrade, I started getting the following error message:

/usr/local/bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

The problem was complicated by the fact that mysqlcheck runs just fine at the command prompt, but as a cronjob running under the same user it failed with the error message above.

After digging around, I found a new option that must be specified on the command line to get mysqlcheck to use the .my.cnf file - the option is "--defaults-file=/root/.my.cnf". Without this, the .my.cnf file will not be read by mysqlcheck.

If you are looking for a handy line to drop in /etc/crontab to check your database for consistency every night on FreeBSD, try the following:

30 4 * * * root /usr/local/bin/mysqlcheck --defaults-file=/root/.my.cnf --all-databases --auto-repair 2>&1 | grep -v "OK" | mail -E -s "Database Problem" root

This cronjob will only notify you when there is a problem, which keeps the email "signal to noise ratio" down when you are running several servers.

Dave

Posted by Mustali Kachwala on March 24 2011 11:40am[Delete] [Edit]

ERROR 1045 (28000): Access denied for user

If you get following error while executing a OUTFILE query

mysql> select field1,count(*) into outfile '/tmp/temp.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from table group by field1;
ERROR 1045 (28000): Access denied for user 'test'@'10.0.3.%' (using password: YES)

then the most probable cause reason is that the entry for the username used by you does not have the FILE privilege enabled in the user table.

Read Full Details @ http://mustalikachwala.blogspot.com/2011/03/mysql-error-1045-28000-access-denied.html

Posted by Tom Andresen on November 21 2013 9:57pm[Delete] [Edit]

nmap is also a useful tool when you can't connect to a remote host via MySQL.

bash> nmap -P0 your_host

Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2013-11-21 15:59 EST
Interesting ports on your_host (your_host):
Not shown: 1679 filtered ports
PORT STATE SERVICE
22/tcp open ssh

Nmap finished: 1 IP address (1 host up) scanned in 29.926 seconds

~~

This showed me that only port 22 was open and that was why I could not connect.