Post Syndicated from Lotfi Mouhib original https://aws.amazon.com/blogs/big-data/amazon-quicksight-deployment-models-for-cross-account-and-cross-region-access-to-amazon-redshift-and-amazon-rds/

Many AWS customers use multiple AWS accounts and Regions across different departments and applications within the same company. However, you might deploy services like Amazon QuickSight using a single-account approach to centralize users, data source access, and dashboard management. This post explores how you can use different Amazon Virtual Private Cloud (Amazon VPC) private connectivity features to connect QuickSight to Amazon RDS or Amazon Redshift deployed in a different AWS account or Region.

Prepare the QuickSight environment

To implement any of the options discussed in this post, you need QuickSight with an Enterprise Edition subscription. One of the differences between this edition and the Standard Edition is the ability to connect QuickSight to a VPC—through an Elastic Network Interface (ENI)—and keep network traffic private within the AWS network. In this section, we take you through creating a VPC and enabling a VPC connection for QuickSight. For the rest of this post, you use AWS CloudShell from the AWS Management Console to run AWS Command Line Interface (AWS CLI) commands.

Before you complete the following steps, make sure you have sufficient AWS Identity and Access Management (IAM) privileges associated with your IAM user or role to create VPC endpoints, security groups, route tables, and a VPC peering connection.

If you have Amazon RDS or Amazon Redshift in the same Region and your organization uses cross-account resource sharing for your VPC, you can skip the next steps and go to the section on VPC sharing.

If you already have a VPC and subnet you want to use, you can skip to step 3.

1. Create a VPC and a subnet in the Region where your QuickSight account is deployed. See the following code:

   aws ec2 create-vpc \
       --cidr-block CIDR block different than where redshift and rds are deployed \
       --tag-specifications \ 
    'ResourceType=vpc,Tags=[{Key=Name,Value=QuicksightVPC}]

2. Create a subnet where QuickSight can deploy an ENI:

   aws ec2 create-subnet \
       --vpc-id ID of the VPC you created above\
       --cidr-block subnet cidr block within your VPC range \
       --tag-specifications \ 
       'ResourceType=subnet,Tags=[{Key=Name,Value=QuicksightSubnet}]'

3. Create a security group and allow a self-reference for all TCP traffic from the VPC CIDR range:

   aws ec2 create-security-group \
       --group-name QuicksightSG \
       --description "Quicksight security group" \
       --vpc-id ID of the VPC you created above \
       --tag-specifications 'ResourceType=security-group,Tags=[{Key=Name,Value=QuicksightSG}]'

4. Create a route table and associate it to the subnet you created:

   aws ec2 create-route-table \
       --vpc-id ID of the VPC you created above \
       --tag-specifications 'ResourceType=route- \ table,Tags=[{Key=Name,Value=QuicksightRouteTable}]'

   aws ec2 associate-route-table \ 
       --route-table-id id of route table created above \
       --subnet-id subnet id created above

   Now you configure QuickSight to create a VPC connection in the subnet you just created.

5. Sign in to the QuickSight console with administrator privileges.
6. Choose your profile icon and choose Manage QuickSight.
   
7. On the Manage QuickSight console, in the left panel, choose Manage VPC Connections.
8. Choose Add VPC Connection.
9. Provide the VPC ID, subnet ID, and security group ID you created earlier.
10. You can leave DNS resolver endpoints empty unless you have a private DNS deployment in the VPC.

You have now enabled QuickSight to access a subnet in your VPC. The following diagram shows the infrastructure you deployed.

Set up the data source infrastructure

You can choose from multiple possible deployment models when connecting Amazon Redshift or Amazon RDS. In the following sections, we walk you through how you can achieve each solution and when to use each one. For the rest of this post, we refer to Amazon Redshift and Amazon RDS as the data source.

VPC peering

A VPC peering connection is a networking connection between two VPCs that enables you to route traffic between them using private addresses. Instances in either VPC can communicate with each other as if they’re within the same network. This model is suitable for when the two accounts aren’t in the same AWS Organizations organization or Region, or are in the same organization but there is no AWS Transit Gateway attachment to the data source VPC. In this section, we create a network path between the QuickSight VPC and the data source VPC.

1. Create a VPC peering between the two VPCs. For instructions, see Creating and accepting a VPC peering connection.
   To use the hostname of the data source, you need to enable the DNS resolution for the peering connection at the requester and accepter of the peering connection. The DNS resolution must also be enabled at bot VPCs for this to work.
