Combine Tableau and Microsoft Entra ID with Amazon Redshift utilizing AWS IAM Id Heart

This publish is co-written with Sid Wray, Jade Koskela, and Ravi Bhattiprolu from SalesForce.

Amazon Redshift and Tableau empower information evaluation. Amazon Redshift is a cloud information warehouse that processes advanced queries at scale and with velocity. Its superior question optimization serves outcomes to Tableau. Tableau’s intensive capabilities and enterprise connectivity assist analysts effectively put together, discover, and share information insights company-wide.

Prospects can combine Amazon Redshift with Tableau utilizing single sign-on (SSO) capabilities enabled by AWS IAM Id Heart integration with trusted id propagation. You should utilize this to seamlessly implement authentication with third-party id suppliers (IdP) and authorization with Redshift. It positions Amazon Redshift as an AWS managed utility, permitting you to take full benefit of the trusted id propagation function.

Amazon Internet Providers (AWS) collaborated with Tableau to allow SSO help for accessing Amazon Redshift from Tableau. Each Tableau Desktop 2023.3.9 and Tableau Server 2023.3.9 releases help trusted id propagation with IAM Id Heart. This SSO integration is out there for Tableau Desktop, Tableau Server, and Tableau Prep.

This weblog publish offers a step-by-step information to integrating IAM Id Heart with Microsoft Entra ID because the IdP and configuring Amazon Redshift as an AWS managed utility. Moreover, you’ll learn to arrange the Amazon Redshift driver in Tableau, enabling SSO immediately inside Tableau Desktop.

Answer overview

The next diagram illustrates the structure of the Tableau SSO integration with Amazon Redshift, IAM Id Heart, and Microsoft Entra ID.

Determine 1: Answer overview for Tableau integration with Amazon Redshift utilizing IAM Id Heart and Microsoft Entra ID

The answer depicted in Determine 1 consists of the next steps:

1. The consumer configures Tableau to entry Amazon Redshift utilizing IAM Id Heart.
2. On a consumer sign-in try, Tableau initiates a browser-based OAuth stream and redirects the consumer to the Microsoft Entra ID sign-in web page to enter the sign-in credentials.
3. After profitable authentication, Microsoft Entra ID points authentication tokens (ID and entry token) to Tableau.
4. The Amazon Redshift driver then makes a name to the Amazon Redshift-enabled Id Heart utility and forwards the entry token.
5. Amazon Redshift passes the token to IAM Id Heart for validation.
6. IAM Id Heart first validates the token utilizing the OpenID Join (OIDC) discovery connection to the trusted token issuer (TTI) and returns an IAM Id Heart generated entry token for a similar consumer. In Determine 1, the TTI is the Microsoft Entra ID server.
7. Amazon Redshift then makes use of the entry token to acquire the consumer and group membership info from Id Heart.
8. The Tableau consumer will be capable to join with Amazon Redshift and entry information based mostly on the consumer and group membership returned from IAM Id Heart.

Conditions

Earlier than you start implementing the answer, you will need to have the next in place:

Walkthrough

On this walkthrough, you’ll use the next steps to construct the answer:

1. Arrange the Microsoft Entra ID OIDC utility
2. Acquire Microsoft Entra ID info
3. Arrange a trusted token issuer in IAM Id Heart
4. Arrange consumer connections and trusted token issuers
5. Arrange the Tableau OAuth config information for Microsoft Entra ID
6. Set up the Tableau OAuth config file for Tableau Desktop
7. Arrange the Tableau OAuth config file for Tableau Server or Tableau Cloud
8. Federate to Amazon Redshift from Tableau Desktop
9. Federate to Amazon Redshift from Tableau Server

Arrange the Microsoft Entra ID OIDC utility

To create your Microsoft Entra utility and repair principal, comply with these steps:

1. Register to the Microsoft Entra admin heart as Cloud Software Administrator (as a minimum).
2. Browse to App registrations underneath Handle, and select New registration.
3. Enter a reputation for the applying. For instance, Tableau-OIDC-App.
4. Choose a supported account kind, which determines who can use the applying. For this instance, choose the primary possibility within the listing.
5. Underneath Redirect URI, choose Internet for the kind of utility you wish to create. Enter the URI the place the entry token is distributed to. On this instance, you’re utilizing localhost, so enter http://localhost:55556/Callback and http://localhost/auth/add_oauth_token.
6. Select Register.
7. Within the navigation pane, select Certificates & secrets and techniques.
8. Select New consumer secret.
9. Enter a Description and choose an expiration for the key or specify a customized lifetime. For this instance, preserve the Microsoft really useful default expiration worth of 6 months. Select Add.
10. Copy the key worth.
    Be aware: It is going to solely be introduced one time; after that you just can not learn it.
