AWS: Unable to Connect Amazon QuickSight to RDS
We have multiple MySQL RDS instances in West-1 (N. California) and wanted to generate multiple reports using QuickSight. The first problem was that QuickSight was only available in West-2 (Oregon) and didn't show our RDS, so I created a read-replica database in West-2.
I was not able to connect to my instance yet and it showed the following error: Connection error. Make sure your instance is accessible from the correct Amazon QuickSight IP address range.
I have already created a security group allowing IP 54.70.204.128/27 in inbound rules and applying it to my instance. What ultimately 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 the MySQL SSH connection method.
Is it possible to establish a connection between QuickSight and RDS without setting up public access to yes?
source to share
I created a support request with AWS about this, their answer is below. I'll try.
Hi Alistair,
Thank you for contacting AWS Premium Support. I am glad to help you today.
I understand that you want to connect to your Prod RDS database from QuickSight, but you are getting an "Not Verified" error. You would also like to know if there is a workaround that your copy of RDS is not publicly available.
Amazon Web Services offers CloudFormation, a service that helps automate certain processes. This service will allow your RDS instance to connect to QuickSight through its own NAT instance. Therefore, you do not need to put your database on a public subnet. Attached CloudFormation template (NAT_RDS_Provisioning.template), after starting the CloudFormation stack [1], an instance is created and a security group is created, after which you enter the required variables. It will then provide access to 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 server instance without exposing your RDS instance.
To start this CloudFormation stack, see the steps below. Please keep in mind that the region you create it in must be the same region as your database.
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.
Make sure the NAT instance has access to your RDS instance, including the security group settings. This must be done by adding the IP address of the NAT instance to the RDS instance database security group.
Then you should be able to access the IP address and EC2 port as a setting, after which it will redirect traffic to your database.
You would see the estimated costs in creating above, however I have added two more links below for more information on this, as well as a cost calculator for you.
The cost will be the same as the EC2 instance, this runs the latest AWS Linux AMI, and the throughput will be determined by instance classes [2] and [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 are helpful.
If you have any difficulty with any of the above questions, or if you are unsure of anything, please do not hesitate to contact me and I will be more than happy to help.
To see a file named "NAT-RDS-Provisioning-Template.template" included in this correspondence, use the link with the code provided below the signature.
Respectfully,
Delene T. Amazon Web Services
See the provided template file content here: https://pastebin.com/m67sz4bR
source to share
Is your RDS replica in the VPC or not?
Define first and then create a security group and attach the necessary rules to it!
I would recommend you read Authorizing Connections from Amazon QuickSight to Amazon RDS Instances
source to share
Fortunately, everything has changed.
Amazon QuickSight can be connected to a database in RDS that resides in a VPC, although the AWS documents are not clear about all the steps involved.
Basically follow these steps: https://docs.aws.amazon.com/quicksight/latest/user/working-with-aws-vpc.html, but make sure you create TWO security groups:
- Security group for an instance in your VPC
- Security Group for Amazon QuickSight Elastic Network Interface
Let's start with the second one: this is the one that QuickSight will assign to the Network Interface (ENI), which will be automatically created in your VPC to access the database. This is the one I missed when creating in the beginning, and this is the one you use to create a VPC connection in QuickSight . See the Amazon QuickSight Elastic Network Interface Security Group Rules for details on the above documents.
The first one looks like this: Incoming: TCP / port according to the database - in the case of MySQL, this is "MYSQL / Aurora, TCP, 3306", Source: previous security group . Don't forget to add this to your DB instance.
Good luck.
source to share