There are cases where you want to connect AWS Quicksight to pull data from an RDS Database in one of your Private VPCs. Its one of those things that you don’t do often and its just funky enough and different enough from most AWS services that I have had to relearn how to do it each time. So here’s what I’ve learnt for posterity.
Quicksight can automatically connect to databases that can be accessed via a public IP. If your DB is publicly accessible to the Internet (with Security Group filtering of course), then you can pretty much ignore this article.
If you happen to have the weird case where your DB does have a public IP address but it is not actually accessible to the public Internet for policy, technical or historical reasons, then read on.
Quicksight VPC Connection
Requirements
Quicksight has the option of creating a connection between you instance of Quicksight and one of your VPCs. It does that by injecting a Network Interface into a subnet you specify from the target VPC.
You only have to supply the
VPC ID
that your target DB is in.Subnet
that is routable to the subnet your target DB is inSecurity Group
dedicated to the Quicksight connection that will allow all TCP taffic to the target DB- Optionally a
DNS Inbound Endpoint
We’re going to assume that the target DB and its VPC already exists.
You can use an existing Subnet
as long as its in the same VPC and is routable to the subnets used by the target DB. By default subnets in a VPC can route to any other subnet in the VPC, but you should double check.
When you create the VPC Connection
in the Quicksight management console, it will automatically create a Network Interface
on the specified Subnet
and will be associated with the Security Group
specified.
Note that the Security Group
associated with this new Quicksight Network Interface
will be stateless. Any response packets coming back from a Quicksight request will have randomly allocated port numbers. Normally Security groups are stateful and handle this for you. But in the case of the Quicksight Network Interface you have to explicitly enable that any port is allowed for inbound.
The optional DNS Inbound Endpoint
allows you to tell Quicksight to use the private DNS Resolver for your VPC instead of querying just the Public DNS zones. This is what is needed if your target DB has a Public IP address. Without this setting this Quicksight will get the Public IP address when it queries the Endpoint name
of your DB. You will be scratching your head for days wondering why the connection is not working.
If you do use DNS Inbound Endpoint
option, you will have to set it up in Route53
.
Detailed instructions for all of this are described below.
A VPC Connection
will allow Quicksight to connect to any of the following in your VPC:
- Amazon OpenSearch Service
- Amazon Redshift
- Amazon Relational Database Service
- Amazon Aurora
- MariaDB
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- Presto
- Snowflake
You can reuse a VPC Connection
for any Datasource in your Quicksight account in a region.
Subnet Info
Get VPC Info
We’ll need the VPC ID
and the CIDR Block
associated
You can look at your RDS Configuration to see what VPC it is in.
In this example:
VPC ID
ends in2aed
CIDR Block
: 10.0.0.0/16
Pick a Subnet for the Quicksight Network Interface to Use
The criteria are:
- In the same VPC as the target DB
- Is a private subnet
- In the same Availability Zone as at least one of the subnets associated with the target DB
- Routable to that subnet in the target DB
- Has a route table that routes to the VPC CIDR Block
- And the Target DB Subnets also can route to the VPC CIDR Block
- Doesnt have an ACL that would block acces to/from the target DB
- This is the usual case
Find the subnets used on the target DB
In this example our target DB is an Aurora Postgres cluster. Looking at the RDS Console we can find the subnets its usings
Click on one of the subnets to view the subnet info
See what Availability Zone
its in (us-east-1b in this example)
Confirm it routes to the VPC CIDR Block (10.0.0.0/16 in this example)
- Go to the subnet view in the VPC Console.
- Find an existing subnet that also routes to the same VPC CIDR Block (or overlapping subset with the DB subnet and its on the same
Availability Zone
)- You could also create a new subnet for this as long as it meets the same criteria
- In our example its the subnet that ends with
90dc
Security Group
Create a new Security Group
dedicated to the Quicksight Network Interface. You don’t have to create one specific to this, but it will make management easier than trying to mix it in with your existing Security Groups.
We’ll call it Amazon-QuickSight-access
. Nothing magic about the name though, whatever fits into your naming scheme.
Inbound Rules
Set the Inbound Rules
to allow trafic on all TCP ports. As mentioned earlier, this is because this will be a stateless security group and all response packets will have random inbound ports.
Outbound Rules
The Outbound Rules
should limit the destinations to just your target DB. The easiest way is to set the destination to be the Security Group set in your RDS Database.
You should also limit the outbound ports to be ones appropriate for your target DB, such as port 5432 for Postgres.
We ran into a problem where for historical reasons, there were some existing inbound rules in the Target DB that prevented us from using the Target DB as the destination security group, so we used a CIDR range that covered the Target DB range of addresses. This should be an unusual situation and you can probably ignore it.
DNS Resolver Endpoints (optional)
You only need to fill this in for cases where the DNS lookup of your Target DB Endpoint
would be incorrect against Public DNS. The usual use case for this is when you have a somewhat complicated VPC Peering setup where your Target DB is on the other side of a VPC Peering setup. In that case, only the DNS Resolver in your private VPC may know the proper resolution of the Target DB Endpoint.
In our case, we had the unusal situation that our Target DB had a public IP address, so when Quicksight would do a DNS Query on the Target DB Endpoint
name, it would get the Public IP address which was not valid for the VPC Connection
. The workaround is for Quicksight to use the local VPC DNS Resolver. And thus our need to setup the DNS Resovler Endpoints
It took a while to figure out this was why we could never get the VPC Connection
to work until we set this up. The diagnostics of the VPC Connection
Validation check does not differentiate between Networking, DNS, or username/password problems. An issue in anyone of those can make the connection validation fail.
Route53 Resolver Inbound Endpoints
Create a Security Group for the Resolver
The resolver needs to have a security group for itself to allow the DNS requests to get to it.
Inbound Rules
- Create a new Security Group and call it something like
quicksight-route53-resolver
or whatever fits your nameing scheme. - Set the
Inbound Rules
to allow for DNS UDP and DNS TCP from all sources on the VPC CIDR Block
Outbound Rules
Can leave the default outbound to all rule
Setup the Route53 Endpoint Resolver
You will need to go to the Route53 Console and select Resolver->Inbound endpoints
and click on the Create Inbound Endpoint
button.
- Set the
Endpoint name
- Something that fits your naming scheme
- Our example is
quicksight-prod
- Set the VPC ID to be the same as your
VPC-ID
used for your Target DB - Set the Security Group to the Security Group you created
- Set the Availability Zone / Subnet for two IP Addresses for the resolver
- Could be any that are routable to the Subnet that is assigned to the
VPC Connection
. - Should be a private subnet
- Might as well make one of them the same Subnet used by the
VPC Connection
- Check the option
Use an IP address that is selected automatically
for both - Click
Submit
when done
- Could be any that are routable to the Subnet that is assigned to the
You will then end up with an Inbound Endpoint
that will have been assigned two IP addresses. These addresses will be needed to supply to the VPC Connection
and be used to update the Quicksight Security Group.
In this example the two IP Addresses are
- 10.0.100.120
- 10.0.101.80
Update the Quicksight Security Group for DNS
If you are using the DNS Resolver Inbound Endpoints feature, you will also have to update the Outbound Rules
of the Security Group we created earlier for the Quicksight Network Interface
. This is to enable Quicksight to be able to access the DNS Resolver as well as the Target DB.
To do this we will add DNS UDP and DNS TCP to the Output Rules
of the Amazon-QuickSight-access
Security Group for each of the two IP Addresses from the Inbound Resolver
we just created. Note that you need to have the CIDR suffix /32
at the end when entering them into the Security Group editor.
Create the Actual VPC Connection
Now we have everything we need to setup the actual VPC Connection
in the Quicksight management console.
You will of course need to have proper permissions to access and manage Quicksight in your account. That is beyond the scope of this article. We’re going to assume you have all that already.
- Enter the Quicksight Console, click on your username on the topr right of the page and select
Manage Quicksight
- Then select
Manage VPC Connections
in the left hand Navbar
- Click on
Add VPC Connection
to create the new connection
Fill in the form with the info we found or created earlier:
VPC Connection Name
- Appropriate name of the connection based on your naming conventions
- Our example:
my-aurora-db
VPC ID
: TheVPC ID
we have been using earlier- Our example ends with
2aed
- Our example ends with
Subnet ID
: The Subnet we chose for the QuicksightNetwork Interface
- In our example it ends with
90dc
- In our example it ends with
Security Group ID
: The Security Group we created for Quicksight- Our example:
Amazon-QuickSight-access
(ended in16e8
)
- Our example:
DNS Resolver Endpoints
: The IP addresses from the DNS ResolverInbound Endpoints
- Our example:
10.0.100.120
and10.0.101.80
- Our example:
Create a Dataset using the VPC Connection
Now that the VPC Connection
has been setup, we can use it to create a Dataset from the Target DB.
- Click on the
Quicksight
logo on the top left of the screen to get back to the Quicksight home page. - Click on
Datasets
at the bottom of the left Navbar - Click on
New dataset
on the top right of the page.
- Click on
Aurora
(or other source, but we’re not going to show other sources in this article)
Fill in the New Aurora data source
form
-
Data source name
: Our example ismy-data-source
-
Connection type
: Select the VPC connection we createdmy-aurora-db
-
Database connector
:PostgreSQL
-
Database server
: TheEndpoint
of your Aurora DB- This is the fully qualified DNS name of your DB endpoint
- You can find it in the
Connectivity & security
tab on the DB’s RDS Console page - You probably want to use a reader endpoint
-
Port
: The proper port for your Target DB- Postgres default is
5432
- Postgres default is
-
Database Name
: The name of the database within the RDS of interest- Same name you would use in a DB connection or in psql to connect to your working DB’s
-
Username
: The db username needed to connect -
Password
: The db password
Click on Validate Connection
It should turn to Validated
with a checkmark if all went well. Should happen within a few seconds.
At this point you can now click on the Create data source
button and do the normal Quicksight data source stuff. That is all independent of the VPC Connection and is not part of this article.
If the VPC Connection fails to Validate
If the Validate failed you are going to have to check several things. There will be an error message. You can click on the details
link, but its probably not going to be helpful.
The error diagnosicts for the VPC Connection rarely gives you any more info other than it could not connect to the DB.
You need to determine if its because of:
- The routing from the Quicksight Network Interface to the Target DB
- The Security Group settings
- Basic error in the regular connection parameters (
Database name
,Username
,Password
) - The
Database Server
is the correct value and Quicksight DNS query is getting the right value (private IPs not public or nothing at all)
Check for basic connectivity
You can check the basic connectivity (routing and security groups) is working using the Reachability Analyzer
in the VPC Console. Unfortunately the analyzer has a limited set of elements that can be specified as a Source and Destination. The only one that applies to the Quicksight VPC Connection as a source and Aurora RDS as a Destination are Network Interfaces
. So we’re going to need to find the IDs of those two Network Interfaces.
Find the ID of the VPC Connection Network Interface
You will need to know the Network Interface
ID of the interface created for the VPC Connection. To figure that out go to the EC2 Console page and click on Network Interfaces
under Network & Security
in the Navbar on the left
Then search for the name you used for the Quicksight connection. Our example was my-aurora-db
It will be part of the description of the Network Interface
associated with that connection. In our example it starts with eni-0b9e
Find the ID of the Target DB Network Interface
You will need to know any of the Network Interface
IDs of the Target DB. There can be a few as there may be one per Availability Zone. It doesn’t matter which one you choose.
- Still on the EC2 Console page
Network Interfaces
page, search for the Target DB’s Security Group name.- You can find the Target DB Security Group name on the RDS Console page for your Target DB under the
Connectivty & Security
tab labeledVPC security groups
- You can find the Target DB Security Group name on the RDS Console page for your Target DB under the
- Select any one of them.
- Our example starts with:
eni-050d
- Our example starts with:
Run the Reachability Analyzer
Go to the VPC Console and click on the Create and analyze path
button on the top right of the page
- Give it a name
- Select
Network Interfaces
for theSource type
andDestination type
- Specify the
Network Interface ID
of the QuicksightNetwork Interface
we found- Starts with
eni-0b9e
in our example
- Starts with
- Specify the
Network Interface ID
of the Target DBNetwork Interface
we found- Starts with
eni-050d
in our example
- Starts with
- Specify
5432
for theDestination port
- Or whatever port you set for your Target DB if not Postgres
- Protcol is
TCP
- Click on
Create and analyze path
If it all works you should see:
If that works, you configured the DNS Endpoint Resolver, but your VPC Connection / Dataset creation still doesn’t work, you may want to repeat the Reachability Analyzer
test for the DNS TCP and UDP ports in addition to the Postgres Port to double check for the DNS passing properly between the resolver and Quicksight.
Conclusion
If the Reachability Analyzer
said connectivity is ok and it still doesn’t work, then its probable that one of the other basic connection parameters is wrong, or there is something wrong with the Endpoint
name. If you hadn’t tried setting up the DNS Endpoint Resolver option, you can try that to see if there was a problem with how Quicksight was resolving the DNS for your Endpoint
. That was what started this whole journey for me.
Otherwise, hopefully this did work for you and you can now happly view your Target DB in Quicksight!