2. Run the following commands to enable the DNS resolution (depending on which account or Region the peering connection is initiated in, you need to run one command in the requester account or Region and the other in the accepter account or Region)

   aws ec2 modify-vpc-peering-connection-options \
       --vpc-peering-connection-id your peering connection id \
       --accepter-peering-connection-options \ 
   '{"AllowDnsResolutionFromRemoteVpc":true}'

   aws ec2 modify-vpc-peering-connection-options \
       --vpc-peering-connection-id your peering connection id \
       --requester-peering-connection-options \ 
   '{"AllowDnsResolutionFromRemoteVpc":true}'

3. Update the route tables in both the QuickSight VPC and data source VPC to route network traffic between them:

   aws ec2 create-route \
       --route-table-id amazon quicksight subnet route table id\
       --destination-cidr-block data source vpc cidr \
       --vpc-peering-connection-id the id of the peering you created above 

   aws ec2 create-route \
       --route-table-id data source subnet route table id\
       --destination-cidr-block quicksight vpc cidr \
       --vpc-peering-connection-id the id of the peering you created above

   Finally, you update the security groups for both QuickSight and the data source to allow traffic. If both VPCs are in the same Region, you can reference the security group instead of the CIDR of either data source or the QuickSight subnet.

4. In the QuickSight AWS account, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id quicksight security group \
       --ip-permissions \ 
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=data source subnet CIDR}]

5. In the data source AWS account, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id data source security group \
       --ip-permissions \ 
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=quicksight subnet CIDR}]

   You have now created and configured a network path between QuickSight and your data source. The following diagram shows the infrastructure you deployed.

You can skip the next sections and proceed to connecting QuickSight to the data source.

AWS Transit Gateway

Transit Gateway connects VPCs through a central hub. It’s usually used in large deployments because it simplifies your network and reduces complex peering relationships. The transit gateway acts as a cloud router—each new connection is only made one time. A deployment model for the VPC of QuickSight with Transit Gateway is suitable when your network or infrastructure team has a policy on limiting VPC peering connections and enforces all new connections between VPCs through the transit gateway.

The Transit Gateway deployment model only works if the two AWS accounts are in the same organization and Region. This method also works if you have two transit gateway in different region that are peered.

Complete the following steps to create a network path between the QuickSight VPC and the data source VPC:

1. Share the transit gateway with the AWS account where QuickSight is deployed.
2. Run the following command to attach the QuickSight VPC to the shared transit gateway:

   aws ec2 create-transit-gateway-vpc-attachment \
       --transit-gateway-id transit gateway id \
       --vpc-id quicksight vpc id \
       --subnet-id quicksight subnet id \
       --options {"DnsSupport": "enable"}

3. Run the following commands to accept the transit gateway attachment you created:

   aws ec2 accept-transit-gateway-vpc-attachment \
       --transit-gateway-attachment-id your transit gateway attachment id

   Now you need to update the transit gateway attachment with same route table that is used by the data source VPC transit gateway attachment.

4. Get the route table ID (TransitGatewayRouteTableId) with the following command:

   aws ec2 describe-transit-gateway-attachments \
       --transit-gateway-attachment-ids your transit gateway id

5. Run the following command to attach the route table to the transit gateway attachment of QuickSight:

   aws ec2 associate-transit-gateway-route-table \
       --transit-gateway-route-table-id your transit gateway id \
       --transit-gateway-attachment-id your transit gateway attachment id

   Lastly, we update the security groups for both QuickSight and the data source to allow traffic.

6. In the AWS account where QuickSight is deployed, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id quicksight security group \
       --ip-permissions \ 
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=data source subnet CIDR}]

7. In the AWS account where the data source is deployed, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id data source security group \
       --ip-permissions \
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=quicksight subnet CIDR}]
   

   You have now created and configured a network path between QuickSight and your data source. The following diagram shows the infrastructure you deployed.

   

   You can skip the next sections and proceed to connecting QuickSight to the data source.

AWS PrivateLink

AWS PrivateLink provides private connectivity between VPCs, AWS services, and your on-premises networks. It simplifies your network architecture and makes it easy to connect AWS services across different accounts and VPCs.

You can use AWS PrivateLink as a self-managed endpoint to connect two VPCs or as a managed VPC endpoint for some services like Amazon Redshift. With an Amazon Redshift-managed VPC endpoint, you can privately access your Amazon Redshift data warehouse in your VPC from your client applications in another VPC within the same AWS account or another AWS account.

