AWS: Unable to connect Amazon QuickSight to RDS [closed]

Question

We have a few MySQL RDS instances in West-1 (N. California) and wanted to create some reports using QuickSight. First issue was that QuickSight was only available in West-2 (Oregon) and did not show our RDS, so I created a read-replica database into West-2.

I was still not able to connect to my instance, and it showed the following error: Connection failed. Make sure your instance is accessible from the appropriate Amazon QuickSight IP address range.

I had already created a Security Group allowing IP 54.70.204.128/27 in the inbound rules and applied it to my instance. What eventually allowed me to connect to the instance was to set the 'Publicly Available' field of the instance to 'Yes'.

However, my boss prefers it to remain 'No' and we were also able to connect to the non-public instance via DOMO using MySQL SSH connection method.

Is it possible to get the connection between QuickSight and RDS working without setting Publicly Accessible to Yes?

Affirmatory

Answer 1

I created a support request with AWS about this, their answer is below. I will give it a try.

Hi Alistair,

Thank you for contacting AWS Premium Support. I am happy to assist you today.

I understand that you would like to connect to your Prod RDS database from QuickSight, but you getting an error: "Not Validated". You also would like to know if there is a workaround seeing that your RDS instance in not Publicly accessible.

Amazon Web Services offers a service called CloudFormation, this service helps automate certain processes. This service will allow your RDS instance to connect to QuickSight through a custom NAT instance. Therefore you will not have to put your Data Base in a public subnet. Attached is a CloudFormation template (NAT_RDS_Provisioning.template), after launching the CloudFormation stack [1] an instance and a security group will be created, you then enter the required variables. This will then give access from the source address on the source port you define to the endpoint of the EC2 instance which will then allow QuickSight to access your RDS instance server without making your RDS instance public.

To launch this CloudFormation stack please see the steps below… Please keep in mind that the region you create this in must be the same region that your database resides in.

1. From your AWS console navigate to " CloudFormation " 
2. Click " create stack ", you will then be asked to Select Template, you will then select " Design template".
3. Next to Parameters - click " Mappings ", at the bottom of that page you will see: Components and Template. 
4. Select " Template " and copy and paste the provided script in there. (see attached: NAT_RDS_Provisioning.template)(Please use case link below signature)
5. In the top right hand corner you will see a refresh button, click to refresh. 
6. On the top left there is a square with a tick inside (clicking this validates the template). 
7. Once validated - click the little cloud with the arrow in it , this will create the stack.
8. You will be taken back to the select template page, click "next" in the bottom right corner.
9. Under Specify Details, name your stack and then complete all Parameters, info on parameters provided below: 
    9.1 Stack name  (Example: NAT-RDS-QuickSight)
    9.2 DestinationAddress - Add your RDS instance Endpoint here. (That way when a fail-over occurs the endpoint should be updated in 60 seconds maximum).
    9.3 DestinationPort - The service remote destination port: 
    9.4 InstanceType - The EC2 instance class. (The size of the NAT instance will depend on the amount of data you want to pull into QuickSight) 
    9.5 KeyName - Name of an existing EC2 KeyPair to enable SSH access to the instance: 
    9.6 SourceAddress - The source range you want to allow access from: example 0.0.0.0/0.
    9.7 SourcePort - The port the service must listen on: 
    9.8 Subnet - a Public Subnet that is in the same VPC as your RDS instance:
10. Click " Next "
11. On the Options page - complete the desired fields and click " Next " (Optional)
12. Review all information - (Confirm the details for your NAT EC2 instance)
13. Under template you will see "Estimate cost - click on cost to give you an idea of the monthly estimate to have this service running) 
14. Then  select " Create " in the bottom right hand corner.
15. On the main CloudFormation Page, click "refresh" You will the notice the status of your Stack being created.
16. If you navigate to your EC2 console you will notice your NAT instance running / creating.

Please ensure that the NAT instance has access to your RDS instance, this includes Security Group settings. This should be done by including the IP address of the NAT instance in the Security Group of the RDS instance database.

You should then be able to access the EC2 IP address and port as setup, this will then forward traffic to your Database.

When creating the above you would have seen the estimate costs involved however I have added two more links below for some more information on this and also the cost calculator for you too.

Costs will be the same as an EC2 instance, this launches the AWS Linux latest standard AMI, and throughput will be determined by the instance class [2] & [3]:

[1] https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/GettingStarted.Walkthrough.html
[2] https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html
[3] https://calculator.s3.amazonaws.com/index.html

I hope the above information and solution is helpful.

Should you run into any difficulties with any of the above or if you are unsure of anything, please free to reach out to me and I will be more than happy to assist.

To see the file named 'NAT-RDS-Provisioning-Template.template' included with this correspondence, please use the case link given below the signature.

Best regards,

Delene T. Amazon Web Services

---

See the provided template file content here: https://pastebin.com/m67sz4bR

---

Di

Note: when connecting to the data source from Quicksight, you need to first select the Database Type (eg MySQL or SQL Server and NOT RDS) then enter the Public DNS (Endpoint for NAT EC2 instance) of your NAT instance in the Database server field. Plus de-select the "Enable SSL" to validate your connection. – Di

Thank you!! my RDS instance was publically accessible and I wasn't able to connect It via quickSight – Bumgarner

Answer 2

Things have changed, luckily.

It is possible to connect Amazon QuickSight to a DB in RDS which is in a VPC, although the AWS docs are not clear about all the necessary steps.

Basically, follow the steps here, https://docs.aws.amazon.com/quicksight/latest/user/working-with-aws-vpc.html but make sure you will create TWO security groups:

• Security Group for the Instance in Your VPC
• Security Group for Amazon QuickSight's Elastic Network Interface

Let’s start with the second one: It is the one which will QuickSight assign to a network interface (ENI), which will be automatically created in your VPC to access the DB. This is the one I missed to create at first and this is the one you use to create a VPC Connection in QuickSight. The details are in the above docs in the paragraph “Security Group Rules for Amazon QuickSight's Elastic Network Interface”.

The first one looks like this: Inbound: TCP / port according to the DB – in the case of MySQL it is “MYSQL/Aurora, TCP, 3306”, Source: the previous security group. Do not forget to add this one to your DB instance.

Good luck.

Supercharge

Just as an update: Here is a blog entry last updated in May 2021 describing the process. And here is the corresponding video from Feb 2021. – Unitary

I am also facing the same problem - to connect Quicksight to RDS in a VPC. Does the above solution is applicable to Quicksight standard edition? or Enterprise edition? – Toneytong

Answer 3

Does your RDS Replica is in a VPC or not?

Identify first and then accordingly create a security group and attach neccessary rules to it !

I would recommend you to read Authorizing Connections from Amazon QuickSight to Amazon RDS Instances

Sexcentenary

That's the documentation that I followed to get the IP address to white-list. My instance is behind a VPC. – Affirmatory