How can I connect Amazon QuickSight to a private Amazon RDS data source in a different AWS Region or AWS account?

Last updated: 2022-07-29

I want to connect my Amazon QuickSight account to an Amazon Relational Database Service (Amazon RDS) data source in a different AWS Region or AWS account. How can I do this?

Short description

Example Amazon RDS data source and Amazon Virtual Private Cloud (Amazon VPC) configuration:

Account type Amazon RDS data source in the same Region Amazon RDS data source in a different Region
Same AWS account Amazon VPC connection in QuickSight Amazon VPC peering
Different AWS account Amazon VPC peering Amazon VPC peering

Resolution

Follow these instructions to connect QuickSight to an Amazon RDS data source in a different AWS Region or AWS account.

Note:

Example Amazon RDS configuration

These instructions use the following example for the Amazon RDS configuration:

  • RDS data source is hosted on VPC: vpc-33cc44dd
  • CIDR range of vpc-33cc44dd: 172.0.0.0/16
  • Subnet IDs and associated route table IDs: subnet-3c3d (rtb-33cd), subnet-4c4d (rtb-44cd)
  • Security group associated with RDS data source: sg-445566

Prepare your QuickSight environment

Note: If you already have an Amazon VPC and subnet, go to step 3.

1.    Create an Amazon VPC and a subnet in the same Region as your QuickSight account. To create an Amazon VPC and subnets, follow the instructions to create and configure your Amazon VPC and subnets.

Note:

  • Make sure that the CIDR block of your Amazon VPC is different than the CIDR block of your Amazon RDS instance.
  • Make sure to enable DNS hostnames and DNS resolution if you're planning to use the hostname of the Amazon RDS data source.

Example Amazon VPC configuration:

Name                     VPC           IPv4 CIDR      Description
-----------------------------------------------------------------------------------------
QuickSight Account VPC   vpc-11aa22bb  10.0.0.0/16    VPC created in QuickSight's Account

Example subnets configuration:

Name                                 Subnet ID      VPC            IPv4 CIDR     Route table
----------------------------------------------------------------------------------------------
Subnet 1 - QuickSight Account VPC    subnet-1a1b    vpc-11aa22bb   10.0.0.0/20   rtb-11ab
Subnet 2 - QuickSight Account VPC    subnet-2a2b    vpc-11aa22bb   10.0.16.0/20  rtb-22ab

2.    Create a security group and add an inbound rule for all TCP traffic from the Amazon VPC CIDR range of the Amazon RDS data source.

Note:

  • Make sure that you choose the Amazon VPC that you created in step 1.
  • For Inbound rules, choose the Type dropdown list, and then choose All TCP.
  • Choose the Source dropdown list, and then choose Custom.
  • If you are using Tags, for Value enter the VPC CIDR range of the Amazon RDS data source.

Example QuickSight security group configuration:

Security Group ID     Security Group Name          VPC ID
---------------------------------------------------------------
sg-112233             QuickSight Security Group    vpc-11aa22bb

Example inbound rule configuration:

Type        Protocol   Port Range   Source          Description
------------------------------------------------------------------------------
All TCP    TCP        0-65535      172.0.0.0/16    VPC CIDR of RDS Data Source

3.    Create the VPC connection in the QuickSight console.

  • Configure the VPC connection and Subnet ID that you created in step 1, and the Security group ID that you created in step 2.
  • Make sure that you are logged in as a QuickSight administrator. Only QuickSight administrators can view the Manage QuickSight option.

Example Amazon VPC connection configuration in QuickSight:

VPC connection name        VPC connection ARN                                                               Subnet ID      Security group ID    DNS resolvers
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
VPCConnectionQuickSight    arn:aws:quicksight:us-east-1:1212121212:vpcConnection/VPCConnectionQuickSight    subnet-1a1b    sg-112233

Prepare the Amazon RDS environment

