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

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’ll be able to run complicated queries utilizing subtle question optimization to rapidly ship outcomes to Tableau, which gives a complete set of capabilities and connectivity choices for analysts to effectively put together, uncover, and share insights throughout the enterprise. For purchasers who need to combine Amazon Redshift with Tableau utilizing single sign-on capabilities, we launched AWS IAM Identification Middle integration to seamlessly implement authentication and authorization.

IAM Identification Middle offers capabilities to handle single sign-on entry to AWS accounts and purposes from a single location. Redshift now integrates with IAM Identification Middle, and helps trusted identification propagation, making it attainable to combine with third-party identification suppliers (IdP) comparable to Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin. This integration positions Amazon Redshift as an IAM Identification Middle-managed software, enabling you to make use of database role-based entry management in your information warehouse for enhanced safety. Function-based entry management lets you 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 Identification Middle. This enables customers to seamlessly entry Amazon Redshift information inside Tableau utilizing their exterior IdP credentials without having to specify AWS Identification 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 Identification 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 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 Identification Middle, and Okta.

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

The answer depicted in Determine 1 contains the next steps:

1. The person configures Tableau to entry Redshift utilizing IAM Identification Middle authentication
2. On a person sign-in try, Tableau initiates a browser-based OAuth circulation and redirects the person 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 Identification Middle software and forwards the entry token.
5. Redshift passes the token to Identification Middle and requests an entry token.
6. Identification Middle verifies/validates the token utilizing the OIDC discovery connection to the trusted token issuer and returns an Identification Middle generated entry token for a similar person. In Determine 1, Trusted Token Issuer (TTI) is the Okta server that Identification Middle trusts to offer 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 person and group membership data from IAM Identification Middle.
8. Tableau person will have the ability to join with Amazon Redshift and entry information based mostly on the person and group membership returned from IAM Identification Middle.

Conditions

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 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 Identification 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 net app in Okta, you’ll be able to observe the directions on this video, or use the next steps to create the wep app in Okta admin console:

Be aware: The Tableau Desktop redirect URLs ought to at all times use localhost. The examples under additionally use localhost for the Tableau Server hostname for ease of testing in a take a look at atmosphere. For this setup, you also needs to entry the server at localhost within the browser. In the event you resolve to make use of localhost for early testing, additionally, you will have to configure the gateway to just accept localhost utilizing this tsm command:

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

In a manufacturing atmosphere, 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 atmosphere with https configured, chances are you’ll skip the localhost configuration and use the total hostname from the beginning.

1. Register to your Okta group as a person with administrative privileges.
2. On the admin console, beneath 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 Common Settings:
   1. App integration identify: Enter a reputation in 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: 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 software

7. Within the Common Settings part, select Edit and choose Require PKCE as further verification beneath Proof Key for Code Alternate (PKCE). This feature 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 Achieved.

Determine 4: OIDC software 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 individual useful resource servers. Inside every authorization server you’ll be able to outline your individual OAuth 2.0 scopes, claims, and entry insurance policies. If 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 submit, we use the default customized authorization server. In case your software has necessities comparable 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’ll be able to observe this information.

Determine 5: Authorization server

Arrange the Okta claims

Tokens include claims which are 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 software. The foundations that the coverage comprises 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. 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, person, 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 in your use case.

Determine 8: Entry coverage guidelines

Setup trusted token issuer in AWS IAM Identification Middle

At this level, you turn to organising the AWS configuration, beginning by including a trusted token issuer (TTI), which makes it attainable to change tokens. This entails connecting IAM Identification Middle to the Open ID Join (OIDC) discovery URL of the exterior OAuth authorization server and defining an attribute-based mapping between the person from the exterior OAuth authorization server and a corresponding person in Identification 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 Identification Middle, after which to the Settings web page.
2. Choose the Authentication tab and beneath Trusted token issuers, select Create trusted token issuer.
3. On the Arrange an exterior IdP to difficulty trusted tokens web page, beneath Trusted token issuer particulars, do the next:
   • For Issuer URL, enter the OIDC discovery URL of the exterior IdP that may difficulty 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, sign up as an admin to Okta and navigate to Safety after which to API and select default beneath the Authorization Servers tab and replica the Issuer URL