A self-managed AWS PrivateLink deployment is a solution for cross-account access; however, I don’t discuss it in this post. The solution relies on the IP of the data source ENI, which may change, which causes the AWS PrivateLink endpoint to lose connectivity to the data source, because the AWS PrivateLink Network Load Balancer uses IP-as-a-target to refer to the data source. This results in QuickSight being unable to refresh the dashboards and reports until you update the AWS PrivateLink configuration with the new IP.

The following steps walk you through creating a network path—using an Amazon Redshift managed VPC endpoint—between the QuickSight VPC and the data source VPC. This method only works with the RA3-instance of Amazon Redshift and not the DS2 instances.

1. Set up an Amazon Redshift-managed VPC endpoint between the Amazon Redshift cluster VPC and QuickSight VPC. For instructions, see Connecting to Amazon Redshift using an interface VPC endpoint.
   Then you update the security groups for both QuickSight and Amazon Redshift to allow traffic.
2. In the AWS account where QuickSight is deployed, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id quicksight security group \
       --ip-permissions \ 
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=data source subnet CIDR}]

3. In the AWS account where the data source is deployed, run the following command:

   aws ec2 authorize-security-group-ingress \
       --group-id data source security group \
       --ip-permissions \
   IpProtocol=tcp,FromPort=0,ToPort=65535,IpRanges=[{CidrIp=quicksight subnet CIDR}]
   

   You have now created and configured a network path between QuickSight and your Amazon Redshift cluster. The following diagram shows the infrastructure you deployed.

   

   You can skip the next section and proceed to connecting QuickSight to the data source.

VPC sharing

VPC sharing allows multiple AWS accounts to create their application resources, such as Amazon Elastic Compute Cloud (Amazon EC2) or RDS instances, into a shared, centrally managed VPC. In this model, the account that owns the VPC where the data source is deployed (owner) shares one or more subnets with other accounts (participants) that belong to the same organization. After a subnet is shared, the participants can use QuickSight to create the VPC connection in a subnet shared with them. Participants can’t view, modify, or delete resources that belong to other participants or the VPC owner.

This model is the most cost-effective because there’s no underlying cost for VPC peering, Transit Gateway, or AWS PrivateLink. It also simplifies network topologies and reduces the number of VPCs that you create and manage, while using separate accounts for billing and access control for each service used.

The following steps walk you through creating a VPC share and deploying a QuickSight ENI in the VPC to connect to the data source.

1. Share the data source VPC with the AWS account where QuickSight is deployed. For instructions, see Work with shared VPCs.
2. Configure QuickSight to create a VPC connection in the VPC you shared. For instructions, see Setting Up a VPC to Use with Amazon QuickSight.

You have now created and configured a network path between QuickSight and your data source. The following diagram shows the infrastructure you deployed.

You can now connect QuickSight to the data source.

Connect QuickSight to the data source

Now that you have established the network link and configured both QuickSight and the data source to accept incoming and outgoing network traffic, you set up QuickSight to connect to the data source.

1. On the QuickSight console, choose Datasets in the navigation pane.
2. Choose Add a dataset.
3. Choose your database engine (such as PostgreSQL, MySQL or Redshift Manual connect).
   Do not choose Amazon RDS or Amazon Redshift auto-discover.
4. For Connection type, choose the VPC connection you created.
5. Provide the necessary information about your database server.
6. Choose Validate connection button to make sure QuickSight can connect to the data source.
7. Choose Create data source.
   
8. Choose the database you want to use and select the table.

You’re now ready to build your dashboards and reports.

Clean up

After following these steps, you should have successfully connected QuickSight to your data source. To avoid incurring any extra charges from the VPC configurations you made and depending on the use case you followed, you should delete the VPC peering, AWS PrivateLink endpoint, or transit gateway attachment.

Conclusion

In this post, we walked through reference access patterns that connect QuickSight to either Amazon RDS or Amazon Redshift running either on a different account, Region, or both. These methods aren’t limited to Amazon RDS or Amazon Redshift; you can use them with any database engine that is self-managed on Amazon EC2 and supported by QuickSight.

The following table summarizes the different deployment models and the preferred way to connect QuickSight to the data source.

After you set up your various data sources, you can join your data across various sources. You can also use these new data sources to gain further insights from your data by setting up ML Insights in QuickSight and setting graphical representations of your data using QuickSight visuals.

About the author

Lotfi Mouhib is a Senior Solutions Architect working for the Public Sector team with Amazon Web Services. He helps public sector customers across EMEA realize their ideas, build new services, and innovate for citizens. In his spare time, Lotfi enjoys cycling and running.