Safe multi-warehouse Amazon Redshift entry behind a Community Load Balancer utilizing Microsoft Entra ID

As information analytics workloads scale, organizations face two challenges. First, they have to ship high-performance analytics at huge scale whereas sustaining safe entry throughout numerous instruments. Second, they have to handle high-concurrency workloads whereas integrating with current identification administration programs.

You’ll be able to deal with these challenges through the use of Amazon Redshift Serverless endpoints behind an AWS Community Load Balancer with Microsoft Entra ID federation. This structure can authenticate whereas serving to to streamline identification administration throughout your information surroundings. Amazon Redshift Serverless offers petabyte-scale analytics with auto scaling capabilities, enabling high-concurrency workloads whereas streamlining person authentication and authorization.

On this submit, we present you how you can configure a local identification supplier (IdP) federation for Amazon Redshift Serverless utilizing Community Load Balancer. You’ll learn to allow safe connections from instruments like DBeaver and Energy BI whereas sustaining your enterprise safety requirements.

Resolution overview

The next diagram reveals the structure.

Determine 1: Pattern structure diagram

On this structure:

• A central Amazon Redshift ETL information warehouse shares information to a number of Amazon Redshift Serverless workgroups utilizing Amazon Redshift information sharing.
• Every workgroup has a devoted managed Amazon Digital Non-public Cloud (Amazon VPC) endpoint.
• A Community Load Balancer sits in entrance of all VPC endpoints, offering a single connection level.
• Customers join from DBeaver or Energy BI by the Community Load Balancer and authenticate utilizing their Microsoft Entra ID credentials.

This setup works whether or not you’re validating the idea with a single workgroup in the present day or planning to scale to a number of workgroups sooner or later.

Conditions

Earlier than you start, just be sure you have accomplished these stipulations.

1. Create Amazon Redshift Serverless endpoints.
2. Arrange datashare from producer to Amazon Redshift Serverless endpoints.
3. Create Amazon Redshift-managed VPC endpoints.
4. Create a Community Load Balancer.
5. Configure a site title.
6. Arrange Amazon Redshift native IdP federation with Microsoft Entra ID.
7. Collect the next out of your registered utility in Microsoft Entra ID:
   1. Scope (API-Scope)
   2. Azure Shopper ID (AppID from App Registration Particulars)
   3. IdP Tenant (Tenant ID from App Registration Particulars)
8. Obtain and set up the newest Amazon Redshift JDBC and ODBC drivers.

This resolution makes use of the next AWS companies.

Implementation steps

This part covers configuring the Community Load Balancer, organising an ACM certificates, creating customized domains in Amazon Redshift, configuring DNS data in Amazon Route 53, and connecting your JDBC and ODBC purchasers utilizing Microsoft Entra ID authentication.

1. Configure the Community Load Balancer

First, accumulate the non-public IP addresses in your Amazon Redshift-managed VPC endpoints:

1. Open the Amazon Redshift Serverless console.
2. Select your workgroup.
3. Notice the non-public IP deal with of your Redshift-managed VPC endpoint.
4. Repeat for every Amazon Redshift Serverless endpoint that you just need to add to the Community Load Balancer.
   

   Determine 2: Amazon Redshift managed VPC endpoint

Subsequent, create a goal group in your endpoints:

1. Open the Amazon Elastic Compute Cloud (Amazon EC2) console.
2. Select Goal Teams.
3. Select Create goal group.
4. Configure the goal group:
   • For Goal sort, select IP addresses.
   • For Goal group title, enter rs-multicluster-tg.
   • For Protocol, select TCP.
   • For Port, enter 5439 (Notice: You will discover your particular port quantity within the Redshift endpoint connection particulars. When you haven’t modified it, use the default port 5439.).
   • For VPC, choose your VPC.
   • Select Subsequent.
   

   Determine 3: create goal group in NLB

   

   Determine 4: NLB goal group creation

Add a listener to your Community Load Balancer:

1. Within the EC2 console, select Load Balancers.
2. Choose your Community Load Balancer.
3. Within the Listeners tab, select Add listener.
4. Configure the listener:
   • For Protocol, select TCP.
   • For Port, enter 5439.
   • For Default motion, select rs-multicluster-tg.
5. Select Add listener.
   

   Determine 5: NLB listener properties.

2. Configure AWS Certificates Supervisor (ACM)

For this instance, we use myexampledomain.com as a customized area. Exchange it with your personal area title earlier than you start.Observe these steps to request and configure your certificates:

1. Request a certificates in AWS Certificates Supervisor (ACM):
   • Open the AWS Certificates Supervisor console.
   • Select Request Certificates.
   • Select Request Public certificates.
   • Select Subsequent.
2. Configure the certificates:
   • Add two domains:
     • Community Load Balancer CNAME: dev-redshift.myexampledomain.com
     • Wildcard area: *.redshift.myexampledomain.com
   • For Validation methodology, select DNS validation.
   • Select Request.

   For enhanced safety, we suggest including particular person Amazon Redshift Serverless CNAMEs as an alternative of utilizing wildcards (*). This instance makes use of DNS validation in AWS Certificates Supervisor, which requires creating CNAME data to show area management.

   

   Determine 6: AWS Certificates Supervisor (ACM) certificates creation

3. Validate the certificates:
   • Your AWS Certificates Supervisor (ACM) certificates initially reveals a ‘Pending validation’ standing.
   • Look ahead to the standing to alter to ‘Issued’ earlier than continuing.
   • You could have an ‘Issued’ standing earlier than creating Amazon Redshift customized domains.
   

   Determine 7: Pattern issued AWS Certificates Supervisor (ACM) certificates

3. Configure Amazon Redshift customized domains

1. Create a customized area title:
   • Open the Amazon Redshift Serverless console.
   • Choose your workgroup.
   • From Actions, select Create customized area title.
   

   Determine 8: Amazon Redshift customized area title creation

2. Configure the area settings:
   • For Customized area title, enter cluster-02.redshift.myexampledomain.com.
   • For ACM certificates, choose the certificates you created for dev-redshift.myexampledomain.com.
   • Select Create.
   

   Determine 9: Amazon Redshift customized area title creation

3. Confirm that the customized area title seems in your workgroup.
   

   Determine 10: Amazon Redshift customized area title

4. Repeat steps 1–3 for every remaining Amazon Redshift Serverless endpoint that you just need to add to the Community Load Balancer. Use a novel customized area title for every endpoint (for instance, cluster-03.redshift.myexampledomain.com, cluster-04.redshift.myexampledomain.com) and choose the identical ACM certificates that you just created earlier.

4. Configure Amazon Route 53

Amazon Route 53 maps your customized area title to the right Amazon Redshift endpoint, making it reachable by title moderately than a system-generated deal with. With out it, purchasers haven’t any option to resolve your customized area and AWS Certificates Supervisor can’t confirm area possession to allow safe connections.First, create a CNAME document in your Community Load Balancer:

1. Get the Community Load Balancer DNS title:
   • Open the Amazon EC2 console.
   • Select Load Balancers.
   • Choose your Community Load Balancer.
   • Copy the DNS title.
   

   Determine 11: NLB DNS title

2. Create Route 53 data:
   • Open the Amazon Route 53 console.
   • Select Hosted Zones.
   • Choose myexampledomain.com.
   • Select Create document.
   • Configure the document:
     • For Document title, enter dev-redshift.myexampledomain.com.
     • For Document sort, select A – Routes visitors to an IPv4 deal with and a few AWS assets.
     • For Alias, select Sure.
     • For Route visitors to, select Alias to Community Load Balancer.
     • Choose your AWS Area and Community Load Balancer DNS title.
     • For Routing coverage, select Easy routing.
     • Select Create data.
   

   Determine 12: NLB – A document in route 53

   

   Determine 13: NLB – A document in Route 53

3. Create the AWS Certificates Supervisor (ACM) validation CNAME:
   • Open AWS Certificates Supervisor.
   • Choose your certificates for dev-redshift.myexampledomain.com.
   • Copy the CNAME title and CNAME worth.
   • Return to Route 53.
   • Create a CNAME document in your myexampledomain.com hosted zone utilizing the values from AWS Certificates Supervisor (ACM).
   • Select Create data.
   

   Determine 14: NLB – CNAME document in Route 53

5. Configure Amazon Redshift JDBC and ODBC drivers with native IdP

