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

This put up 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 pace. 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.

Clients can combine Amazon Redshift with Tableau utilizing single sign-on (SSO) capabilities enabled by AWS IAM Identity Center integration with trusted identity propagation. You should use this to seamlessly implement authentication with third-party identity providers (IdP) and authorization with Redshift. It positions Amazon Redshift as an AWS managed application, permitting you to take full benefit of the trusted identity propagation feature.

Amazon Web Services (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 identification propagation with IAM Identification Heart. This SSO integration is out there for Tableau Desktop, Tableau Server, and Tableau Prep.

This weblog put up supplies a step-by-step information to integrating IAM Identification Heart with Microsoft Entra ID because the IdP and configuring Amazon Redshift as an AWS managed software. Moreover, you’ll learn to arrange the Amazon Redshift driver in Tableau, enabling SSO straight inside Tableau Desktop.

Resolution overview

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

Determine 1: Resolution overview for Tableau integration with Amazon Redshift utilizing IAM Identification 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 Identification 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 Identification Heart software and forwards the entry token.
5. Amazon Redshift passes the token to IAM Identification Heart for validation.
6. IAM Identification Heart first validates the token utilizing the OpenID Join (OIDC) discovery connection to the trusted token issuer (TTI) and returns an IAM Identification 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 Identification 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 Identification Heart.

Conditions

Earlier than you start implementing the answer, you have 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 software
2. Accumulate Microsoft Entra ID info
3. Arrange a trusted token issuer in IAM Identification Heart
4. Arrange consumer connections and trusted token issuers
5. Arrange the Tableau OAuth config recordsdata 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 software

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

1. Sign up to the Microsoft Entra admin center as Cloud Application Administrator (in any case).
2. Browse to App registrations beneath 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 checklist.
5. Beneath Redirect URI, choose Internet for the kind of software you wish to create. Enter the URI the place the entry token is shipped 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, hold the Microsoft beneficial default expiration worth of 6 months. Select Add.
10. Copy the key worth.
    Observe: It should solely be introduced one time; after that you simply can’t learn it.
11. Within the navigation pane, beneath Handle, select Expose an API.
12. When you’re establishing for the primary time, you may see Set to the best of Utility ID URI.
13. Select Set, after which select Save.
14. After the software ID URI is ready up, select Add a scope.
15. For Scope identify, enter a reputation. For instance, redshift_login.
16. For Admin consent show identify, enter a show identify. 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.

Accumulate Microsoft Entra ID info

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

1. Tenant ID,Shopper ID and Viewers worth: To get these values:
   1. Sign up to the Azure portal together with your Microsoft account.
   2. Beneath Handle, select App registrations.
   3. Select the applying that you simply created in earlier sections.
   4. On the left panel, select Overview, a brand new web page will seem containing the Necessities part. Yow will discover the Tenant ID,Shopper ID and Viewers worth (Utility ID URI) as proven within the following determine:
      

      Determine 2: Overview part of OIDC software

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

      Determine 3: Utility scope

Arrange a trusted token issuer in IAM Identification 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 may trade 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 Identity Center, after which to the Settings
2. Choose the Authentication tab and beneath Trusted token issuers, select Create trusted token issuer.
3. On the Arrange an exterior IdP to concern trusted tokens web page, beneath Trusted token issuer particulars, do the next:
   1. For Issuer URL, enter the OIDC discovery URL of the exterior IdP that can concern tokens for trusted identification propagation. The URL could be: https://sts.home windows.internet/<tenantid>/. To search out your Microsoft Entra tenant ID, see Collect Microsoft Entra ID information.
   2. For Trusted token issuer identify, enter a reputation to determine this TTI in IAM Identification Heart and within the software console.
   3. Beneath Map attributes, do the next:
      1. For Identification supplier attribute, choose an attribute from the checklist to map to an attribute within the Identification Heart identification retailer. You’ll be able to select E mail, Object Identifier, Topic, and Different. This instance makes use of Different the place we’re specifying the upn (consumer principal identify) because the Identification supplier attribute to map with E mail from the IAM identification Heart attribute.
      2. For IAM Identification Heart attribute, choose the corresponding attribute for the attribute mapping.
   4. Beneath Tags (elective), select Add new tag, specify a worth for Key, and optionally for Worth. For details about tags, see Tagging AWS IAM Identity Center resources.

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 software (equivalent to Tableau) that isn’t managed by AWS exchanges the exterior token (JSON Internet Token (JWT) for an IAM Identification Heart token earlier than calling AWS companies.

The JWT should comprise 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 software that exchanges the token.

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

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

Determine 5: Redshift IAM Identification Heart connection

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

Determine 6: Including an viewers declare for the TTI

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

Arrange the Tableau OAuth config recordsdata for Microsoft Entra ID

To combine Tableau with Amazon Redshift utilizing IAM Identification Heart, you should use a customized XML. On this step, you utilize the next XML and exchange the values beginning with the $ signal and highlighted in daring. The remainder of the values may be stored as they’re, or you may 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.

Observe: The XML file shall be used for all of the Tableau merchandise together with Tableau Desktop, Server, and Cloud. You should use the next XML or you may check with Tableau’s github.

<?xml model="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
<dbclass>redshift</dbclass>
<!-- For configs embedded within the connector package deal, do not prefix with "custom_". For exterior configs, all the time prefix with "custom_". -->
<oauthConfigId>custom_redshift_azure</oauthConfigId>
<clientIdDesktop>$copy_client_id_from_azure_oidc_app</clientIdDesktop>
<clientSecretDesktop>$copy_client_secret_from_azure_oidc_app</clientSecretDesktop>
<redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55559/Callback</redirectUrisDesktop>
<!-- For multitenant apps use the frequent endpoint, for single tenant apps use the listing particular endpoint. -->
<authUri>https://login.microsoftonline.com/$azure_tenant_id/oauth2/v2.0/authorize</authUri>
<tokenUri>https://login.microsoftonline.com/$azure_tenant_id/oauth2/v2.0/token</tokenUri>
<scopes>openid</scopes>
<scopes>offline_access</scopes>
<scopes>e mail</scopes>
<!-- An instance with a customized API, which was required on the time of writing for integration with AWS IAM IDC. -->
<scopes>$scope_from_azure_oidc_app</scopes>
<capabilities>
<entry>
<key>OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_REQUIRE_PKCE</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_STATE</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM</key>
<worth>false</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</key>
<worth>true</worth>
</entry>
<!-- Relying on the Azure software, dynamic ports will not be allowed. Allow this if not allowed. -->
<entry>
<key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
<worth>true</worth>
</entry>
</capabilities>
<accessTokenResponseMaps>
<entry>
<key>ACCESSTOKEN</key>
<worth>access_token</worth>
</entry>
<entry>
<key>REFRESHTOKEN</key>
<worth>refresh_token</worth>
</entry>
<entry>
<key>access-token-issue-time</key>
<worth>issued_at</worth>
</entry>
<entry>
<key>id-token</key>
<worth>id_token</worth>
</entry>
<entry>
<key>username</key>
<worth>e mail</worth>
</entry>
<entry>
<key>access-token-expires-in</key>
<worth>expires_in</worth>
</entry>
</accessTokenResponseMaps>
</pluginOAuthConfig>

The next is an instance XML file:

<?xml model="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
<dbclass>redshift</dbclass>
<!-- For configs embedded within the connector package deal, do not prefix with "custom_". For exterior configs, all the time prefix with "custom_". -->
<oauthConfigId>custom_redshift_azure</oauthConfigId>
<clientIdDesktop>1230a234-b456-7890-99c9-a12345bcc123</clientIdDesktop>
<clientSecretDesktop>RdQbc~1234559xFX~c65737wOwjsdfdsg123bg2</clientSecretDesktop>
<redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55559/Callback</redirectUrisDesktop>
<!-- For multitenant apps use the frequent endpoint, for single tenant apps use the listing particular endpoint. -->
<authUri>https://login.microsoftonline.com/e12a1ab3-1234-12ab-12b3-1a5012221d12/oauth2/v2.0/authorize</authUri>
<tokenUri>https://login.microsoftonline.com/e12a1ab3-1234-12ab-12b3-1a5012221d12/oauth2/v2.0/token</tokenUri>
<scopes>openid</scopes>
<scopes>offline_access</scopes>
<scopes>e mail</scopes>
<!-- An instance with a customized API, which was required on the time of writing for integration with AWS IAM IDC. -->
<scopes>api://1230a234-b456-7890-99c9-a12345bcc123/redshift_login</scopes>
<capabilities>
<entry>
<key>OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_REQUIRE_PKCE</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_STATE</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM</key>
<worth>false</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</key>
<worth>true</worth>
</entry>
<!-- Relying on the Azure software, dynamic ports will not be allowed. Allow this if not allowed. -->
<entry>
<key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
<worth>true</worth>
</entry>
</capabilities>
<accessTokenResponseMaps>
<entry>
<key>ACCESSTOKEN</key>
<worth>access_token</worth>
</entry>
<entry>
<key>REFRESHTOKEN</key>
<worth>refresh_token</worth>
</entry>
<entry>
<key>access-token-issue-time</key>
<worth>issued_at</worth>
</entry>
<entry>
<key>id-token</key>
<worth>id_token</worth>
</entry>
<entry>
<key>username</key>
<worth>e mail</worth>
</entry>
<entry>
<key>access-token-expires-in</key>
<worth>expires_in</worth>
</entry>
</accessTokenResponseMaps>
</pluginOAuthConfig>

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 put it aside beneath DocumentsMy Tableau RepositoryOAuthConfigs.

Observe: Presently, this integration isn’t supported in macOS as a result of the Redshift ODBC 2.X driver isn’t supported but for MAC. Will probably be supported quickly.

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

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

1. Sign up to the Tableau Server or Tableau Cloud utilizing admin credentials.
2. Navigate to Settings.
3. Go to OAuth Purchasers Registry and choose Add OAuth Shopper
4. Select following settings:
   1. Connection Kind: Amazon Redshift
   2. OAuth Supplier: Custom_IdP
   3. Shopper Id: Enter your IdP consumer ID worth
   4. Shopper 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. You must use the complete hostname with https.
   6. Select OAuth Config File. Choose the XML file that you simply configured within the earlier part.
   7. Choose Add OAuth Shopper 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 identify of the server that hosts the database and the identify of the database you wish to connect with.
   2. Port: Enter 5439.
   3. Database: Enter your database identify. This instance makes use of dev.
   4. Authentication: Choose OAuth.
   5. Federation Kind: Choose Identification Heart.
   6. Identification Heart Namespace: You’ll be able to go away this worth clean.
   7. OAuth Supplier: This worth ought to routinely be pulled out of your configured XML. Will probably be the worth from the ingredient oauthConfigId.
   8. Choose Require SSL.
   9. Select Sign up.
      

      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 Identification 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 may create your own 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 examine the revealed report (SalesReport on this instance) utilizing IAM Identification Heart authentication.

1. Sign up 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 may entry the report.
   

   Determine 14: Tableau report

Confirm consumer identification from Amazon Redshift

As an elective step, you may audit the federated IAM Identification Heart consumer from Amazon Redshift.

Determine 15 is a screenshot from the Amazon Redshift system desk (sys_query_history) exhibiting 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 a part of pg_user_info pg
on sys.user_id=pg.usesysid
the place query_id=<query_id> and usesysid=<federateduser_id> and query_type="SELECT"
order by start_time desc
;

Determine 15: Person audit in sys_query_history

Clear up

Full the next steps to scrub up your assets:

1. Delete the IdP purposes that you simply created to combine with IAM Identification Heart.
2. Delete the IAM Identification Heart configuration.
3. Delete the Amazon Redshift software and the Amazon Redshift provisioned cluster or serverless occasion that you simply created for testing.
4. Delete the AWS Identity and Access Management (IAM) position and IAM coverage that you simply created as a part of the conditions for IAM Identification Heart and Amazon Redshift integration.
5. Delete the permission set from IAM Identification Heart that you simply created for Amazon Redshift Question Editor V2 within the administration account.

Conclusion

This put up 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 discovered the right way to configure Tableau Desktop and Tableau Server to seamlessly combine with Amazon Redshift utilizing IAM Identification Heart for SSO. By harnessing this integration between a third-party IdP and IAM Identification 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 Identification Heart:

Concerning the Authors

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an business chief in analytics, software 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 almost 20 years of expertise in Digital Promoting, Information Analytics, Connectivity Integration and Identification and Entry Administration. He at present 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 combination of the Tableau Platform with AWS companies 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 deal with areas together with information connectivity, authentication, and identification 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 varied Amazon Redshift Companions and clients to drive higher integration.

Ravi Bhattiprolu is a Senior Associate 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 clients obtain their enterprise targets.