Add an inbound rule in the security group associated with the Amazon RDS data source. This allows all TCP traffic from the Amazon VPC CIDR range of the QuickSight account Amazon VPC.

Example security group inbound rule (sg-445566) configuration of the Amazon RDS data source:

Type        Protocol   Port Range   Source         Description
------------------------------------------------------------------------------------
All TCP    TCP        0-65535      10.0.0.0/16    VPC CIDR of QuickSight Account VPC

Amazon VPC Peering

Create a connection between the Amazon VPCs

Create a VPC peering connection between the QuickSight account Amazon VPC and the Amazon RDS data source Amazon VPC.

1.    Open the Amazon VPC console.

2.    In the navigation pane, choose Peering connections, and then choose Create peering connection.

3.    (Optional) For Name, enter a name for the peering connection. For example, QuickSight RDS VPC peering.

4.    Choose the VPC ID dropdown list, and then choose the VPC that you created previously.

5.    For Select another VPC to peer with, do one of the following:

        Choose My account if your RDS data source and QuickSight use the same AWS account.

        -or-

        If your RDS data source and QuickSight don't use the same AWS account, then choose Another account.

6.    For Region, do one of the following:

        Choose This Region if your Amazon RDS data source and QuickSight use the same AWS account. Then, choose the VPC ID dropdown list, and choose the Amazon VPC that you created previously.

        -or-

        Choose Another Region, choose the Region dropdown list, choose the Region for the RDS data source and VPC, and then for VPC ID enter the VPC ID.

7.    Choose Create peering connection.

Example of Amazon VPC peering connection configuration:

Name                          Peering connection ID    Status               Requester VPC    Accepter VPC
--------------------------------------------------------------------------------------------------------
QuickSight RDS VPC peering    pcx-ab12cd34             Pending acceptance   vpc-11aa22bb     vpc-33cc44dd

Accept an Amazon VPC peering connection

An Amazon VPC peering connection in the pending-acceptance state must be accepted in the same AWS account and Region of the accepter Amazon VPC.

Follow the instructions to accept an Amazon VPC peering connection.

Example Amazon VPC peering connection configuration:

Name                          Peering connection ID    Status   Requester VPC    Accepter VPC
--------------------------------------------------------------------------------------------------------
QuickSight RDS VPC peering    pcx-ab12cd34             Active   vpc-11aa22bb     vpc-33cc44dd

Note: To use the hostname of the Amazon RDS data source, you must enable the DNS resolution for an Amazon VPC peering connection. The DNS resolution must also be enabled for both Amazon VPCs.

Update the route tables

Follow instructions to update the route tables in the QuickSight account and RDS data source account to route network traffic.

Note:

  • The route destination is the CIDR block of the peer VPC and the target is the ID of the VPC peering connection.
  • Make sure to update the route tables of all subnets associated with the Amazon RDS data source with the same routes to avoid connectivity loss.

Example QuickSight Amazon VPC connection subnets route table (rtb-11ab) configuration:

Destination     Target
----------------------------
10.0.0.0/16     local
172.0.0.0/16    pcx-ab12cd34

Example Amazon RDS data source subnets route tables (rtb-33cd, rtb-44cd) configuration:

Destination     Target
-----------------------------
172.0.0.0/16    local
10.0.0.0/16     pcx-ab12cd34

Connect QuickSight to the Amazon RDS data source

1.    Open the Amazon QuickSight console.

2.    In the navigation pane, choose Datasets, and then choose New dataset.

3.    Choose your database engine, such as PostgreSQL, MySQL, and so on.

4.    For Connection type, choose the dropdown list, and then choose the Amazon VPC that you created for step 3 in the Prepare your QuickSight environment section.

5.    Enter the variables for your database.

6.    Choose Validate connection to make sure that QuickSight can connect to the data source, and then choose Create data source.

7.    Choose the database that you want to use, choose a table, and then choose Select.

For more information, see Creating a dataset using an existing database data source.