Combine Tableau and Okta with Amazon Redshift utilizing AWS IAM Id Heart

This weblog submit is co-written with Sid Wray and Jake Koskela from Salesforce, and Adiascar Cisneros from Tableau. 

Amazon Redshift is a quick, scalable cloud information warehouse constructed to serve workloads at any scale. With Amazon Redshift as your information warehouse, you possibly can run complicated queries utilizing refined question optimization to shortly ship outcomes to Tableau, which affords a complete set of capabilities and connectivity choices for analysts to effectively put together, uncover, and share insights throughout the enterprise. For patrons who need to combine Amazon Redshift with Tableau utilizing single sign-on capabilities, we launched AWS IAM Id Heart integration to seamlessly implement authentication and authorization.

IAM Id Heart supplies capabilities to handle single sign-on entry to AWS accounts and purposes from a single location. Redshift now integrates with IAM Id Heart, and helps trusted id propagation, making it potential to combine with third-party id suppliers (IdP) equivalent to Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin. This integration positions Amazon Redshift as an IAM Id Heart-managed utility, enabling you to make use of database role-based entry management in your information warehouse for enhanced safety. Position-based entry management lets you apply high-quality grained entry management utilizing row stage, column stage, and dynamic information masking in your information warehouse.

AWS and Tableau have collaborated to allow single sign-on help for accessing Amazon Redshift from Tableau. Tableau now helps single sign-on capabilities with Amazon Redshift connector to simplify the authentication and authorization. The Tableau Desktop 2024.1 and Tableau Server 2023.3.4 releases help trusted id propagation with IAM Id Heart. This permits customers to seamlessly entry Amazon Redshift information inside Tableau utilizing their exterior IdP credentials while not having to specify AWS Id and Entry Administration (IAM) roles in Tableau. This single sign-on integration is out there for Tableau Desktop, Tableau Server, and Tableau Prep.

On this submit, we define a complete information for organising single sign-on to Amazon Redshift utilizing integration with IAM Id Heart and Okta because the IdP. By following this information, you’ll learn to allow seamless single sign-on authentication to Amazon Redshift information sources immediately from inside Tableau Desktop, streamlining your analytics workflows and enhancing safety.

Resolution overview

The next diagram illustrates the structure of the Tableau SSO integration with Amazon RedShift, IAM Id Heart, and Okta.

Determine 1: Resolution overview for Tableau integration with Amazon Redshift utilizing IAM Id Heart and Okta

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

1. The consumer configures Tableau to entry Redshift utilizing IAM Id Heart authentication
2. On a consumer sign-in try, Tableau initiates a browser-based OAuth movement and redirects the consumer to the Okta login web page to enter the login credentials.
3. On profitable authentication, Okta points an authentication token (id and entry token) to Tableau
4. Redshift driver then makes a name to Redshift-enabled IAM Id Heart utility and forwards the entry token.
5. Redshift passes the token to Id Heart and requests an entry token.
6. Id Heart verifies/validates the token utilizing the OIDC discovery connection to the trusted token issuer and returns an Id Heart generated entry token for a similar consumer. In Determine 1, Trusted Token Issuer (TTI) is the Okta server that Id Heart trusts to supply tokens that third-party purposes like Tableau makes use of to name AWS companies.
7. Redshift then makes use of the token to acquire the consumer and group membership data from IAM Id Heart.
8. 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.

Stipulations

Earlier than you start implementing the answer, just remember to have the next in place:

Walkthrough

On this walkthrough, you construct the answer with following steps:

• Arrange the Okta OIDC utility
• Arrange the Okta authorization server
• Arrange the Okta claims
• Setup the Okta entry insurance policies and guidelines
• Setup trusted token issuer in AWS IAM Id Heart
• Setup shopper connections and trusted token issuers
• Setup the Tableau OAuth config information for Okta
• Set up the Tableau OAuth config file for Tableau Desktop
• Setup the Tableau OAuth config file for Tableau Server or Tableau Cloud
• Federate to Amazon Redshift from Tableau Desktop
• Federate to Amazon Redshift from Tableau Server

Arrange the Okta OIDC utility

To create an OIDC net app in Okta, you possibly can comply with the directions on this video, or use the next steps to create the wep app in Okta admin console:

Word: The Tableau Desktop redirect URLs ought to all the time use localhost. The examples under additionally use localhost for the Tableau Server hostname for ease of testing in a take a look at surroundings. For this setup, you must also entry the server at localhost within the browser. For those who determine to make use of localhost for early testing, additionally, you will have to configure the gateway to simply accept localhost utilizing this tsm command:

 tsm configuration set -k gateway.public.host -v localhost

