Wondering how to resolve AWS RDS access denied error? We can help you.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how our Support techs assist with this query.

How to resolve AWS RDS access denied error?

Typical error looks as shown below:

ERROR 1045 (28000): Access denied for user 'root'@'10.0.4.253' (using password: YES)

Today, let us see the steps followed by our Support Techs to resolve it:

IAM authentication is disabled

By default, IAM authentication is disable. Review your configuration settings for your Amazon RDS for MySQL cluster and make sure that IAM authentication is enable.

From the Amazon RDS console, you can modify the instance by choosing Database Authentication.

Then, choose Password and IAM database authentication and Continue to update your configuration settings.

Note: If you choose Apply Immediately when updating your cluster configuration settings, all pending modifications are apply immediately.

Insufficient IAM role permissions

To successfully connect to your Amazon RDS for MySQL instance using IAM database authentication, you must have access to the rds-db:connect action.

The rds-db:connect action allows connections to the DB instance.

For example:

{
    "Version": "2012-10-17",
    "Statement": [
    {
        "Effect": "Allow",
        "Action": [
            "rds-db:connect"
        ],
        "Resource": [
            " arn:aws:rds-db:region:account-id:dbuser:(DbiResourceId or DbClusterResourceId)/db-user-name"
        ]
    }]
}

Note: Replace db-user-name with the database account user that’s associate with the IAM authentication.

Additionally, make sure that you’re using the correct resource ID (instead of only specifying the ARN).

Next to find a DB instance’s resource ID, choose the Resource tab in the AWS Management Console.

Then, choose the Configuration tab to view the resource ID.

Database user is improperly configured

With Amazon RDS for MySQL, IAM authentication is handle by AWSAuthenticationPlugin.

Therefore, to connect to your Amazon RDS for MySQL instance using IAM authentication, you must use AWSAuthenticationPlugin.

To confirm that this plugin is associate with your IAM role, run the following command:

select user,plugin,host from mysql.user where user like '%db-user-name%';

Your output will look like this:

+------+-------------------------+------+
| user | plugin | host |
+------+-------------------------+------+
| root | AWSAuthenticationPlugin | % |
+------+-------------------------+------+
1 row in set (0.00 sec)

If the IAM role is restrict to using a specific host, make sure that you’re using the correct hostname.

Also, make sure that you have proper permissions to access the specify database.

Then, to view the permissions grant to a user, use the following command syntax:

show grants for <user>;

To grant privileges to another user, use the following command syntax:

grant select on <mydb>.<mytable> to <user>;

Incorrect connection string

When you connect to the Amazon RDS for MySQL database, you must use the –enable-cleartext-plugin option in your connection string.

The –enable-cleartext-plugin syntax is used for passing the password, acting as an authentication token.

The –enable-cleartext-plugin syntax also indicates that AWSAuthenticationPlugin must use for the database connection.

The plugin is required when configuring the database user, and is require for IAM authentication to work.

If AWSAuthenticationPlugin is incorrectly configure, then IAM authentication doesn’t work.

As a result, you’ll get an Access Denied error when you try to connect to your database.

Also, because authentication tokens consist of several characters, save the token to an environment variable instead.

Then, use that variable when you connect to your MySQL DB instance. For example:

RDSHOST="rdsmysql.123456789012.us-west-2.rds.amazonaws.com"
TOKEN="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --region us-west-2 --username db-user-name)"
mysql --host=$RDSHOST --port=3306 --enable-cleartext-plugin --user=jane_doe --password=$TOKEN

If you’re getting the error `Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation` while trying to import a database dump you have done using mysqldump,

• If you’re getting the error `Access denied; you need the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation` while trying to import a database dump you have done using mysqldump, you might have encounter this issue.

You can fix the error by modifying the database dump file (.sql) which is being cause by SESSION.SQL_LOG_BIN and GLOBAL.GTID_PURGED lines. Or, you can use the force flag -f after the MySQL command. Shell

 mysql -f -u dbname -p -h hostvalue -D databasename < dbdump.sql

You will still get errors when you run the import, but it won’t prevent it from happening.

Without -f, any errors will cause the MySQL command to fail. This flag tells it to ignore the errors and keep going.

[Need help with the process? We’d be happy to assist]

Conclusion

In short, we saw how our Support Techs resolve AWS RDS access denied error