The JDBC and ODBC driver configuration connects your consumer purposes to Amazon Redshift by the Community Load Balancer utilizing your Microsoft Entra ID credentials for authentication. Configuring each drivers permits any software, whether or not DBeaver utilizing JDBC or Energy BI utilizing ODBC, to authenticate by the identical identification supplier and attain the right Amazon Redshift endpoint by a single connection level.

JDBC driver setup in DBeaver

1. Create a brand new Amazon Redshift connection:
   • Host: dev-redshift.myexampledomain.com (NLB CNAME).
   • Database: dev.
   • Authentication: Database Native.
   • Username: login id for a person account.
   

   Determine 15: Amazon Redshift JDBC driver setup

2. Configure driver properties:
   • plugin_name: com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider.
   • sslmode: verify-ca.
3. Add person driver properties:
   • client_id: [Your Microsoft Entra ID application client ID].
   • idp_tenant: [Your Microsoft Entra ID tenant].
   • listen_port: 7890.
   • loginTimeout: 60.
   • scope: [Your Microsoft Entra ID application scope].
   

   Determine 16: Amazon Redshift JDBC driver person properties

ODBC driver setup

1. Configure the system DSN:
   • Open ODBC Information Supply Administrator (64-bit).
   • Select System DSN.
   • Select Add.
   • Choose Amazon Redshift ODBC Driver (x64) 2.01.04.00.
   • Select End.
     
     
2. Configure connection settings:
   • Information Supply Title: dev-redshift.
   • Server: dev-redshift.myexampledomain.com.
   • Port: 5439.
   • Database: dev.
   • Auth sort: Identification Supplier: Browser Azure AD OAUTH2.
   • Scope: [Your Microsoft Entra ID application scope].
   • Azure Shopper ID: [Your Microsoft Entra ID application client ID].
   • IdP Tenant: [Your Microsoft Entra ID application tenant].
   

   Determine 17: Amazon Redshift ODBC driver properties

3. Configure SSL settings:
   • SSL Mode: verify-ca.
   • Select Save.
   

   Determine 18: Amazon Redshift ODBC driver properties

6. Validate connectivity

Check DBeaver connection

1. After configuring the JDBC driver properties, select Check Connection.
2. Authenticate by the Microsoft login in your browser.
3. Confirm that you just obtain successful message.
4. Affirm profitable connection utilizing Native IdP by the Community Load Balancer.

Determine 19: Microsoft Entra id authentication

Determine 20: Profitable Microsoft Entra id authentication

Determine 21: Profitable Amazon Redshift authentication

Check energy BI desktop connection

1. Launch Energy BI Desktop:
   • Select Get information.
   • Select Extra.
   • Beneath Different, choose ODBC.
   • Select Join.
   

   Determine 22: Energy BI desktop connectivity utilizing Amazon Redshift ODBC driver

   

   Determine 23: Energy BI desktop connectivity utilizing Amazon Redshift ODBC driver

2. Configure the connection:
   • Choose dev-redshift from the Information supply title.
   • Select OK.
   • Full Microsoft Entra ID authentication in your browser.
   

   Determine 24: Energy bi desktop connectivity utilizing Amazon Redshift odbc driver

   

   Determine 25: Profitable Microsoft Entra id authentication

3. Check the connection:
   • From Navigator, select schema tpcds.
   • Choose date_dim.
   • Select Load.
   • Confirm you can analyze your Amazon Redshift information in Energy BI Desktop.
   

   Figure26: Energy BI desktop related to Amazon Redshift and schema searching

   

   Determine 27: Energy BI desktop fetching information from date_dim desk

Cleansing up

To keep away from ongoing costs, delete the next assets:

1. Delete the Amazon Redshift information warehouses (provisioned cluster or serverless workgroup and namespace) and the VPC endpoints that you just created.
2. Delete the certificates that you just created in AWS Certificates Supervisor (ACM).
3. Delete the Community Load Balancer.

Conclusion

On this submit, we confirmed you how you can combine Amazon Redshift Serverless with Microsoft Entra ID utilizing an AWS Community Load Balancer as a single connection endpoint throughout a number of workgroups. As your information analytics use circumstances develop, you’ll be able to proceed to scale horizontally by including new workgroups behind the identical Community Load Balancer with out altering your customers’ connection settings or authentication expertise.

For extra details about extending and scaling this resolution, see the next assets:

AWS Blogs

Concerning the authors