11. Within the navigation pane, underneath Handle, select Expose an API.
12. Should you’re establishing for the primary time, you possibly can see Set to the fitting of Software ID URI.
13. Select Set, after which select Save.
14. After the utility ID URI is ready up, select Add a scope.
15. For Scope title, enter a reputation. For instance, redshift_login.
16. For Admin consent show title, enter a show title. For instance, redshift_login.
17. For Admin consent description, enter an outline of the scope.
18. Select Add scope.

For extra details about establishing the Microsoft Entra app, see Register a Microsoft Entra app and create a service principal.

Acquire Microsoft Entra ID info

To configure your IdP with IAM Id Heart and Amazon Redshift, acquire the next parameters from Microsoft Entra ID. Should you don’t have these parameters, contact your Microsoft Entra ID admin.

1. Tenant ID,Consumer ID and Viewers worth: To get these values:
   1. Register to the Azure portal along with your Microsoft account.
   2. Underneath Handle, select App registrations.
   3. Select the applying that you just created in earlier sections.
   4. On the left panel, select Overview, a brand new web page will seem containing the Necessities part. You’ll find the Tenant ID,Consumer ID and Viewers worth (Software ID URI) as proven within the following determine:
      

      Determine 2: Overview part of OIDC utility

2. Scope: To search out your scope worth:
   1. Within the navigation pane of the OIDC utility, underneath Handle, select Expose an API.
   2. You will see that the worth underneath Scopes as proven within the following determine:
      

      Determine 3: Software scope

Arrange a trusted token issuer in IAM Id Heart

At this level, you have got completed configurations within the Entra ID console; now you’re prepared so as to add Entra ID as a TTI. You’ll begin by including a TTI so you possibly can change tokens. On this step, you’ll create a TTI within the centralized administration account. To create a TTI, comply with these steps:

1. Open the AWS Administration Console and navigate to IAM Id Heart, after which to the Settings
2. Choose the Authentication tab and underneath Trusted token issuers, select Create trusted token issuer.
3. On the Arrange an exterior IdP to situation trusted tokens web page, underneath Trusted token issuer particulars, do the next:
   1. For Issuer URL, enter the OIDC discovery URL of the exterior IdP that can situation tokens for trusted id propagation. The URL could be: https://sts.home windows.web//. To search out your Microsoft Entra tenant ID, see Acquire Microsoft Entra ID info.
   2. For Trusted token issuer title, enter a reputation to determine this TTI in IAM Id Heart and within the utility console.
   3. Underneath Map attributes, do the next:
      1. For Id supplier attribute, choose an attribute from the listing to map to an attribute within the Id Heart id retailer. You may select E mail, Object Identifier, Topic, and Different. This instance makes use of Different the place we’re specifying the upn (consumer principal title) because the Id supplier attribute to map with E mail from the IAM id Heart attribute.
      2. For IAM Id Heart attribute, choose the corresponding attribute for the attribute mapping.
   4. Underneath Tags (non-obligatory), select Add new tag, specify a worth for Key, and optionally for Worth. For details about tags, see Tagging AWS IAM Id Heart assets.

Determine 4 that follows exhibits the arrange for TTI.

Determine 4: Create a trusted token issuer

4. Select Create trusted token issuer.

Arrange consumer connections and trusted token issuers

