fix/mysql/rds-access-denied-for-user
MySQL error

Access denied for user

Updated Oct 12, 20263-min read
TL;DR

MySQL and MariaDB on Amazon RDS reject the login with error 1045, or 1698 on MariaDB, before any database is opened. On RDS the cause is usually a master password that Secrets Manager rotated, an IAM auth token the server didn't accept, or a user that doesn't match the host you connect from.

  • ·The master password changed: RDS-managed passwords in Secrets Manager rotate every 7 days by default
  • ·An IAM token was signed for the wrong region, host or port, or is older than 15 minutes
  • ·The user wasn't created with AWSAuthenticationPlugin, so the server never accepts a token
  • ·The account is defined for a different host than the one you connect from
  • ·Login works but the user has no grant on the database (error 1044)
CHECK FIRSTLook at the end of the message. "using password: YES" means a password or token was sent and rejected. "using password: NO" means the client sent nothing the server could use, which points to the wrong login method for that user.

What you're seeing

The error appears right after the TLS handshake, in the mysql client, an app driver or a GUI. The host part is the address RDS sees, which is your public IP or the bastion's private IP when you tunnel.

text
ERROR 1045 (28000): Access denied for user 'admin'@'203.0.113.10' (using password: YES)

ERROR 1045 (28000): Access denied for user 'iam_user'@'203.0.113.10' (using password: NO)

ERROR 1698 (28000): Access denied for user 'iam_user'@'203.0.113.10'

ERROR 1044 (42000): Access denied for user 'iam_user'@'%' to database 'appdb'
Also seen as: rds access denied for user, rds access denied for user using password yes, rds mysql access denied for user, aws rds access denied for user, rds iam authentication access denied for user

What's causing this

Ranked most-likely first.

  1. 1

    The master password was rotated

    If the instance manages its master password in Secrets Manager, RDS rotates it on a schedule. A password copied into a client weeks ago stops working without any change on your side.

  2. 2

    The IAM token was rejected

    Tokens must be signed for the RDS endpoint, its real port and its region, and used within 15 minutes. The IAM policy must allow rds-db:connect for the user on the instance's resource ID.

  3. 3

    The user isn't set up for IAM

    A user created with a normal password asks for that password, so a token is never even sent and you see "using password: NO". IAM users need the AWSAuthenticationPlugin.

  4. 4

    The user's host doesn't match

    MySQL accounts are user plus host. 'app'@'10.0.%' won't match a login from your laptop, and a tunnel makes the login come from the bastion's address instead of yours.

  5. 5

    Missing grants on the database

    Error 1044 means the login worked but the user has no privileges on the database it tried to open.

How to fix it

Step 1: Get the current master password

For an RDS-managed master password, read it from Secrets Manager instead of using a saved copy. Otherwise reset it.

bash
aws rds describe-db-instances --db-instance-identifier mydb \
  --query 'DBInstances[0].MasterUserSecret.SecretArn'

aws secretsmanager get-secret-value --secret-id '<secret arn>' \
  --query SecretString --output text

# Not managed by Secrets Manager: reset it
aws rds modify-db-instance --db-instance-identifier mydb \
  --master-user-password 'new-password' --apply-immediately

Step 2: Check how the user logs in and from where

Connect as the master user and look at the plugin and host for the account.

sql
SELECT user, host, plugin FROM mysql.user WHERE user = 'iam_user';

Step 3: Create the IAM user correctly

Use the MySQL form on RDS MySQL and Aurora MySQL, and the MariaDB form on RDS MariaDB. Require TLS, since IAM logins only work over TLS.

sql
-- MySQL and Aurora MySQL
CREATE USER 'iam_user'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS' REQUIRE SSL;

-- MariaDB
CREATE USER 'iam_user'@'%' IDENTIFIED VIA AWSAuthenticationPlugin USING 'RDS';
ALTER USER 'iam_user'@'%' REQUIRE SSL;

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'iam_user'@'%';

Step 4: Sign a fresh token and log in

Sign for the RDS endpoint, its real port and region. The mysql client only sends the token if the cleartext plugin is enabled.

bash
TOKEN=$(aws rds generate-db-auth-token \
  --hostname mydb.abc123xyz.us-east-1.rds.amazonaws.com \
  --port 3306 --region us-east-1 --username iam_user)

mysql --host=mydb.abc123xyz.us-east-1.rds.amazonaws.com --port=3306 \
  --ssl-ca=global-bundle.pem --ssl-mode=VERIFY_IDENTITY \
  --enable-cleartext-plugin --user=iam_user --password="$TOKEN"

Step 5: Check the IAM side

Confirm which identity you are using and that its policy allows rds-db:connect on dbuser:db-XXXX/iam_user, where db-XXXX is the instance's resource ID (cluster-XXXX for Aurora).

bash
aws sts get-caller-identity
aws rds describe-db-instances --db-instance-identifier mydb \
  --query 'DBInstances[0].DbiResourceId'

Prevention

Don't copy RDS-managed master passwords into tools. Have the client read the secret at connect time, so rotation never breaks a saved connection.

For people, prefer IAM logins over shared passwords, with a token generated for every new connection, and create accounts for '%' or the exact network you connect from.

Debug MySQL faster

1bench is a native GUI for MySQL. Inspect queries, connections, and settings without leaving the app. See what's happening before you have to Google it.

Open MySQL in 1bench