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

This weblog put up 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 may run complicated queries utilizing refined question optimization to rapidly 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 Middle integration to seamlessly implement authentication and authorization.

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

AWS and Tableau have collaborated to allow single sign-on assist 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 assist trusted identification propagation with IAM Id Middle. This permits customers to seamlessly entry Amazon Redshift information inside Tableau utilizing their exterior IdP credentials with no need to specify AWS Id and Entry Administration (IAM) roles in Tableau. This single sign-on integration is offered for Tableau Desktop, Tableau Server, and Tableau Prep.

On this put up, we define a complete information for organising single sign-on to Amazon Redshift utilizing integration with IAM Id Middle and Okta because the IdP. By following this information, you’ll discover ways to allow seamless single sign-on authentication to Amazon Redshift information sources instantly 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 Middle, and Okta.

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

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

1. The consumer configures Tableau to entry Redshift utilizing IAM Id Middle authentication
2. On a consumer sign-in try, Tableau initiates a browser-based OAuth circulation 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 Middle software and forwards the entry token.
5. Redshift passes the token to Id Middle and requests an entry token.
6. Id Middle verifies/validates the token utilizing the OIDC discovery connection to the trusted token issuer and returns an Id Middle generated entry token for a similar consumer. In Determine 1, Trusted Token Issuer (TTI) is the Okta server that Id Middle trusts to offer tokens that third-party purposes like Tableau makes use of to name AWS providers.
7. Redshift then makes use of the token to acquire the consumer and group membership info from IAM Id Middle.
8. Tableau consumer will be capable of join with Amazon Redshift and entry information primarily based on the consumer and group membership returned from IAM Id Middle.

Conditions

Earlier than you start implementing the answer, just be sure you have the next in place:

Walkthrough

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

• Arrange the Okta OIDC software
• 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 Middle
• 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 software

To create an OIDC internet app in Okta, you may observe 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 check setting. For this setup, you also needs to entry the server at localhost within the browser. In case you resolve to make use of localhost for early testing, additionally, you will must configure the gateway to simply accept localhost utilizing this tsm command:

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

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

1. Check in to your Okta group as a consumer with administrative privileges.
2. On the admin console, underneath Purposes within the navigation pane, select Purposes.
3. Select Create App Integration.
4. Choose OIDC – OpenID Join because the Signal-in methodology and Internet Utility because the Utility sort.
5. Select Subsequent.
6. In Basic Settings:
   1. App integration title: Enter a reputation to 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 have to 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: hold 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 software