A 3rd-party utility (akin to Tableau) that isn’t managed by AWS exchanges the exterior token (JSON Internet Token (JWT) for an IAM Id Heart token earlier than calling AWS providers.

The JWT should include a topic (sub) declare, an viewers (aud) declare, an issuer (iss), a consumer attribute declare, and a JWT ID (JTI) declare. The viewers is a worth that represents the AWS service that the applying will use, and the viewers declare worth should match the worth that’s configured within the Redshift utility that exchanges the token.

On this part, you’ll specify the viewers declare within the Redshift utility, which you’re going to get from Microsoft Entra ID. You’ll configure the Redshift utility within the member account the place the Redshift cluster or serverless occasion is.

1. Choose IAM Id Heart connection from Amazon Redshift console menu.

Determine 5: Redshift IAM Id Heart connection

2. Choose the Amazon Redshift utility that you just created as a part of the stipulations.
3. Choose the Consumer connections tab and select Edit.
4. Select Sure underneath Configure consumer connections that use third-party IdPs.
5. Choose the checkbox for Trusted token issuer that you just created within the earlier part.
6. Enter the aud declare worth underneath Configure chosen trusted token issuers. For instance, api://1230a234-b456-7890-99c9-a12345bcc123. To get the viewers worth, see Acquire Microsoft Entra ID info.
7. Select Save.

Determine 6: Including an viewers declare for the TTI

Your IAM Id Heart, Amazon Redshift, and Microsoft Entra ID configuration is full. Subsequent, you could configure Tableau.

Arrange the Tableau OAuth config information for Microsoft Entra ID

To combine Tableau with Amazon Redshift utilizing IAM Id Heart, you could use a customized XML. On this step, you employ the next XML and exchange the values beginning with the $ signal and highlighted in daring. The remainder of the values could be saved as they’re, or you possibly can modify them based mostly in your use case. For detailed info on every of the weather within the XML file, see the Tableau documentation on GitHub.

Be aware: The XML file can be used for all of the Tableau merchandise together with Tableau Desktop, Server, and Cloud. You should utilize the next XML or you possibly can consult with Tableau’s github.

redshift

custom_redshift_azure
$copy_client_id_from_azure_oidc_app
$copy_client_secret_from_azure_oidc_app
http://localhost:55556/Callback
http://localhost:55557/Callback
http://localhost:55558/Callback
http://localhost:55559/Callback

https://login.microsoftonline.com/$azure_tenant_id/oauth2/v2.0/authorize
https://login.microsoftonline.com/$azure_tenant_id/oauth2/v2.0/token
openid
offline_access
e-mail

$scope_from_azure_oidc_app


OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT
true


OAUTH_CAP_REQUIRE_PKCE
true


OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD
true


OAUTH_CAP_SUPPORTS_STATE
true


OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM
false


OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN
true



OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL
true




ACCESSTOKEN
access_token


REFRESHTOKEN
refresh_token


access-token-issue-time
issued_at


id-token
id_token


username
e-mail


access-token-expires-in
expires_in

The next is an instance XML file:

redshift

custom_redshift_azure
1230a234-b456-7890-99c9-a12345bcc123
RdQbc~1234559xFX~c65737wOwjsdfdsg123bg2
http://localhost:55556/Callback
http://localhost:55557/Callback
http://localhost:55558/Callback
http://localhost:55559/Callback

https://login.microsoftonline.com/e12a1ab3-1234-12ab-12b3-1a5012221d12/oauth2/v2.0/authorize
https://login.microsoftonline.com/e12a1ab3-1234-12ab-12b3-1a5012221d12/oauth2/v2.0/token
openid
offline_access
e-mail

api://1230a234-b456-7890-99c9-a12345bcc123/redshift_login


OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT
true


OAUTH_CAP_REQUIRE_PKCE
true


OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD
true


OAUTH_CAP_SUPPORTS_STATE
true


OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM
false


OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN
true



OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL
true




ACCESSTOKEN
access_token


REFRESHTOKEN
refresh_token


access-token-issue-time
issued_at


id-token
id_token


username
e-mail


access-token-expires-in
expires_in

Set up the Tableau OAuth config file for Tableau Desktop

After the configuration XML file is created, it have to be copied to a location for use by Amazon Redshift Connector from Tableau Desktop. Save the file from the earlier step as .xml and reserve it underneath DocumentsMy Tableau RepositoryOAuthConfigs.

Be aware: At present, this integration isn’t supported in macOS as a result of the Redshift ODBC 2.X driver isn’t supported but for MAC. It will likely be supported quickly.

Arrange the Tableau OAuth config file for Tableau Server or Tableau Cloud

To combine with Amazon Redshift utilizing IAM Id Heart authentication, you will need to set up the Tableau OAuth config file in Tableau Server or Tableau Cloud.

1. Register to the Tableau Server or Tableau Cloud utilizing admin credentials.
2. Navigate to Settings.
3. Go to OAuth Purchasers Registry and choose Add OAuth Consumer
4. Select following settings:
   1. Connection Sort: Amazon Redshift
   2. OAuth Supplier: Custom_IdP
   3. Consumer Id: Enter your IdP consumer ID worth
   4. Consumer Secret: Enter your consumer secret worth
   5. Redirect URL: Enter http://localhost/auth/add_oauth_token. This instance makes use of localhost for testing in a neighborhood surroundings. It is best to use the total hostname with https.
   6. Select OAuth Config File. Choose the XML file that you just configured within the earlier part.
   7. Choose Add OAuth Consumer and select Save.

Determine 7: Create an OAuth connection in Tableau Server or Cloud

Federate to Amazon Redshift from Tableau Desktop

Now you’re prepared to connect with Amazon Redshift from Tableau as an Entra ID federated consumer. On this step, you create a Tableau Desktop report and publish it to Tableau Server.

1. Open Tableau Desktop.
2. Choose Amazon Redshift Connector and enter the next values:
   1. Server: Enter the title of the server that hosts the database and the title of the database you wish to connect with.
   2. Port: Enter 5439.
   3. Database: Enter your database title. This instance makes use of dev.
   4. Authentication: Choose OAuth.
   5. Federation Sort: Choose Id Heart.
   6. Id Heart Namespace: You may depart this worth clean.
   7. OAuth Supplier: This worth ought to robotically be pulled out of your configured XML. It will likely be the worth from the aspect oauthConfigId.
   8. Choose Require SSL.
   9. Select Register.
      

      Determine 8: Tableau Desktop OAuth connection

3. Enter your IdP credentials within the browser pop-up window.
   

   Determine 9: Microsoft Entra sign up web page

4. When authentication is profitable, you will note the message proven in Determine 10 that follows.
   

   Determine 10: Profitable authentication utilizing Tableau

Congratulations! You’re signed in utilizing the IAM Id Heart integration with Amazon Redshift. Now you’re able to discover and analyze your information utilizing Tableau Desktop.

Determine 11: Profitable connection utilizing Tableau Desktop

After signing in, you possibly can create your individual Tableau Report on the desktop model and publish it to your Tableau Server. For this instance, we created and revealed a report named SalesReport.

Federate to Amazon Redshift from Tableau Server

After you have got revealed the report from Tableau Desktop to Tableau Server, sign up as a non-admin consumer and look at the revealed report (SalesReport on this instance) utilizing IAM Id Heart authentication.

1. Register to the Tableau Server web site as a non-admin consumer.
2. Navigate to Discover and go to the folder the place your revealed report is saved.
3. Choose the report and select Signal In.
   

   Determine 12: Person audit in sys_query_history

4. To authenticate, enter your non-admin Microsoft Entra ID (Azure) credentials within the browser pop-up.
   

   Determine 13: Tableau Server signal In

5. After your authentication is profitable, you possibly can entry the report.
   

   Determine 14: Tableau report

Confirm consumer id from Amazon Redshift

As an non-obligatory step, you possibly can audit the federated IAM Id Heart consumer from Amazon Redshift.

Determine 15 is a screenshot from the Amazon Redshift system desk (sys_query_history) displaying that consumer Ethan from Microsoft Entra ID is accessing the gross sales report.

choose distinct user_id, pg.usename as username, trim(query_text) as query_text
from sys_query_history sys
be part of pg_user_info pg
on sys.user_id=pg.usesysid
the place query_id= and usesysid= and query_type="SELECT"
order by start_time desc
;

Determine 15: Person audit in sys_query_history

Clear up

Full the next steps to wash up your assets:

1. Delete the IdP functions that you just created to combine with IAM Id Heart.
2. Delete the IAM Id Heart configuration.
3. Delete the Amazon Redshift utility and the Amazon Redshift provisioned cluster or serverless occasion that you just created for testing.
4. Delete the AWS Id and Entry Administration (IAM) function and IAM coverage that you just created as a part of the stipulations for IAM Id Heart and Amazon Redshift integration.
5. Delete the permission set from IAM Id Heart that you just created for Amazon Redshift Question Editor V2 within the administration account.

Conclusion

This publish explored a streamlined strategy to entry administration for information analytics through the use of Tableau’s help for OIDC for SSO. The answer facilitates federated consumer authentication, the place consumer identities from an exterior IdP are trusted and propagated to Amazon Redshift. You realized the way to configure Tableau Desktop and Tableau Server to seamlessly combine with Amazon Redshift utilizing IAM Id Heart for SSO. By harnessing this integration between a third-party IdP and IAM Id Heart, customers can securely entry Amazon Redshift information sources inside Tableau with out managing separate database credentials.

The next are key assets to study extra about Amazon Redshift integration with IAM Id Heart:

In regards to the Authors

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an business chief in analytics, utility platform, and database applied sciences, and has greater than 25 years of expertise within the IT world.

Sid Wray is a Senior Product Supervisor at Salesforce based mostly within the Pacific Northwest with practically 20 years of expertise in Digital Promoting, Information Analytics, Connectivity Integration and Id and Entry Administration. He at the moment focuses on supporting ISV companions for Salesforce Information Cloud.

Adiascar Cisneros is a Tableau Senior Product Supervisor based mostly in Atlanta, GA. He focuses on the mixing of the Tableau Platform with AWS providers to amplify the worth customers get from our merchandise and speed up their journey to helpful, actionable insights. His background consists of analytics, infrastructure, community safety, and migrations.

Jade Koskela is a Principal Software program Engineer at Salesforce. He has over a decade of expertise constructing Tableau with a concentrate on areas together with information connectivity, authentication, and id federation.

Harshida Patel is a Principal Options Architect, Analytics with AWS.

Maneesh Sharma is a Senior Database Engineer at AWS with greater than a decade of expertise designing and implementing large-scale information warehouse and analytics options. He collaborates with numerous Amazon Redshift Companions and prospects to drive higher integration.

Ravi Bhattiprolu is a Senior Accomplice Options Architect at AWS. He collaborates with strategic impartial software program vendor (ISV) companions like Salesforce and Tableau to design and ship progressive, well-architected cloud merchandise, integrations, and options to assist joint AWS prospects obtain their enterprise targets.