Determine 9: Authorization server issuer

4. For Trusted token issuer identify, enter a reputation to establish this trusted token issuer in IAM Identification Middle and within the software console.
5. Beneath Map attributes, do the next:
   • For Identification supplier attribute, choose an attribute from the listing to map to an attribute within the IAM Identification Middle identification retailer.
   • For IAM Identification Middle attribute, choose the corresponding attribute for the attribute mapping.
6. Beneath Tags (elective), select Add new tag, enter a price for Key and optionally for Worth. Select Create trusted token issuer. For details about tags, see Tagging AWS IAM Identification Middle assets.

This instance makes use of Topic (sub) because the Identification supplier attribute to map with Electronic mail from the IAM identification Middle 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 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 Identification Middle connection from Amazon Redshift console menu.

Determine 11: Amazon Redshift IAM Identification Middle connection

2. Choose the Amazon Redshift software that you simply created as a part of the stipulations.
3. Choose the Shopper connections tab and select Edit.
4. Select Sure beneath 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 beneath part Configure chosen trusted token issuers. For instance, okta_tableau_audience.

To get the viewers worth from Okta, sign up as an admin to Okta and navigate to Safety after which to API and select default beneath the Authorization Servers tab and replica the Viewers worth.

Determine 12: Authorization server viewers

Be aware: 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 Identification Middle, Amazon Redshift, and Okta configuration are full. Subsequent, it’s essential to configure Tableau.

To combine Tableau with Amazon Redshift utilizing IAM Identification Middle, it’s essential to use a customized XML. On this step, you employ the next XML and change the values beginning with the $ signal and highlighted in daring. The remainder of the values will be saved as they’re, or you’ll be able to 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.

Be aware: The XML file will likely 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 reserve it beneath DocumentsMy Tableau RepositoryOAuthConfigs.

Be aware: At the moment 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 Identification Middle authentication, you have 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 Shopper
4. Select following settings:
   • Connection Sort: 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 atmosphere. You need to 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 hook up with Amazon Redshift from Tableau via federated sign-in utilizing IAM Identification 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 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 Sort: Choose Identification Middle.
   6. Identification Middle Namespace: You’ll be able to go away this worth clean.
   7. OAuth Supplier: This worth ought to mechanically be pulled out of your configured XML. Will probably be the worth from the factor oauthConfigId.
   8. Choose Require SSL.
   9. Select Register.

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 Identification Middle 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 person Ethan from Okta is accessing the gross sales report.

Determine 19: Person audit in sys_query_history

After signing in, you’ll be able to 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’ve got revealed the report from Tableau Desktop to Tableau Server, sign up as a non-admin person and examine the revealed report (SalesReport on this instance) utilizing IAM Identification Middle authentication.

1. Register to the Tableau Server website as a non-admin person.
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 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’ll be able to 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 got created to combine with IAM Identification Middle.
2. Delete the IAM Identification 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 position and IAM coverage that you simply created for IAM Identification Middle and Amazon Redshift integration.
5. Delete the permission set from IAM Identification Middle that you simply created for Amazon Redshift Question Editor V2 within the administration account.

Conclusion

This submit coated streamlining entry administration for information analytics by utilizing Tableau’s functionality to assist single sign-on based mostly on the OAuth 2.0 OpenID Join (OIDC) protocol. The answer permits federated person authentication, the place person 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 Identification Middle for single sign-on. By harnessing this integration of a 3rd occasion IdP with IAM Identification Middle, customers can securely entry Amazon Redshift information sources inside Tableau with out managing separate database credentials.

Listed under are key assets to be taught extra about Amazon Redshift integration with IAM Identification Middle

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 presently 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 invaluable, actionable insights. His background contains 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 varied Amazon Redshift Companions and clients to drive higher integration.

Ravi Bhattiprolu is a Senior Accomplice Options Architect at Amazon Net Companies (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 objectives.