How can I create a private connection from Amazon QuickSight to an Amazon Redshift cluster or an Amazon RDS DB instance that's in a private subnet?

5 minute read
0

I want to create a private connection from Amazon QuickSight to an Amazon Redshift cluster or database instance that's in a private subnet.

Short description

QuickSight supports Amazon Virtual Private Cloud (Amazon VPC) connections to AWS data sources. The Amazon VPC connection allows you to privately connect to an Amazon Redshift cluster or an Amazon Relational Database Service (Amazon RDS) instance.

To create a private connection from QuickSight, you must provide a subnet and security group from a VPC that's in the same AWS Region. Then, create a private connection from QuickSight to the private subnet. After you establish the private connection, you can allow traffic between the new security group and the Amazon Redshift cluster or DB instance security group.

Note: The data source must be in the same AWS account and Region that you use for QuickSight. Cross-Region and cross-account data sources require additional configuration. For more information, see How can I connect Amazon QuickSight to a private Amazon RDS data source in a different AWS Region or AWS account?

Resolution

Important:

Add an inbound rule and outbound rule to the QuickSight security group

Complete the following steps:

  1. Identify the ID of the subnet that QuickSight uses to establish a private connection to your data source.
    Note: Each VPC connection must use at least two subnets. You can either use an existing subnet in the same VPC with a route to the database instance, or create a new subnet.
  2. Create a new QuickSight security group in the same VPC.
  3. Add an inbound rule to the security group that allows all communication from the Amazon Redshift cluster or RDS DB instance.
  4. For Type, choose All TCP.
  5. For Source, choose Custom, and then enter the ID of the security group that your Amazon Redshift cluster or RDS DB instance uses.
  6. Add an outbound rule to the QuickSight security group that allows all traffic to the Amazon Redshift cluster or RDS DB instance.
  7. For Type, choose Custom TCP Rule.
  8. For Port Range, enter the port that the Amazon Redshift cluster or RDS DB instance uses. The default Amazon Redshift port is 5439. The default Amazon RDS port is 3306.
  9. For Destination, choose Custom, and then enter the ID of the security group that your Amazon Redshift cluster or RDS DB instance uses.

Add an inbound rule and outbound rule to the Redshift cluster or RDS security group

Complete the following steps:

  1. In the Amazon Redshift cluster or RDS DB instance's security group, add an inbound rule that allows all incoming traffic from the QuickSight security group.
  2. For Type, choose Custom TCP Rule.
  3. For Port Range, enter the port that the Amazon Redshift cluster or RDS DB instance uses. The default Amazon Redshift port is 5439. The default Amazon RDS port is 3306.
  4. For Source, choose Custom, and then enter the QuickSight security group ID.
  5. In the Amazon Redshift cluster or RDS DB instance's security group, add another outbound rule that allows all traffic to the QuickSight security group.
  6. For Type, choose All TCP.
  7. For Destination, choose Custom, and then enter the QuickSight security group ID.

Create a private connection from QuickSight to Amazon VPC

Complete the following steps:

  1. Open the QuickSight console.
  2. Choose your profile icon, and then choose Manage QuickSight.
  3. In the navigation pane, choose Manage VPC connections, and then choose ADD VPC CONNECTION.
  4. For VPC connection name, enter a name for the connection.
  5. For VPC ID, choose the VPC for your Amazon Redshift cluster or RDS DB instance.
  6. For Execution role, choose the IAM role that you use for the VPC connection.
    Note: The Execution role dropdown list shows only IAM policies that contain a trust policy that allows QuickSight to configure the VPC connection.
  7. For Subnet ID, select at least two private subnets.
  8. Choose Add.

Create a new dataset from the Amazon Redshift cluster or RDS DB instance

Complete the following steps:

  1. Open the QuickSight console, and then choose Datasets.
  2. Choose New dataset.
  3. Create a connection to an auto-discovered AWS data source. Be sure to choose the VPC connection type that you created.

Example QuickSight SG-123345678f:

Inbound:

Type             Protocol          Port Range         Source                  Description------------------------------------------------------------------------------------------------------------------
All TCP           All              0 - 65535       sg-122887878f         Amazon RDS/Amazon Redshift security group

Outbound:

Type              Protocol          Port Range           Source                  Description------------------------------------------------------------------------------------------------------------
Custom TCP          TCP            5439 or 3306       sg-122887878f       Amazon RDS/Amazon Redshift security group

Example Amazon RDS or Amazon Redshift SG-122887878f:

Inbound:

Type             Protocol          Port Range           Source                Description-----------------------------------------------------------------------------------------------------
Custom TCP         TCP            5439 or 3306        sg-123345678f        QuickSight security group

Outbound:

Type            Protocol          Port Range          Source                  Description-------------------------------------------------------------------------------------------------
All TCP           TCP             0 - 65535           sg-123345678f        QuickSight security group

Related information

Connecting to a VPC with Amazon QuickSight

AWS OFFICIAL
AWS OFFICIALUpdated a month ago
5 Comments

I am still getting timeouts after following this guide for an rds postgres instance. Any idea what might be missing?

deniz
replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago

Hey I'm also getting timeouts after following this guide and all the others. I have even used the VPC reachability analyzer and confirmed that each network interface of the quicksight VPC connection can successfully reach the RDS network interface.

Is there any other way to troubleshoot my connection? I've made extremely permissive security group rules (allow all traffic on all ports etc) and still within the same VPC and subnet, the connection times out.

EDIT: This top answer solved my problem, apparently the underlying quicksight JDBC doesn't support "scram-sha-256" password hashing which my postgres 14 RDS had enabled by default, following the answer guide solved my issue. Hopefully it saves someone else from the wasted days I've lost!

nick
replied a year ago

The above comment from Nick needs to be on a pin comment here, I battled with this issue for almost a month.

replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago