MicroStrategy ONE

Integrating MicroStrategy With Snowflake for Single Sign-On Using Okta

Starting in MicroStrategy 2020 Update 2, MicroStrategy supports connection to Snowflake through OAuth authentication.

OAuth authentication is supported only in MicroStrategy Web, Library, and Mobile with HTTPS enabled. OAuth authentication is not supported in MicroStrategy Workstation or Developer.

MicroStrategy and Snowflake also support single sign-on (SSO) using SAML protocol, and Okta as an Identiy Provider (IdP).

If any of the following steps have already been configured in your environment, you can skip them.

  1. Configure MicroStrategy to use single sign-on with Okta
    1. Troubleshoot and test the configuration
  2. Configure Snowflake to use single sign-on with Okta
    1. Troubleshoot and test the configuration
    2. Set up Okta's External OAuth security integration
    3. Test the External OAuth configuration
  3. Configure the database instance to use Okta
    1. Create a basic authentication database connection
    2. Add warehouse tables to the warehouse using MicroStrategy Developer
    3. Create an OAuth authentication database connection
    4. Create connection mappings for non-admin users
  4. Consume data from dashboards and reports
    1. Authenticate to Snowflake from MicroStrategy Web
    2. Execute dashboards
  5. Troubleshooting

Configure MicroStrategy to Use Single Sign-On with Okta

Refer to the following documentations to configure MicroStrategy Web and Library to use single sign-on.

MicroStrategy only supports JSP Web. IIS is not supported.

  1. Enabling SAML Authentication for MicroStrategy Library
  2. Enabling SAML Authentication for JSP Web and Mobile
  3. Integrating SAML Support with Okta
  4. Mapping SAML Users to MicroStrategy

Once you've completed all steps, you can troubleshoot the configuration.

Troubleshoot and Test the Configuration

  1. Access your MicroStrategy Web URL. For example, https://tec-w-012480:8443/MicroStrategy/servlet/mstrWeb.

    You are redirected to Okta's authentication page.

  2. Enter your credentials to authenticate to Okta. You are redirected to MicroStrategy Web or Library.

Configure Snowflake to Use Single Sign-On with Okta

Refer to following Snowflake documentations to set up Snowflake single sign-on authentication with Okta.

  1. Overview of Federated Authentication and SSO
  2. Configuring an Identity Provider (IdP) for Snowflake: Okta Setup
  3. Configuring Snowflake to Use Federated Authentication

Troubleshoot and Test the Single Sign-On Configuration

The Okta account used as IdP for Snowflake must be the same account used to authenticate MicroStrategy.

  1. Access Snowflake via the web interface. For example, https://XXXXX.snowflakecomputing.com/.

  2. Click Single Sign On. You are redirected to Okta's authentication page.

  3. Enter your credentials to authenticate to Okta. You are redirected to the Snowflake web interface and a console appears.

Set Up Okta's External OAuth Security Integration

MicroStrategy automatically authenticates users in Snowflake using OAuth authentication. To allow OAuth authentication in Snowflake using Okta as the IdP, refer to the following Snowflake documentations.

  1. Introduction to OAuth
  2. External OAuth Overview
  3. Configure Okta for External OAuth

When creating the Authorization server in Okta (described in Step 2: Create an OAuth Authorization Server), the following scopes must be specified:

  • session:role-any
  • openid
  • profile
  • email
  • offline_access

Test External OAuth Configuration

Refer the following Snowflake documentations.

  1. Testing Procedure
  2. Connecting to Snowflake with External OAuth

Configure the Database Instance to Use Okta

Create a Basic Authentication Database Connection

In MicroStrategy Developer, create a new database instance with a basic authentication connection.

    1. In the Database instance name field, type in a name.
    2. From the Database connection type drop-down, select Snowflake.
    3. Click New to create a new database connection.
    4. In the Database connection name field, type in a name.
    5. Select the DSN.

    6. Create a database login and save your settings.

Add Warehouse Tables to the Warehouse

Once the database instance is created, it can be used to add tables to the project schema via MicroStrategy Developer.

Create an OAuth Authentication Database Connection

