How do I troubleshoot access errors that I receive when I try to connect to my Amazon RDS or Amazon Aurora MySQL DB cluster?

Short description

The Access denied error or other access issues might occur for the following reasons:

• Username or password not valid
• The DB instance isn't in the AVAILABLE state
• The connection tried is using the wrong host
• The user exists, but the host doesn't have the correct permissions to connect or grant access to another user
• The client doesn't use the required SSL
• Duplicate users exist with the same username
• Special characters in the password are being converted by Bash
• A connection packet doesn't contain the correct information
• The max_allowed_packet parameter value is too small
• AWS Identity and Access Management (IAM) authentication issues
• Amazon RDS Proxy issues
• Kerberos authentication issues
• Lack of permissions
• AWS Lambda function configuration issues
• A database user is locked
• The application code is fetching the wrong password
• Issues when you use the SELECT INTO OUTFILE Amazon Simple Storage Service (Amazon S3) option

To authenticate to your Amazon Aurora MySQL DB cluster, complete one of the following options:

• Access Control and Account Management on the MySQL website.
• IAM database authentication
• Connect to a database through Amazon RDS Proxy
• Use Kerberos authentication for Amazon Aurora MySQL

For more information, see Access denied on the MySQL website.

Resolution

Note: If you receive errors when running AWS Command Line Interface (AWS CLI) commands, confirm that you're running a recent version of the AWS CLI.

Note: Make sure to review all commands and replace all instances of example strings with your required values.

Username or password not valid

Incorrect username

1.    Check whether the user exists:

mysql> SELECT user FROM mysql.user WHERE User='username';

2.    If the user doesn't exist, then create a new user:

mysql> CREATE USER 'example-new-username'@'%' IDENTIFIED BY 'example-new-password-here'; 
mysql> FLUSH PRIVILEGES;

For more information, see Master user account privileges.

Incorrect password

Make sure that you enter the correct username and password when you connect to the database. Don't use special characters in your password.

If you use a non-primary user account and you forgot the password, use the Amazon RDS primary user to reset the password. If you forgot the Amazon RDS primary user password, see How do I reset the admin user password for my Amazon RDS DB instance?

Reset the non-primary user password:

ALTER USER 'example-username'@'example-localhost' IDENTIFIED BY 'example-new-password';

Note: By default, log_error_verbosity is set to 2 in Amazon RDS. Make sure to validate errors and warnings that are logged by Amazon RDS to gain insight on connection failures because of authentication.

The DB instance isn't in the AVAILABLE state

Confirm that your DB instance is currently in the AVAILABLE state by reviewing these topics:

• For Amazon RDS, see View Amazon RDS DB instance status.
• For Amazon Aurora, see View DB instance status in an Aurora cluster.

For more information, see How do I resolve problems when I connect to my Amazon RDS DB instance?

The connection tried is using the wrong host

To make sure that you don't connect to your DB instance with the wrong host, complete the following steps:

• Specify the correct host with -h flag and port.
• Review the Instance page on the Amazon RDS console, and then check your hostname and port.
• Make sure that you don't try to connect with your DB instance IP address. IP addresses can change due to instance stop and start, host replacement, failovers, or upgrades.

The user exists, but the host doesn't have the correct permissions to connect or grant access to another user

1.    Identify the host user that MySQL allows connections from:

 mysql> SELECT host, user FROM mysql.user WHERE User='username';

2.    Modify the existing user to allow a new IP address:

RENAME USER example-current-user@example-ipaddress TO example-new-user@example-ipaddress;

3.    To create a new user with the correct host or with the correct % to match any IP addresses, run this command:

mysql> CREATE USER 'example-db-user'@'%' IDENTIFIED BY 'example-new-password';

The client doesn't use the required SSL

To check whether there are users that require SSL when connecting, run this command:

mysql> SELECT * FROM mysql.user WHERE ssl_type <> '';

If a user requires SSL, then they must connect with SSL. For more information, see Use SSL/TLS to encrypt a connection to a DB cluster and Connect from the MySQL command-line client with SSL/TLS (encrypted).

Duplicate users exist with the same username

If there is another user with the same username, then your connection is denied.

Check whether there are users with the same username:

mysql> SELECT host, user FROM mysql.user WHERE User='example-username';

If there are users with the same username, then complete these steps:

1. Log in as a different user or as a user that has permissions to drop other users.
2. Drop the duplicate username.
3. Connect with the user that was denied because of a duplicate username.

For more information, see How do I create another admin user for my Amazon RDS DB instance that is running MySQL?

Special characters in the password are converted by Bash

If special characters in your password are interpreted by Bash, then wrap your password in single quotes.

A connection packet doesn't contain the correct information

To obtain a connection packet, you must have more than the connect_timeout parameter value (seconds). Contact your DBA, and then adjust the value of the DB parameter based on your queries and workload. For more information, see connect_timeout on the MySQL website.

The max_allowed_packet parameter value is too small

The max_allowed_packet parameter has a low value by default. This allows the value to catch large and incorrect packets. If you use large BLOB columns or long strings, then increase the max_allowed_packet parameter value. For more information, see max_allowed_packet on the MySQL website and Best practices for configuring parameters for Amazon RDS for MySQL.

IAM authentication issues

To troubleshoot your connection issues related to IAM authentication, complete these steps:

• To connect to an instance, see How do I allow users to authenticate to an Amazon RDS for MySQL DB instance with their IAM credentials?
• To troubleshoot errors, see Why do I get an Access Denied error when I try to connect to Amazon RDS for MySQL with IAM authentication?
• To retrieve information on a debugging error message, run the following command:

$ TOKEN="$(aws rds generate-db-auth-token --hostname <example-endpoint> --port 3306 --username <example-username> --region <example-region-debug)"

Amazon RDS Proxy issues

To troubleshoot your connection issues related to Amazon RDS Proxy, complete these steps:

• To connect, see How do I connect to my Amazon RDS MySQL DB instance or Aurora MySQL DB cluster with Amazon RDS Proxy?
• If you can't connect, then see Why can't I connect to my Amazon RDS DB or Amazon Aurora DB instance with RDS Proxy?

Note: To log more details on your connection failure, use Enhanced Logging until your issue is further isolated. Also, connections from Amazon RDS Proxy are allowed within the Amazon Virtual Private Cloud (Amazon VPC) network. Non-Amazon VPC connections are blocked.

Kerberos authentication issues

To set up Kerberos authentication for Amazon Aurora MySQL DB clusters, see Set up Kerberos authentication for Aurora MySQL DB clusters.

Lack of permissions

Before you grant supported permissions, the database user that performs the grant command must have the requested permissions and grant option. If you get access denied errors as the Amazon RDS primary user, reset your password and then retry the command.

Note: There is no downtime to reset the primary user password.

To view a user's permissions, run this command:

SHOW GRANTS for username;

For a list of supported Amazon Aurora MySQL permissions, see Master user account privileges.

AWS Lambda function configuration issues

To troubleshoot issues that you have with your AWS Lambda function configuration, see How do I configure a Lambda function to connect to an RDS instance?

A database user is locked

When a database user is locked, you might get this error:

"Error:[3118] Access denied for user 'example-username'@'example-localhost'. Account is locked"

To resolve this error, complete these steps:

1.    Unlock the account from the Amazon RDS primary user account:

ALTER USER 'example-db-username'@'example-%' ACCOUNT UNLOCK;

2.    Check whether the user account is still locked:

SELECT user, host, account_locked FROM mysql.user where user = 'example-username';

The application code is fetching the wrong password

The application code fetches user and password from values stored in variables and might fail due to misconfiguration. To validate the connection failure, manually pass the username and password to connect from another MySQL client tool. Then, check the connection status.

Issues when you use the SELECT INTO OUTFILE S3 option

The SELECT INTO OUTFILE S3 option requires settings such as your IAM policy, cluster parameter group values, and outbound security group rules to be set correctly. Incorrect settings might lead to an access denied error. For more information, see Save data from an Amazon Aurora MySQL DB cluster into text files in an Amazon S3 bucket.

Related information

Connect to an Amazon Aurora MySQL DB cluster

IAM database authentication for MariaDB, MySQL, and PostgreSQL

Work with parameter groups

How can I troubleshoot connectivity to an Amazon RDS DB instance that uses a public or private subnet of a VPC?