In a manufacturing surroundings, or Tableau Cloud, you must use the total hostname that your customers will entry Tableau on the net, together with https. If you have already got an surroundings with https configured, you might skip the localhost configuration and use the total hostname from the beginning.

1. Sign up to your Okta group as a consumer with administrative privileges.
2. On the admin console, below Functions within the navigation pane, select Functions.
3. Select Create App Integration.
4. Choose OIDC – OpenID Join because the Signal-in methodology and Net Software because the Software sort.
5. Select Subsequent.
6. In Normal Settings:
   1. App integration identify: Enter a reputation on your app integration. For instance, Tableau_Redshift_App.
   2. Grant sort: Choose Authorization Code and Refresh Token.
   3. Signal-in redirect URIs: The sign-in redirect URI is the place Okta sends the authentication response and ID token for the sign-in request. The URIs should be absolute URIs. Select Add URl and together with the default URl, add the next URIs.
      • http://localhost:55556/Callback
      • http://localhost:55557/Callback
      • http://localhost:55558/Callback
      • http://localhost/auth/add_oauth_token
   4. Signal-out redirect URIs: maintain the default worth as http://localhost:8080.
   5. Skip the Trusted Origins part and for Assignments, choose Skip group task for now.
   6. Select Save.

Determine 2: OIDC utility

7. Within the Normal Settings part, select Edit and choose Require PKCE as extra verification below Proof Key for Code Trade (PKCE). This selection signifies if a PKCE code problem is required to confirm shopper requests.
8. Select Save.

Determine 3: OIDC App Overview

9. Choose the Assignments tab after which select Assign to Teams. On this instance, we’re assigning awssso-finance and awssso-sales.
10. Select Performed.

Determine 4: OIDC utility group assignments

For extra data on creating an OIDC app, see Create OIDC app integrations.

Arrange the Okta authorization server

Okta lets you create a number of customized authorization servers that you should utilize to guard your personal useful resource servers. Inside every authorization server you possibly can outline your personal OAuth 2.0 scopes, claims, and entry insurance policies. You probably have an Okta Developer Version account, you have already got a customized authorization server created for you known as default.

For this weblog submit, we use the default customized authorization server. In case your utility has necessities equivalent to requiring extra scopes, customizing guidelines for when to grant scopes, otherwise you want extra authorization servers with completely different scopes and claims, then you possibly can comply with this information.

Determine 5: Authorization server

Arrange the Okta claims

Tokens comprise claims which can be statements in regards to the topic (for instance: identify, position, or e-mail handle). For this instance, we use the default customized declare sub. Observe this information to create claims.

Determine 6: Create claims

Setup the Okta entry insurance policies and guidelines

Entry insurance policies are containers for guidelines. Every entry coverage applies to a specific OpenID Join utility. The foundations that the coverage accommodates outline completely different entry and refresh token lifetimes relying on the character of the token request. On this instance, you create a easy coverage for all shoppers as proven in Determine 7 that follows. Observe this information to create entry insurance policies and guidelines.

Determine 7: Create entry insurance policies

Guidelines for entry insurance policies outline token lifetimes for a given mixture of grant sort, consumer, and scope. They’re evaluated in precedence order and after an identical rule is discovered, no different guidelines are evaluated. If no matching rule is discovered, then the authorization request fails. This instance makes use of the position depicted in Determine 8 that follows. Observe this information to create guidelines on your use case.

Determine 8: Entry coverage guidelines

Setup trusted token issuer in AWS IAM Id Heart

At this level, you turn to organising the AWS configuration, beginning by including a trusted token issuer (TTI), which makes it potential to trade tokens. This entails connecting IAM Id Heart to the Open ID Join (OIDC) discovery URL of the exterior OAuth authorization server and defining an attribute-based mapping between the consumer from the exterior OAuth authorization server and a corresponding consumer in Id Heart. On this step, you create a TTI within the centralized administration account. To create a TTI:

