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

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

Amazon Redshift and Tableau empower information evaluation. Amazon Redshift is a cloud information warehouse that processes complicated 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.

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

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

This weblog publish offers a step-by-step information to integrating IAM Identification Middle with Microsoft Entra ID because the IdP and configuring Amazon Redshift as an AWS managed software. Moreover, you’ll discover ways to arrange the Amazon Redshift driver in Tableau, enabling SSO instantly inside Tableau Desktop.

Resolution overview

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

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

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

1. The person configures Tableau to entry Amazon Redshift utilizing IAM Identification Middle.
2. On a person sign-in try, Tableau initiates a browser-based OAuth circulate and redirects the person 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 Middle software and forwards the entry token.
5. Amazon Redshift passes the token to IAM Identification Middle for validation.
6. IAM Identification Middle first validates the token utilizing the OpenID Join (OIDC) discovery connection to the trusted token issuer (TTI) and returns an IAM Identification Middle generated entry token for a similar person. In Determine 1, the TTI is the Microsoft Entra ID server.
7. Amazon Redshift then makes use of the entry token to acquire the person and group membership info from Identification Middle.
8. The Tableau person will be capable 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, you will need to have the next in place:

Walkthrough

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

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

Arrange the Microsoft Entra ID OIDC software

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

1. Register to the Microsoft Entra admin heart as Cloud Software Administrator (at least).
2. Browse to App registrations below Handle, and select New registration.
3. Enter a reputation for the applying. For instance, Tableau-OIDC-App.
4. Choose a supported account sort, which determines who can use the applying. For this instance, choose the primary choice within the record.
5. Beneath Redirect URI, choose Internet for the kind of software you need to create. Enter the URI the place the entry token is distributed to. On this instance, you’re utilizing localhost, so enter http://localhost:55556/Callback and http://localhost/auth/add_oauth_token.
6. Select Register.
7. Within the navigation pane, select Certificates & secrets and techniques.
8. Select New consumer secret.
9. Enter a Description and choose an expiration for the key or specify a customized lifetime. For this instance, maintain the Microsoft really helpful default expiration worth of 6 months. Select Add.
10. Copy the key worth.
    Observe: It’ll solely be introduced one time; after that you simply can’t learn it.
11. Within the navigation pane, below Handle, select Expose an API.
12. If you happen to’re organising for the primary time, you possibly can see Set to the suitable of Software 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 title, enter a reputation. For instance, redshift_login.
16. For Admin consent show title, enter a show title. For instance, redshift_login.
17. For Admin consent description, enter an outline of the scope.
18. Select Add scope.

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

Gather Microsoft Entra ID info

To configure your IdP with IAM Identification Middle and Amazon Redshift, accumulate the next parameters from Microsoft Entra ID. If you happen to don’t have these parameters, contact your Microsoft Entra ID admin.