7. Within the Basic Settings part, select Edit and choose Require PKCE as further verification underneath Proof Key for Code Alternate (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 Finished.

Determine 4: OIDC software group assignments

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

Arrange the Okta authorization server

Okta permits you to create a number of customized authorization servers that you need to use to guard your personal useful resource servers. Inside every authorization server you may outline your personal OAuth 2.0 scopes, claims, and entry insurance policies. In case you have an Okta Developer Version account, you have already got a customized authorization server created for you known as default.

For this weblog put up, we use the default customized authorization server. In case your software has necessities akin to requiring extra scopes, customizing guidelines for when to grant scopes, otherwise you want extra authorization servers with totally different scopes and claims, then you may observe this information.

Determine 5: Authorization server

Arrange the Okta claims

Tokens comprise claims which might be statements concerning the topic (for instance: title, function, or e mail handle). For this instance, we use the default customized declare sub. Comply with 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 software. The foundations that the coverage incorporates outline totally different entry and refresh token lifetimes relying on the character of the token request. On this instance, you create a easy coverage for all purchasers as proven in Determine 7 that follows. Comply with 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 function depicted in Determine 8 that follows. Comply with this information to create guidelines to your use case.

Determine 8: Entry coverage guidelines

Setup trusted token issuer in AWS IAM Id Middle

At this level, you turn to organising the AWS configuration, beginning by including a trusted token issuer (TTI), which makes it doable to change tokens. This entails connecting IAM Id Middle 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 Middle. 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 Middle, after which to the Settings web page.
2. Choose the Authentication tab and underneath Trusted token issuers, select Create trusted token issuer.
3. On the Arrange an exterior IdP to problem trusted tokens web page, underneath 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 identification 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 underneath the Authorization Servers tab and replica the Issuer URL

Determine 9: Authorization server issuer

4. For Trusted token issuer title, enter a reputation to determine this trusted token issuer in IAM Id Middle and within the software console.
5. Underneath Map attributes, do the next:
   • For Id supplier attribute, choose an attribute from the record to map to an attribute within the IAM Id Middle identification retailer.
   • For IAM Id Middle attribute, choose the corresponding attribute for the attribute mapping.
6. Underneath Tags (optionally available), 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 Middle sources.

This instance makes use of Topic (sub) because the Id supplier attribute to map with Electronic mail from the IAM identification Middle attribute. Determine 10 that follows exhibits 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 change externally generated tokens have to be configured to make use of the TTI you created within the earlier step. Additionally, the viewers declare (or aud declare) from Okta have to be specified. On this instance, you might be configuring the Amazon Redshift software within the member account the place the Amazon Redshift cluster or serverless occasion exists.

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

Determine 11: Amazon Redshift IAM Id Middle connection

2. Choose the Amazon Redshift software that you simply created as a part of the stipulations.
3. Choose the Consumer connections tab and select Edit.
4. Select Sure underneath Configure shopper connections that use third-party IdPs.
5. Choose the checkbox for Trusted token issuer which you might have created within the earlier part.
6. Enter the aud declare worth underneath 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 underneath the Authorization Servers tab and replica 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 software 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 Middle, Amazon Redshift, and Okta configuration are full. Subsequent, you should configure Tableau.

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

Word: The XML file will probably 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 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 underneath 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. It is going to be supported quickly.

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

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

1. Check in to the Tableau Server or Tableau Cloud utilizing admin credentials.
2. Navigate to Settings.
3. Go to OAuth Shoppers Registry and choose Add OAuth Consumer
4. Select following settings:
   • Connection Kind: Amazon Redshift
   • OAuth Supplier: Custom_IdP
   • Consumer ID: Enter your IdP shopper ID worth
   • Consumer 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 setting. 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 Consumer 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 Middle 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 title of the server that hosts the database and the title of the database you need 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 Kind: Choose Id Middle.
   6. Id Middle Namespace: You possibly can go away this worth clean.
   7. OAuth Supplier: This worth ought to routinely be pulled out of your configured XML. It is going to be the worth from the factor oauthConfigId.
   8. Choose Require SSL.
   9. Select Check in.

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 note the message proven in Determine 17 that follows.

Determine 17: Profitable authentication utilizing Tableau

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

Determine 18: Efficiently linked 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: Consumer audit in sys_query_history

After signing in, you may 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 might have 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 Middle authentication.

1. Check in to the Tableau Server website 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 may entry the report.

Determine 22: Tableau report

Clear up

Full the next steps to scrub up your sources:

1. Delete the IdP purposes that you’ve created to combine with IAM Id Middle.
2. Delete the IAM Id Middle 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 IAM function and IAM coverage that you simply created for IAM Id Middle and Amazon Redshift integration.
5. Delete the permission set from IAM Id Middle that you simply created for Amazon Redshift Question Editor V2 within the administration account.

Conclusion

This put up coated streamlining entry administration for information analytics through the use of Tableau’s functionality to assist single sign-on primarily based 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 Middle for single sign-on. By harnessing this integration of a 3rd social gathering IdP with IAM Id Middle, customers can securely entry Amazon Redshift information sources inside Tableau with out managing separate database credentials.

Listed under are key sources to study extra about Amazon Redshift integration with IAM Id Middle

In regards to 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 primarily based within the Pacific Northwest with almost 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 primarily based 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 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 numerous Amazon Redshift Companions and prospects to drive higher integration.

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