Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL ERROR 1045 (28000) "Access denied for user": which account the server matched

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

ERROR 1045 (28000): Access denied for user 'report'@'192.0.2.10' (using password: YES) means the server received the connection and refused the login. The message tells you three things: the user name, the host the server saw the connection come from, and whether a password was sent. The causes to check are a wrong password, no account for that user and host, and a different account matching first. Establish which before you change a password.

What the error means

The MySQL 8.4 error reference lists error 1045, symbol ER_ACCESS_DENIED_ERROR, SQLSTATE 28000, with the message text Access denied for user '%s'@'%s' (using password: %s). The manual's troubleshooting page says: "The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password." It adds that using password: NO means you tried to log in without a password.

An account is a user name plus a host. The manual says account names take the form 'user_name'@'host_name', that user name comparisons are case-sensitive and host comparisons are not. On connection, the server checks the user table in the mysql database. A connection is accepted only if "The client host name and user name match the Host and User columns in some user table row", the client supplies the credentials in that row, and "The row indicates that the account is unlocked".

When several rows could match, the server sorts the rows and uses the first match. The manual states the rule that surprises people: the most specific Host values sort first, and "for a given user name, all rows that explicitly name that user" are not tried first. A row with a blank user name (an anonymous account) that matches the host can match before the named user's row. The manual's example is a user jeffrey connecting from h1.example.net who is "authenticated as an anonymous user, even though he specified a user name when connecting", because the row with no user name sorts first.

Other errors look similar and need different fixes. The error reference lists Access denied for user '%s'@'%s'. Account is locked. (error 3118), Your password has expired. To log in you must change it using a client that supports expired passwords. (error 1862), and Host '%s' is not allowed to connect to this MySQL server (error 1130). The troubleshooting page says the last of these means there is no user table row whose Host value matches the client host.

Likely causes

Cause What you see How to confirm
Wrong password using password: YES Connect with --no-defaults and a password typed at the prompt
No password sent using password: NO The client was started without -p, or the option file has no password
Old password in an option file Works at one prompt, fails from a script or tool The manual: check option files; run the client with --no-defaults
Account exists for another host The host in the message is not one the account allows Read Host for the user in mysql.user
Anonymous account matches first The connected account is ''@'host' (blank user) SELECT CURRENT_USER(); after a successful connection shows @host
Host name did not resolve The message shows 'root'@'' or ''@'unknown' The manual says 'root'@'' indicates a DNS problem, and says of ''@'unknown' that mysqld cannot resolve the client's host name
Account locked The Account is locked. message account_locked column is Y
Plugin mismatch (MySQL 8.4 disables mysql_native_password by default) Account uses mysql_native_password, or the client cannot do the plugin plugin column in mysql.user

Prove the cause before changing anything

All of these are read-only.

1. Read the three parts of the message. Note the user, the host and the password flag exactly. The host is the one the server saw, which may be an IP address or a resolved name.

2. Rule out option files and environment. The manual says: "Remember that client programs use connection parameters specified in option files or environment variables." and advises running the client with --no-defaults to suppress option files. Then type the password at the prompt:

mysql --no-defaults -h db01 -u report -p

The connecting page's examples use -h, -u and -p; with -p and no value, the client prompts for the password, which the page says is more secure than giving the password on the command line.

3. Read the accounts as the server holds them. As an administrator:

SELECT user, host, plugin, account_locked FROM mysql.user WHERE user IN ('report', '');

The manual names the User, Host, plugin and account_locked columns. Include the blank user ('') in the filter, because an anonymous row can shadow the named one. Compare the rows against the host in the message, applying the sort rule above: literal addresses and host names are most specific, then ranges, then %.

4. If you can connect, see which account you got. The manual says to use CURRENT_USER(), which returns the User and Host from the matching row, and compares it with USER(), which "The user name and host name provided by the client". A result such as @localhost means the server treated you as anonymous.

5. Check name resolution. The manual says a message with an empty host ('root'@'') means the server could not resolve the client address, and lists fixes: repair DNS, use IP addresses in the grant tables, or add the client to /etc/hosts. It says mysqladmin flush-hosts resets the host cache for this kind of fault.

Fix

Choose the fix that matches the proof. Do not reset passwords as a first step.

Wrong or missing password