After adding tables to the project schema, another database connection can be created for OAuth authentication.

  1. Create an OAuth database connection via MicroStrategy Developer:
    1. Select the Snowflake_SSO_DSN_OAuth default connection and click New.
    2. In the Database connection name field, type in a name.
    3. Select the DSN.
    4. Go to the Advanced tab.

    5. In the Additional connection string parameters field, enter TOKEN=?MSTR_OAUTH_TOKEN;AUTHENTICATOR=oauth;.

      This will act as a placeholder that will be replaced by a real token when the user uses the Snowflake database instance.

    6. Click OK.
    7. Click New.

    8. In the Database login, enter a name.

    9. Select the Use network login id (Windows authentication) checkbox.

  2. Set the OAuth parameters in MicroStrategy Web:
    1. Log in to MicroStrategy Web as the administrator user.
    2. In the Database Instance menu, select OAuth Parameters.
    3. Fill out the required fields:
      • When setting OAuth parameters, select OKTA.
      • For Client ID, recover the Client ID saved in Step 1: Configure Okta for External OAuth.
      • For Client Secret, recover the Client Secret saved in Step 1: Configure Okta for External OAuth.
      • For OAuth URL and Token URL, edit the Snowflake's Authorization Server created in Okta (as described in Step 2: Create an OAuth Authorization Server).
        1. Navigate to the Okta Admin Console.

        2. In the Security menu, go to API > Authorization Servers.

        3. Edit Snowflake's related authorization server.

        4. Copy the value for Issuer. The value should be similar to https://dev-XXXXX.oktapreview.com/oauth2/YYYYY.

        5. To obtain the Init OAuth URL and Refresh Token URL, add the following values to the Issuer value:

          Init OAuth URL: https://dev-XXXX.oktapreview.com/oauth2/YYYYY/v1/authorize

          Refresh token URL: https://dev-XXXXX.oktapreview.com/oauth2/YYYY/v1/token

        6. Copy the Callback URL. This will be whitelisted.
  3. Whitelist the callback URL:
    1. In the Okta Admin Console, go to the application created in Step 1: Create an OAuth Compatible Client to Use with Snowflake.
    2. Go to the General tab.
    3. Click Edit.
    4. Locate the Login redirect URIs section and click Add URI.
    5. Add the copied Callback URL to the list.

Create Connection Mapping for Non-Admin Users

In this example workflow, an administrator wants to use basic authentication in MicroStrategy Developer. Then, the analyst uses OAuth authentication in MicroStrategy Web and Library.

A connection mapping can be created for the analyst to use the Snowflake_SSO_DSN_OAuth connection, and for the administrator to use the Snowflake_SSO_DSN_Basic connection. For more information on connection mapping, see Controlling Access to the Database: Connection Mappings.

  1. In MicroStrategy Developer, right-click on Project > Project Configuration.
  2. Go to Database Instances > Connection Mapping.
  3. Right-click on the grid > New.

  4. Modify the connection mapping to have the appropriate fields.

    In this example, the OAuth database connection name is Snowflake_SSO_DSN_OAuth and the basic database connection name is Snowflake_SSO_DSN_Basic.

  5. Click OK.
  6. Go to Administration > Database Instances.
  7. Edit the database instance. In this example, the database instance is Snowflake_SSO.
  8. Select Snowflake_SSO_DSN_Basic as the default database connection.
  9. Click OK.

Consume Data from Dashboards and Reports

Authenticate to Snowflake from MicroStrategy Web

Using an analyst user mapped to the Okta user (as explained in Mapping SAML Users to MicroStrategy), log in to MicroStrategy Web.

  1. In the Data Import dialog, select the primary database instance for the project. For example, Snowflake_SSO.

    The Okta authentication page momentarily appears and then disappears. If you encounter a 404 error, then the Callback URL is not correctly whitelisted.

  2. Select the database instance. The dialog displays.

    At this point, you are authenticated to Snowflake and can access data and dashboards with their credentials.

Execute Dashboards

Execute a project schema based dashboard.

Troubleshooting

When authenticating to Snowflake from the Data Import dialog, a screen appears with a 404 error

Cause: The callback URL was not added to the whitelist of valid redirect URLs.

Solution: Add the appropriate callback URL to the whitelist of valid URLs as described in Whitelist the Callback URL.

Failed to retrieve refresh token for autehenticationError in Process method of Component: Query EngineServer, Project MicroStrategy TPCH, Job 42, Error Code = -2147212544

Cause: Authentication to Snowflake has not been established yet.

Solution: You need to authenticate to Snowflake via the Data Import dialog.

Client ID or Clietn Secret not found in metadata. Error in Process method of Component: QueryEngineServer, Project MicroStrategy TPCH, Job 69, Error Code = -2147212544

Cause: Connection mapping resolves to the basic authentication database connection.

Solution: Confirm the connection mapping is mapped correctly for the user. Change the default database connection for the database instance.

Intelligence Server Logs

In case of errors, please enable WSAuth.log, as well as DSSErrors.log. It is also recommended that you place the file log for the WSAuth components directly in the DSSErrors.log.

Snowflake Driver Log

To enable the Snowflake driver, see KB48422: How to enable debug log for newly bundled Snowflake driver.

Related Content

KB484275: Best practices for using the Snowflake Single Sign-on (SSO) feature

Integrating MicroStrategy with Snowflake for Single Sign-On using Azure AD