1. Tenant ID,Shopper ID and Viewers worth: To get these values:
   1. Register 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. You will discover the Tenant ID,Shopper ID and Viewers worth (Software 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, below Handle, select Expose an API.
   2. You will see that the worth below Scopes as proven within the following determine:
      

      Determine 3: Software scope

Arrange a trusted token issuer in IAM Identification Middle

At this level, you may have completed configurations within the Entra ID console; now you’re prepared so as to add Entra ID as a TTI. You’ll begin by including a TTI so you possibly can 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 Identification Middle, after which to the Settings
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:
   1. For Issuer URL, enter the OIDC discovery URL of the exterior IdP that may problem tokens for trusted id propagation. The URL could be: https://sts.home windows.web//. To search out your Microsoft Entra tenant ID, see Gather Microsoft Entra ID info.
   2. For Trusted token issuer title, enter a reputation to establish this TTI in IAM Identification Middle and within the software console.
   3. Beneath Map attributes, do the next:
      1. For Identification supplier attribute, choose an attribute from the record to map to an attribute within the Identification Middle id retailer. You may select Electronic mail, Object Identifier, Topic, and Different. This instance makes use of Different the place we’re specifying the upn (person principal title) because the Identification supplier attribute to map with Electronic mail from the IAM id Middle attribute.
      2. For IAM Identification Middle attribute, choose the corresponding attribute for the attribute mapping.
   4. Beneath Tags (non-obligatory), select Add new tag, specify a worth for Key, and optionally for Worth. For details about tags, see Tagging AWS IAM Identification Middle sources.

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

The JWT should include a topic (sub) declare, an viewers (aud) declare, an issuer (iss), a person 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 Middle connection from Amazon Redshift console menu.

Determine 5: 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 below 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 below Configure chosen trusted token issuers. For instance, api://1230a234-b456-7890-99c9-a12345bcc123. To get the viewers worth, see Gather Microsoft Entra ID info.
7. Select Save.

Determine 6: Including an viewers declare for the TTI

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

Arrange the Tableau OAuth config information for Microsoft Entra ID

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

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

redshift

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

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

$scope_from_azure_oidc_app


OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT
true


OAUTH_CAP_REQUIRE_PKCE
true


OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD
true


OAUTH_CAP_SUPPORTS_STATE
true


OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM
false


OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN
true



OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL
true




ACCESSTOKEN
access_token


REFRESHTOKEN
refresh_token


access-token-issue-time
issued_at


id-token
id_token


username
e-mail


access-token-expires-in
expires_in

The next is an instance XML file:

redshift

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

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

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


OAUTH_CAP_REQUIRES_PROMPT_SELECT_ACCOUNT
true


OAUTH_CAP_REQUIRE_PKCE
true


OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD
true


OAUTH_CAP_SUPPORTS_STATE
true


OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM
false


OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN
true



OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL
true




ACCESSTOKEN
access_token


REFRESHTOKEN
refresh_token


access-token-issue-time
issued_at


id-token
id_token


username
e-mail


access-token-expires-in
expires_in

Set up the Tableau OAuth config file for Tableau Desktop

After the configuration XML file is created, it 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.

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 Middle authentication, you will need to set up the Tableau OAuth config file in Tableau Server or Tableau Cloud.

1. Register to the Tableau Server or Tableau Cloud utilizing admin credentials.
2. Navigate to Settings.
3. Go to OAuth Shoppers Registry and choose Add OAuth Shopper
4. Select following settings:
   1. Connection Sort: 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 atmosphere. 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 person. 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 Sort: Choose Identification Middle.
   6. Identification Middle Namespace: You may 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 aspect oauthConfigId.
   8. Choose Require SSL.
   9. Select Register.
      

      Determine 8: Tableau Desktop OAuth connection

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

   Determine 9: Microsoft Entra sign up web page

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

   Determine 10: Profitable authentication utilizing Tableau

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

Determine 11: Profitable connection utilizing Tableau Desktop

After signing in, you possibly can create your 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 may have printed the report from Tableau Desktop to Tableau Server, sign up as a non-admin person and think about the printed 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 printed report is saved.
3. Choose the report and select Signal In.
   

   Determine 12: Person audit in sys_query_history

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

   Determine 13: Tableau Server signal In

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

   Determine 14: Tableau report

Confirm person id from Amazon Redshift

As an non-obligatory step, you possibly can audit the federated IAM Identification Middle person from Amazon Redshift.

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

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

Determine 15: Person audit in sys_query_history

Clear up

Full the next steps to scrub up your sources:

1. Delete the IdP functions that you simply 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 AWS Identification and Entry Administration (IAM) position and IAM coverage that you simply created as a part of the stipulations 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 publish explored a streamlined strategy to entry administration for information analytics by utilizing Tableau’s assist for OIDC for SSO. The answer facilitates federated person authentication, the place person identities from an exterior IdP are trusted and propagated to Amazon Redshift. You discovered easy methods to configure Tableau Desktop and Tableau Server to seamlessly combine with Amazon Redshift utilizing IAM Identification Middle for SSO. By harnessing this integration between a third-party IdP and IAM Identification Middle, customers can securely entry Amazon Redshift information sources inside Tableau with out managing separate database credentials.

The next are key sources 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 practically 20 years of expertise in Digital Promoting, Knowledge Analytics, Connectivity Integration and Identification and Entry Administration. He presently 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 providers to amplify the worth customers get from our merchandise and speed up their journey to beneficial, actionable insights. His background consists of analytics, infrastructure, community safety, and migrations.

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

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

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

Ravi Bhattiprolu is a Senior Accomplice Options Architect at 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 clients obtain their enterprise targets.