Correct the password the client sends (application settings, option file). If the account's password is genuinely unknown, save first: run the SELECT in step 3 for the exact account and keep the output (plugin, lock state and host). Disruptive step: changing the password breaks every client still using the old one, and the old password cannot be recovered from the server. Then set a new one. The manual's syntax allows IDENTIFIED BY 'auth_string', and its examples use IDENTIFIED BY 'new_password' (one adds PASSWORD EXPIRE, which is left out here):

ALTER USER 'report'@'192.0.2.10' IDENTIFIED BY 'new_password';

Use the exact user and host values the SELECT returned, not a guess. To back out, run the same statement with the previous password, if the clients still hold it.

No account for that user and host

Save first: run the SELECT in step 3 and keep its output, so you can see that no row existed. Disruptive step: creating the account is a new way into the server. Limit the host part, avoid % where you can, and grant only the privileges needed (privileges are not covered here). Then create the account, with the exact host the server saw. The manual's form is CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';. The manual also says that if you do not know the client's address you can use %, then "change the '%' in the user table row to the actual host name that shows up in the log", because otherwise your system "is left insecure because it permits connections from any host for the given user name". To back out, drop the account you created (DROP USER with the exact name you created).

Anonymous account shadowing

Do not drop accounts to test a theory. Confirm with CURRENT_USER() first, then decide with the owner of the server whether the anonymous row is needed. Risk: dropping an account removes its access for everything that uses it, and the grants are lost with it. Record the account's grants (SHOW GRANTS FOR 'user'@'host') before any such step.

Locked account

Run the SELECT in step 3 and keep its output, which shows the account's current lock state. The manual's statement is ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;. Disruptive step: unlocking restores access for an account that was locked deliberately or by failed-login tracking. Find out why it was locked first. To back out, run ACCOUNT LOCK for the same account.

Plugin mismatch on MySQL 8.4

The manual says mysql_native_password is "disabled by default in MySQL 8.4" and that 8.4 clients use caching_sha2_password by default. Save first: run the SELECT in step 3 for the account and keep the output (plugin and host). Disruptive step: moving the account to the default plugin changes the account's plugin and password for every client that uses it. Check that the client library supports it before changing it. Moving the account to the default plugin is the documented direction; the ALTER USER syntax in the manual allows ALTER USER 'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password'; (the manual's own example continues with further options after this clause, which you do not need). To back out, run ALTER USER ... IDENTIFIED WITH mysql_native_password BY 'old_password'; only if the server still allows that plugin. It is disabled by default in 8.4, so on a default server that back-out is not available; plan the change with that in mind and update the clients instead.

Lost administrator password

The manual has a procedure for resetting the root password on Unix, using a file named by --init-file. Save first: run the SELECT in step 3 for the root account and keep its output. Disruptive step: the procedure involves stopping the server, which interrupts every user, and writing a password into a file that the manual says to delete after the server has started. The old root password cannot be restored from the server, so record the new one before you finish. Follow the manual's page for your platform exactly, and schedule it. Do not use --skip-grant-tables on a server reachable by users: the manual says it lets anyone connect without a password and with all privileges.

Example (illustrative): On app01, a reporting script gets ERROR 1045 (28000): Access denied for user 'report'@'192.0.2.10' (using password: YES) while the same login works by hand from the same host. The script's tool reads a password from an option file in the service account's home directory, left from before a password change. Running the tool with --no-defaults and a typed password connects. The administrator updates the option file. No server change was needed.

Confirm the fix

  1. The client connects with the intended user and host.
  2. SELECT CURRENT_USER(); shows the account you intended, not an anonymous one.
  3. The application's own connection succeeds and its privileges work.
  4. Other clients of the same account still connect, or have been updated.

What to watch afterwards

  • Keep a list of which applications use which account, and from which hosts.
  • Use host patterns as narrow as the network allows.
  • Alert on repeated access-denied lines in the server error log.
  • Do not store passwords in option files that nobody owns.

Risks and limits

  • These commands have not been run or lab-tested. They come from the MySQL 8.4 manual.
  • MySQL 8.4 only. MariaDB is not covered.
  • The client message for an account that uses a plugin the server has not loaded is not covered.
  • Privilege grants, TLS requirements and proxy users are not covered.

Sources