Configure OAuth for a Synapse connection

ThoughtSpot supports OAuth for a Microsoft Azure Synapse connection. This page describes the setup and configuration required for using OAuth2.0 with Azure AD as the IdP for Azure Synapse connections.

Each ThoughtSpot instance requires a unique Synapse security integration. Each user in Synapse must have a default warehouse and default role.

Part 1: Create AD application in Azure

The first step in setting up OAuth with Azure AD as an IdP is to create an active directory application in Azure.

  1. Sign in to the Azure portal and navigate to "Azure Active Directory" resource.

  2. Under Manage in the side navigation bar, select App registrations and then New registration.

    Click App registrations > New registration

    The Register an application modal appears.

    Register an application modal

  3. Enter a name for the application, and select the supported account types.

  4. For the Redirect URI, enter your ThoughtSpot instance’s hostname in the following format: https://<your_instance_name>/callosum/v1/connection/generateTokens, and select Register.

    For example: https://myTS.com/callosum/v1/connection/generateTokens.

    The OAuth client application creation is complete.

    The following image displays example details of an application.

    Example OAuth client application details
    If your IdP supports rotation of refresh tokens with every usage, please ensure that this option is NOT chosen. ThoughtSpot does not support one-time use refresh tokens.

  5. Make a note of the Application (client) ID, which you will use later in the OAuth2.0 authorization workflow.

    You will now create the client secret for your application.

  6. In the application you just created, select Certificates & Secrets.

    Click Certificates and Secrets

  7. Select New client secret to create a secret for the application. This secret will also be used in OAuth2.0 authorization flow along with client ID.

  8. Under API / Permissions name, for Azure SQL Database, add the user_impersonation permission, as shown in the following image.

    The Delegated type indicates that the application will authenticate on behalf of the user.

    Add the user impersonation permission with the delegated type

  9. From the application home page, select Endpoints and make a note of the OAuth2.0 authorization and token endpoints.

    In the Endpoints section

Part 2: Set active directory admin for database server

An active directory user must be set as Admin for the SQL server.

  1. Navigate to the SQL server associated with the Synapse deployment and select Active Directory Admin.

    Click Azure Active Directory > Active Directory Admin

  2. At the top of the page, click Set admin to search for users in the directory, and select the user you want to set as administrator for the server.

    Click Set admin

    You should now see the Admin name set for the server.

    Active directory is now linked with the SQL database.

Part 3: Create database user

  1. Create the AD user you want to use for database login by logging in to the database with the administrator user you set earlier.

  2. Use the following DDL in the following format to create the user:

    CREATE USER [<email_address@company>] FROM EXTERNAL PROVIDER;

    Example:

    CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

    This is referred to as a "contained user" created in the database mapped to Azure AD identity. For details, see Microsoft’s documentation: Create contained users mapped to Azure AD identities.

    You cannot manage database roles for AD users from Azure. You must manage them from the database side.

Troubleshooting

If the following Invalid Resource error appears on initial login, you must update the API permissions.

An Invalid Resource error message

Under API / Permissions name, for Azure SQL Database, add the user_impersonation permission, as shown in the following image.

The Delegated type indicates that the application will authenticate on behalf of the user.

Add the user impersonation permission with the delegated type

Logging in to a connection created by another user using OAuth

As an admin user, you may run into an issue logging in to connections created using OAuth. To resolve this issue, complete the following steps:

  1. Search on a table belonging to the connection you are trying to edit. The following error appears:

    Error reading "Error in loading data. Connection to Snowflake could not be established. OAuth login required. Login"

  2. Click Login. You will be directed to the IDP login page.

  3. Enter your login credentials.

  4. You will now have access to edit the connection.

OAuth connection improvements

If you do not have a valid OAuth access token, you can now directly navigate to the OAuth authorization screen when performing one of the following actions on a connection shared with you:

  • View sample data

  • Create a custom SQL view

  • Edit the connection

Related information

Was this page helpful?
×