RDS MySQL ERROR 1045 (28000): Access denied for user when connecting to db from EC2

Question

I have an EC2 instance that I am trying to connect to an RDS (mySQL) instance.

I created an RDS mySQL instance with the name: mydbinstance, user: mysuperuser and a generic 8 character (mypassword) just for testing purpose.

Virtual Private Cloud: default (same as where the EC2 is located)

Subnet group: default

Public accessibility: No (DB instance will not have a public IP address assigned. No EC2 instance or devices outside of the VPC will be able to connect.)

Availability zone: No preference

VPC security groups: Create new VPC security group

IAM DB authentication: Disabled

I added the RDS instance to the same VPC as the EC2 instance, so it's in the same zone.

The dbinstance is using a new security group:

Type: Custom TCP Rule Protocol: TCP Port: 3306 CIDR: 174.62.127.135/32

Where does this IP address comes from? I didn't specify anything at instantiation...

Next, I ssh to my EC2 instance (the one located in the same VPC), and try:

$ mysql -h mydbinstance.$(RDS_INSTANCE).us-west-2.rds.amazonaws.com -P 3306 -u mymasteruser -p

I entered mypassword (the one I specified earlier) at the prompt and I get:

ERROR 1045 (28000): Access denied for user 'mymasteruser'@'localhost' (using password: YES)

I tried getting the IP address of the EC2 box with ifconfig, and updated the security group IP address (the one I was wondering about above) used by RDS with it. Same result, no luck connecting. I've been googling for an answer since then.

What are my other options to debug this? I would appreciate any suggestions.

UPDATE

I was able to run this from my laptop (by changing the DB Public Access to YES):

mysql -h mydbinstance.$(RDS_INSTANCE).us-west-2.rds.amazonaws.com -P 3306 -u mymasteruser -p

found 0 associations found 1 connections:

found 0 associations
found 1 connections:
     1: flags=82<CONNECTED,PREFERRED>
    outif en3
    src 192.168.86.33 port 55863
    dst xx.xxx.xxx.xxx port 3306
    rank info not available
    TCP aux info available

But I am unable to get any output from my EC2 instance. It hangs! Same why I run netcat (nc) command. Why is this not working on my EC2 instance?

Solution

I was able to make it work after I found the nugget on the aws forum. Posting it back here in case someone stumble on the same issue:

"In order to connect to MySQL, you will have to authorize the IP address of the host from which you plan to connect to MySQL. You should also revoke access from the CIDR you currently have authorized. If you don’t know your computer’s IP address, you can see it by visiting the site http://checkip.amazonaws.com/. Note that you will have to add “/32” to the end of this to convert the IP address to a CIDR which contains only that IP address. For example, if your IP address is 192.0.2.10, you should authorize access to the CIDR 192.0.2.10/32.

Comments

[Juan Jimenez]

This answer isn't clear enough. Authorize the ip address of the host.. where? Revoke access... where?

[Anurag Chutani]

In "Inbound rules" for "Security group", which is linked to your RDS instance.

[Leon]

^ the above setting can be found in EC2 > security groups. NOT RDS ... confusingly!

[Jimbo]

In RDS there is current a "connectivity and security" tab. Click to edit the "VPC security group" the click on the link under the "security group ID" column you can then "edit inbound rules"

[Kishor Pawar]

What if the host doesn't have static public IP?

[avepr]

This is not a good answer and does not suit most of the cloud application setup. Given most basic configuration: an autoscaling group of EC2 instances running a backend application that needs access to a DB. Each instance has its own IP and as the group scales out/in, the IPs will change. So the correct way should be to set the DB security group inbound rules to allow inbound traffic from the security group associated with the auto-scaling group.