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.

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. Log in to the Azure portal and navigate to “Azure Active Directory” resource.

  2. Under Manage click App registrations and then New registration.

    azure app registrations

    The Register an application appears.

    azure register application
  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 click Register.

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

    The OAuth client application creation is complete.

    Example details of an application is displayed as shown below.

    azure application complete
  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, click Certificates & Secrets.

    azure cert secrets
  7. Click 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 below.

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

    azure config permissions
  9. From the application home page, click Endpoints and make a note of the OAuth2.0 authorization and token endpoints.

    azure application endpoints

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 to the SQL server associated with the Synapse deployment and click Active Directory Admin.

    azure ad 1
  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.

    azure ad 2

    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 into 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 [admin@example.com] 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.

Troubleshooting

  • If valid API Permissions for the AD application are not present, the following error appears:

    synapse oauth error1
  • With the API permission granted as below, the user is required to consent to the application accessing the resource.

    synapse oauth error2
  • You cannot manage database roles for AD users from Azure. You must manage them from the database side. For details, see Microsoft’s documentation: Create contained users mapped to Azure AD identities.