AWS: can't connect to RDS database from my machine

Question

The EC2 instance/live web can connect just fine to the RDS database. But when I want to debug the code in my local machine, I can't connect to the database and got this error:

OperationalError: (2003, "Can't connect to MySQL server on 'aa9jliuygesv4w.c03i1 ck3o0us.us-east-1.rds.amazonaws.com' (10060)")

I've added .pem and .ppk keys to .ssh and I already configure EB CLI. I don't know what should I do anymore.

FYI: The app is in Django

I guess AWS has an official support channel where people are paid to answer questions like yours. No matter how much I would like to help you, it is very hard to reproduce the problem without access to your AWS account - this makes the question a bit off-topic here. — Paulo Scardine, May 13 '16 at 14:49
check your security group - when creating your DB did you select `Publicly Accessible` to yes ? — Frederic Henri, May 13 '16 at 15:07

score 266 · Accepted Answer · answered May 13 '16 at 19:39

266

It turns out it is not that hard. Do these steps:

Go to EC2 Dashboard
Go to Security Groups tab
Select and only select the RDS database security group. You'll see the security group detail at the bottom
Click Inbound tab
Click Edit button
Add Type:MYSQL/Aurora;Protocol:TCP;Range:3306;Source:0.0.0.0/0

answered May 13 '16 at 19:39

Aminah Nuraini

18,120
8
90
108

51

Opening up your MySQL instance to 0.0.0.0/0 (everyone?) is a security risk though? – kafka Jan 10 '18 at 10:05
1

This worked for me ! But can you explain if this is the standard way of allowing outside access to RDS ? – Raheel Feb 28 '18 at 16:44
1

In my case I only had to add the "Private IP" of the EC2 instance – Nil Llisterri Mar 05 '18 at 20:18
5

@kafka it's fine if you are debugging on a test database, otherwise you probably want to restrict the IP range to a trusted IP (say, that of your company). In that case, you would write X.X.X.X/32, to only allow one IP address. Side note: Amazon RDS wizard seems to be setting the rule to your "current IP" when creating the DB security group. I realised this now as I came back home and the test db connection I was playing with in the office stopped working. – coconup Mar 08 '18 at 19:34
This worked for me. Thanks. As @coconup mentioned, now RDS has "My IP" option at source when you create a rule. – Quan VO Jul 26 '18 at 22:06
i had ticked the box to make a public ip in a hurry moving a live service over during an outage so didn't read the small print. This answer was a life saver.... – simbo1905 May 02 '19 at 17:51
The two answers to this question are required to expose the DB instance publicly. It does not suffice with one thereof I think they should be combined. I'd go ahead and edit the accepted answer but I'd like some backers first. Or, even better, @Aminah Nuraini does it for me. – Mauro Oct 18 '19 at 19:29
You might want to add another rule for UDP too "MYSQL/Aurora;Protocol:UDP;Range:3306;Source:0.0.0.0/0" depending on how you connect to it. – randomness2077 Jan 15 '20 at 07:57
Now there is a button along the lines of "Add my own IP" and it will allow connections only from your machine – Jun 07 '21 at 09:34
Thank you for this post, I lost hours looking for solution and finally found yours. – Filip Górny Nov 13 '21 at 17:54
Worked for me! Thanks – Wanderson Xesquevixos de Sique Jul 21 '23 at 23:07
I also added the same thing into my security groups, But I can't be able to connect it from my local machine. Could anyone help me to fix it? – Yellow Digital Labs Aug 22 '23 at 11:34

Anthony R · Answer 2 · 2020-01-20T14:31:08.830

136

MAKE SURE PUBLIC ACCESSIBILITY IS SET TO YES

This is what I spent the last 3 days trying to solve...

Instructions to change Public Accessibility

edited Jan 20 '20 at 14:31

answered Aug 15 '19 at 01:58

Anthony R

2,739
1
12
11

7

And then you can add a security group with the IP of your web server and your developer access ips. – kintsukuroi Aug 28 '19 at 22:27
if i make that will people still need a password? or anybody can access? – Kailegh Oct 08 '19 at 15:51
They will still need both your credentials and the downloaded key to ssh into your db. – Anthony R Oct 08 '19 at 17:28
where can I find this setting? – danielrvt Jan 10 '20 at 16:52
... in doing so you can skip the VPC assignment (first point in the instructions link) – spakendralo man Sep 24 '20 at 20:46
Note: Serverless instances CANNOT be configured to be public, but don't notify you of that fact. – fastmultiplication May 23 '21 at 01:52
If you allowed access to your (or any) IP in a security group inbound rule BEFORE setting public accessibility to yes, it won't work. You have to edit the rule and save it again, just AFTER you allow public accessibility. – jbn1981 Feb 22 '22 at 18:55
`You have to edit the rule and save it again` Unreal – ahhhhhhhhhhhhhhhhhhhhhhhhhhhhh May 14 '23 at 16:34

score 28 · Answer 3 · answered Apr 27 '21 at 22:21

28

Accept traffic from any IP address

After creating an RDS instance my security group inbound rule was set to a specific IP address. I had to edit inbound rules to allow access from any IP address.

"Security group rules"
Select a security group
Click "Inbound Rules"
Click "Edit Inbound Rules"
Under "Source" Select the Dropdown and click "Anywhere"
::0 or 0.0.0.0/0 Should appear.
Click "Save Rules"

answered Apr 27 '21 at 22:21

Caleb Gates

914
7
20

I added both and it is working now ;) – Aguid Nov 30 '21 at 21:08
This is the correct answer if you've set it to public access and expecting it to just work, it still requires adding the rule to the inbound table – Joel Davey Jan 09 '23 at 10:50

score 15 · Answer 4 · answered Apr 10 '20 at 13:19

15

Just burned two hours going through the great solutions on this page. Time for the stupid answer!

I redid my Security Groups, VPC's, Routing Tables, Subnets, Gateways... NOPE. I copy-pasted the URL from the AWS Console, which in some cases results in a hidden trailing space. The endpoint is in a <div> element, which the browser gives a \n when copying. Pasting this into the Intellij db connector coerces it to a space.

I only noticed the problem after pasting the URL into a quote string in my source code.

answered Apr 10 '20 at 13:19

Josh Hibschman

3,148
1
25
27

2

For those that get this far down the list... I had to do 3 of the solutions posted on this page. 1) I had to add an inbound rule as outlined by @aminah, 2) I had to make sure public accessibility was set as outlined by AnythonR, and lastly 3) there was a blank space at the end of the hostname from copying the text from the aws console... That last one took me a while to figure out... Wish they had a "Copy to Clipboard" button like Azure has for everything. – Stanton Jul 10 '21 at 01:52

score 8 · Answer 5 · edited Jul 28 '23 at 21:32

Make sure that your VPC and subnets are wide enough.

The following CIDR configuration works great for two subnets:

VPC 10.0.0.0/16 10.0.0.0 — 10.0.255.255 (65536 addresses)
Subnet 1 10.0.0.0/17 10.0.0.0 — 10.0.127.255 (32768 addresses, half)
Subnet 2 10.0.128.0/17 10.0.128.0 — 10.0.255.255 (32768 addresses, other half)

Adjust it if you need three subnets.

I wasn't able to connect to my RDS database. I've manually reviewed any detail and everything was alright. There were no indications of any issues whatsoever and I couldn't find any suitable information in the documentation. My VPC was configured with narrow CIDR: 10.0.0.0/22 and each subnet had a 255 addresses. After I've changed CIDR to 10.0.0.0/16 and split it totally between two subnets my RDS connection started to working. It was a pure luck that I've managed to find a source of the problem, because it doesn't make any sense to me.

score 2 · Answer 6 · answered Sep 23 '20 at 17:50

Well almost everyone has pointed out the answers, i will put it in different perspective so that you can understand.

There are two ways to connect to you AWS RDS