1. Open the AWS Administration Console and navigate to IAM Id Heart, after which to the Settings web page.
2. Choose the Authentication tab and below Trusted token issuers, select Create trusted token issuer.
3. On the Arrange an exterior IdP to problem trusted tokens web page, below Trusted token issuer particulars, do the next:
   • For Issuer URL, enter the OIDC discovery URL of the exterior IdP that may problem tokens for trusted id propagation. The administrator of the exterior IdP can present this URL (for instance, https://prod-1234567.okta.com/oauth2/default).

To get the issuer URL from Okta, check in as an admin to Okta and navigate to Safety after which to API and select default below the Authorization Servers tab and duplicate the Issuer URL

Determine 9: Authorization server issuer

4. For Trusted token issuer identify, enter a reputation to determine this trusted token issuer in IAM Id Heart and within the utility console.
5. Underneath Map attributes, do the next:
   • For Id supplier attribute, choose an attribute from the checklist to map to an attribute within the IAM Id Heart id retailer.
   • For IAM Id Heart attribute, choose the corresponding attribute for the attribute mapping.
6. Underneath Tags (elective), select Add new tag, enter a worth for Key and optionally for Worth. Select Create trusted token issuer. For details about tags, see Tagging AWS IAM Id Heart assets.

This instance makes use of Topic (sub) because the Id supplier attribute to map with E mail from the IAM id Heart attribute. Determine 10 that follows reveals the arrange for TTI.

Determine 10: Create Trusted Token Issuer

Setup shopper connections and trusted token issuers

On this step, the Amazon Redshift purposes that trade externally generated tokens should be configured to make use of the TTI you created within the earlier step. Additionally, the viewers declare (or aud declare) from Okta should be specified. On this instance, you’re configuring the Amazon Redshift utility within the member account the place the Amazon Redshift cluster or serverless occasion exists.

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

Determine 11: Amazon Redshift IAM Id Heart connection

2. Choose the Amazon Redshift utility that you simply created as a part of the stipulations.
3. Choose the Shopper connections tab and select Edit.
4. Select Sure below Configure shopper connections that use third-party IdPs.
5. Choose the checkbox for Trusted token issuer which you’ve got created within the earlier part.
6. Enter the aud declare worth below part Configure chosen trusted token issuers. For instance, okta_tableau_audience.

To get the viewers worth from Okta, check in as an admin to Okta and navigate to Safety after which to API and select default below the Authorization Servers tab and duplicate the Viewers worth.

Determine 12: Authorization server viewers

Word: The viewers declare worth should precisely match with IdP viewers worth in any other case your OIDC reference to third half utility like Tableau will fail.

7. Select Save.

Determine 13: Including Viewers Declare for Trusted Token Issuer

Setup the Tableau OAuth config information for Okta

At this level, your IAM Id Heart, Amazon Redshift, and Okta configuration are full. Subsequent, you’ll want to configure Tableau.

To combine Tableau with Amazon Redshift utilizing IAM Id Heart, you’ll want to 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 will be stored as they’re, or you possibly can modify them based mostly in your use case. For detailed data on every of the weather within the XML file, see the Tableau documentation on GitHub.

Word: The XML file can be used for all of the Tableau merchandise together with Tableau Desktop, Server, and Cloud.

<?xml model="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
<dbclass>redshift</dbclass>
<oauthConfigId>custom_redshift_okta</oauthConfigId>
<clientIdDesktop>$copy_client_id_from_okta_oidc_app</clientIdDesktop>
<clientSecretDesktop>$copy_client_secret_from_okta_oidc_app</clientSecretDesktop>
<redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
<authUri>https://$copy_okta_host_value.okta.com/oauth2/default/v1/authorize</authUri>
<tokenUri>https://$copy_okta_host_value.okta.com/oauth2/default/v1/token</tokenUri>
<scopes>openid</scopes>
<scopes>e-mail</scopes>
<scopes>profile</scopes>
<scopes>offline_access</scopes>
<capabilities>
<entry>
<key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_REQUIRE_PKCE</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>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</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>id-token</key>
<worth>id_token</worth>
</entry>
<entry>
<key>access-token-issue-time</key>
<worth>issued_at</worth>
</entry>
<entry>
<key>access-token-expires-in</key>
<worth>expires_in</worth>
</entry>
<entry>
<key>username</key>
<worth>preferred_username</worth>
</entry>
</accessTokenResponseMaps>
</pluginOAuthConfig>

The next is an instance XML file:

<?xml model="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
<dbclass>redshift</dbclass>
<oauthConfigId>custom_redshift_okta</oauthConfigId>
<clientIdDesktop>ab12345z-a5nvb-123b-123b-1c434ghi1234</clientIdDesktop>
<clientSecretDesktop>3243jkbkjb~~ewf.112121.3432423432.asd834k</clientSecretDesktop>
<redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
<redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
<authUri>https://prod-1234567.okta.com/oauth2/default/v1/authorize</authUri>
<tokenUri>https://prod-1234567.okta.com/oauth2/default/v1/token</tokenUri>
<scopes>openid</scopes>
<scopes>e-mail</scopes>
<scopes>profile</scopes>
<scopes>offline_access</scopes>
<capabilities>
<entry>
<key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
<worth>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_REQUIRE_PKCE</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>true</worth>
</entry>
<entry>
<key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</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>id-token</key>
<worth>id_token</worth>
</entry>
<entry>
<key>access-token-issue-time</key>
<worth>issued_at</worth>
</entry>
<entry>
<key>access-token-expires-in</key>
<worth>expires_in</worth>
</entry>
<entry>
<key>username</key>
<worth>preferred_username</worth>
</entry>
</accessTokenResponseMaps>
</pluginOAuthConfig>

Set up the Tableau OAuth config file for Tableau Desktop

After the configuration XML file is created, it should 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 below DocumentsMy Tableau RepositoryOAuthConfigs.

Word: 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.

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

To combine with Amazon Redshift utilizing IAM Id Heart authentication, you could 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:
   • Connection Kind: Amazon Redshift
   • OAuth Supplier: Custom_IdP
   • Shopper ID: Enter your IdP shopper ID worth
   • Shopper Secret: Enter your shopper secret worth
   • 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 total hostname with https.
   • Select OAuth Config File. Choose the XML file that you simply configured within the earlier part.
   • Choose Add OAuth Shopper and select Save.

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

Federate to Amazon Redshift from Tableau Desktop

Now you’re prepared to connect with Amazon Redshift from Tableau via federated sign-in utilizing IAM Id Heart authentication. 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 need to hook up 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 Id Heart.
   6. Id Heart Namespace: You’ll be able to depart this worth clean.
   7. OAuth Supplier: This worth ought to robotically be pulled out of your configured XML. Will probably be the worth from the factor oauthConfigId.
   8. Choose Require SSL.
   9. Select Sign up.

Determine 15: Tableau Desktop OAuth connection

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

Determine 16: Okta Login Web page

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

Determine 17: Profitable authentication utilizing Tableau

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

Determine 18: Efficiently related utilizing Tableau Desktop

Determine 19 is a screenshot from the Amazon Redshift system desk (sys_query_history) exhibiting that consumer Ethan from Okta is accessing the gross sales report.

Determine 19: Person audit in sys_query_history

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

Federate to Amazon Redshift from Tableau Server

After you’ve got printed the report from Tableau Desktop to Tableau Server, check in as a non-admin consumer and examine the printed report (SalesReport on this instance) utilizing IAM Id 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 printed report is saved.
3. Choose the report and select Signal In.

Determine 20: Tableau Server Signal In

4. To authenticate, enter your non-admin Okta credentials within the browser pop-up.

Determine 21: Okta Login Web page

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

Determine 22: Tableau report

Clear up

Full the next steps to scrub up your assets:

1. Delete the IdP purposes that you’ve 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 simply created for testing.
4. Delete the IAM position and IAM coverage that you simply created for IAM Id Heart and Amazon Redshift integration.
5. Delete the permission set from IAM Id Heart that you simply created for Amazon Redshift Question Editor V2 within the administration account.

Conclusion

This submit lined streamlining entry administration for information analytics by utilizing Tableau’s functionality to help single sign-on based mostly on the OAuth 2.0 OpenID Join (OIDC) protocol. The answer allows federated consumer authentication, the place consumer identities from an exterior IdP are trusted and propagated to Amazon Redshift. You walked via the steps to configure Tableau Desktop and Tableau Server to combine seamlessly with Amazon Redshift utilizing IAM Id Heart for single sign-on. By harnessing this integration of a 3rd get together IdP with IAM Id Heart, customers can securely entry Amazon Redshift information sources inside Tableau with out managing separate database credentials.

Listed under are key assets to study extra about Amazon Redshift integration with IAM Id Heart

Concerning 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, Knowledge Analytics, Connectivity Integration and Id and Entry Administration. He at present focuses on supporting ISV companions for Salesforce Knowledge 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 worthwhile, 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 varied Amazon Redshift Companions and clients to drive higher integration.

Ravi Bhattiprolu is a Senior Accomplice Options Architect at Amazon Net Providers (AWS). He collaborates with strategic unbiased 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.