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

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

You possibly can deal with these challenges by utilizing Amazon Redshift Serverless endpoints behind an AWS Community Load Balancer with Microsoft Entra ID federation. This structure can authenticate whereas serving to to streamline id administration throughout your knowledge setting. Amazon Redshift Serverless supplies petabyte-scale analytics with auto scaling capabilities, enabling high-concurrency workloads whereas streamlining consumer authentication and authorization.

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

Resolution overview

The next diagram exhibits the structure.

Determine 1: Pattern structure diagram

On this structure:

• A central Amazon Redshift ETL knowledge warehouse shares knowledge to a number of Amazon Redshift Serverless workgroups utilizing Amazon Redshift knowledge sharing.
• Every workgroup has a devoted managed Amazon Digital Personal 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 way of 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 at the moment or planning to scale to a number of workgroups sooner or later.

Conditions

Earlier than you start, just remember to have accomplished these conditions.

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 website identify.
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 Consumer 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, establishing an ACM certificates, creating customized domains in Amazon Redshift, configuring DNS information in Amazon Route 53, and connecting your JDBC and ODBC shoppers utilizing Microsoft Entra ID authentication.

1. Configure the Community Load Balancer

First, gather the personal IP addresses in your Amazon Redshift-managed VPC endpoints:

1. Open the Amazon Redshift Serverless console.
2. Select your workgroup.
3. Word the personal 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 kind, select IP addresses.
   • For Goal group identify, enter rs-multicluster-tg.
   • For Protocol, select TCP.
   • For Port, enter 5439 (Word: Yow will discover your particular port quantity within the Redshift endpoint connection particulars. In case 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. Substitute it with your personal area identify 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 advocate including particular person Amazon Redshift Serverless CNAMEs as a substitute of utilizing wildcards (*). This instance makes use of DNS validation in AWS Certificates Supervisor, which requires creating CNAME information to show area management.

   

   Determine 6: AWS Certificates Supervisor (ACM) certificates creation

3. Validate the certificates:
   • Your AWS Certificates Supervisor (ACM) certificates initially exhibits a ‘Pending validation’ standing.
   • Anticipate the standing to vary to ‘Issued’ earlier than continuing.
   • You have to 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 identify:
   • Open the Amazon Redshift Serverless console.
   • Choose your workgroup.
   • From Actions, select Create customized area identify.
   

   Determine 8: Amazon Redshift customized area identify creation

2. Configure the area settings:
   • For Customized area identify, 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 identify creation

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

   Determine 10: Amazon Redshift customized area identify

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 identify 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 identify to the proper Amazon Redshift endpoint, making it reachable by identify fairly than a system-generated deal with. With out it, shoppers 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 report in your Community Load Balancer:

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

   Determine 11: NLB DNS identify

2. Create Route 53 information:
   • Open the Amazon Route 53 console.
   • Select Hosted Zones.
   • Choose myexampledomain.com.
   • Select Create report.
   • Configure the report:
     • For File identify, enter dev-redshift.myexampledomain.com.
     • For File kind, select A – Routes site visitors to an IPv4 deal with and a few AWS sources.
     • For Alias, select Sure.
     • For Route site visitors to, select Alias to Community Load Balancer.
     • Choose your AWS Area and Community Load Balancer DNS identify.
     • For Routing coverage, select Easy routing.
     • Select Create information.
   

   Determine 12: NLB – A report in route 53

   

   Determine 13: NLB – A report 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 identify and CNAME worth.
   • Return to Route 53.
   • Create a CNAME report in your myexampledomain.com hosted zone utilizing the values from AWS Certificates Supervisor (ACM).
   • Select Create information.
   

   Determine 14: NLB – CNAME report in Route 53

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

The JDBC and ODBC driver configuration connects your consumer functions to Amazon Redshift by way of the Community Load Balancer utilizing your Microsoft Entra ID credentials for authentication. Configuring each drivers permits any instrument, whether or not DBeaver utilizing JDBC or Energy BI utilizing ODBC, to authenticate by way of the identical id supplier and attain the proper Amazon Redshift endpoint by way of 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 consumer account.
   

   Determine 15: Amazon Redshift JDBC driver setup

2. Configure driver properties:
   • plugin_name: com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider.
   • sslmode: verify-ca.
3. Add consumer 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 consumer properties

ODBC driver setup

1. Configure the system DSN:
   • Open ODBC Knowledge 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:
   • Knowledge Supply Title: dev-redshift.
   • Server: dev-redshift.myexampledomain.com.
   • Port: 5439.
   • Database: dev.
   • Auth kind: Id Supplier: Browser Azure AD OAUTH2.
   • Scope: [Your Microsoft Entra ID application scope].
   • Azure Consumer 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 way of the Microsoft login in your browser.
3. Confirm that you just obtain a hit message.
4. Verify profitable connection utilizing Native IdP by way of 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 knowledge.
   • 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 Knowledge supply identify.
   • 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 knowledge in Energy BI Desktop.
   

   Figure26: Energy BI desktop linked to Amazon Redshift and schema shopping

   

   Determine 27: Energy BI desktop fetching knowledge from date_dim desk

Cleansing up

To keep away from ongoing prices, delete the next sources:

1. Delete the Amazon Redshift knowledge 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 knowledge analytics use instances develop, you possibly can 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 sources:

AWS Blogs

In regards to the authors