You provision an instance in the same VPC & Subnet. You install the workbench you will be able to connect to the DB. You would not need to make it public accessible. Example: You can provision an windows instance in the same VPC group and install workbench and you can connect to the DB via endpoint.
The other way is to make the Db publically accessible to your IP only to prevent unwanted access. You can change the DB security group to allow the DB port traffic to your IP only. In this way your DB will be publically accessible but to you only. This is the way we do for various AWS services we add there security group in the source part of the SG.

If both the options doesn't work then the error is in the VPC routing table, you can check there if it associated with the subnet and also if the internet gateway is attached.

You can watch this video it will clear your doubts:

https://youtu.be/e18NqiWeCHw

score 1 · Answer 7 · answered May 30 '20 at 22:02

In my case, when I upgrade the size. The private address of the rds instance fell into a private subnet of the VPC. You can use the article My instance is in a private subnet, and I can't connect to it from my local computer to find out your db instance address.

However, changing the route table didn't fix my issue. What I did finally solve my problem is to downgrade the size and then upgrade the size back. Once the private address falls back to the public subnet. Everything works like a charm.

score 1 · Answer 8 · answered Nov 27 '20 at 13:30

I was also not able to connect even from inside an ec2 instance.

After digging AWS RDS options it turns out that ec2 instances are only able to connect to RDS in the same VPC they are in.

When creating an ec2 instance in the same VPC where the RDS was I could access it as expected.

Himanshu Kumar · Answer 9 · 2021-06-18T03:22:58.220

1

Do not forget to check if you have your VPN or firewall blocking connection.

edited Jun 18 '21 at 03:22

answered Jun 17 '21 at 17:32

Himanshu Kumar

89
1
10

score 1 · Answer 10 · answered Sep 15 '22 at 18:00

The ideal debugging checklist is:

Instance's "Publicly Accessible" property should be enabled
The security group attached to the instance should have open inbound rules (as open as you'd want)
The funny part is still if you're not able to access it - then the problem surely is with your instance lying in a private subnet of the respective VPC.

However, there're more secure ways to access your RDS instance. The best bet would be not make it publicly accessible, lock down security groups and have a P2P relay endpoint (think Tailscale).

score 0 · Answer 11 · answered Sep 02 '21 at 13:49

0

In case you've tried all answers above try this... Recreate the database....

AWS on database creation provides an option to allow public/private access access

answered Sep 02 '21 at 13:49

Codedreamer

1,552
15
13

You don't need to re-create the DB to change public access, this can be modified after creation – Joel Davey Jan 09 '23 at 10:51

score 0 · Answer 12 · answered Oct 03 '22 at 18:44

0

I'm sure it's not the proper answer but I added the internet gateway to all my private subnet route tables.. Even though the private subnets and the public subnets are in the subnetgroup.

answered Oct 03 '22 at 18:44

bieboebap

320
3
18

score 0 · Answer 13 · edited Feb 03 '23 at 21:02

0

For me none of the above worked.

What did work was creating a peering connection between my default VPC and the VPC in which the database was created, as it appears that when connecting to resources in AWS, it automatically goes through the default VPC.

enter image description here

Then, set up routing using the peering connection between the 2 VPCs. Also, make sure that your security groups permits postgres ports from your default VPC CIDR block as well. And finally, make sure all the subnets are associated with your route table accessing this peering connection.

edited Feb 03 '23 at 21:02

tdy

36,675
19
86
83

answered Jan 31 '23 at 09:52

Monty

36
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 03 '23 at 18:15

score 0 · Answer 14 · answered Mar 10 '23 at 16:03

I went through all the obvious checks mentioned above but still couldn't connect to the instance. Turned out it was because of the name of the instance. I deleted an old instance and created a new one with the same name. Seems like somewhere in the AWS cache it still remembered the old database name and applied the old config to the new instance. The simple solution was to just use a new name for the database. Adding this here in case someone else has been banging their head on the desk like me for hours.

AWS: can't connect to RDS database from my machine

14 Answers14

Linked