Version 2021

Integrating MicroStrategy with Snowflake for Single Sign-On Using Okta

  • Workstation
  • Developer

Starting in MicroStrategy 2021 Update 10, MicroStrategy supports single-sign-on to Snowflake using Open-ID through Okta in all clients out-of-the-box.

MicroStrategy 2021 Update 9 introduces a preview feature that supports connection to Snowflake through OAuth authentication in Web.

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 Okta Identity Provider for Snowflake On-Behalf-Of
    1. Create an Okta Application
    2. Edit the OpenID Connect ID Token
    3. Grant API Scopes
    4. Create an API
    5. Update the API Issuer
    6. Add a session:role-any Scope
    7. Create an Access Policy and Rule
    8. Enable the Okta Integration in Snowflake
  2. Integrate MicroStrategy with Okta OIDC for Snowflake
    1. Create and Map a MicroStrategy User to a Okta User
    2. Integrate MicroStrategy with Okta Using OIDC
    3. Create an Enterprise Security Object
    4. Create a Snowflake JDBC or ODBC Data Source with Oauth On-Behalf-Of
    5. Test the Snowflake Data Source

Configure Okta Identity Provider for Snowflake On-Behalf-Of

Create an Okta Application

  1. Log in to Okta.
  2. In the Navigation pane, under Applications, click Applications.
  3. Click Create App Integration.

  4. Choose the OIDC - OpenID Connect sign-in method and the Native Application application type.
  5. Click Next.
  6. Enter a name for the application integration, and choose the Refresh Token and Token Exchange grant types.
  7. Add your sign-in redirect URIs for your environment. For example, https://env-308750.customer.cloud.microstrategy.com/MicroStrategyLibrary/auth/oidc/login.
  8. Choose the Allow everyone in your organization to access controlled access.
  9. Click Save.

Edit the OpenID Connect ID Token

  1. Navigate to the Sign On tab of your newly created application.
  2. In OpenID Connect ID Token, click Edit.
  3. From the Issuer drop-down, select the Okta URL.

  4. Click Save.

Grant API Scopes

  1. Navigate to the Okta API Scopes tab of your newly created application.

  2. Click Grant next to the following scopes:
  • okta.apps.read
  • okta.groups.read
  • okta.users.read

Create an API

  1. In the Navigation pane, under Security, click API.
  2. Click Add Authorization Server.
  3. Enter a name and audience and click Save.

Update the API Issuer

  1. Navigate to the Settings tab of your newly created API.
  2. In Settings, click Edit.
  3. From the Issuer drop-down, select the Okta URL.
  4. Click Save.

Add a session:role-any Scope

  1. Navigate to the Scopes tab of your newly created API.
  2. Click Add Scope.
  3. In the Name, Display phrase, and Descriptions fields, enter session:role-any.

Create an Access Policy and Rule

  1. Navigate to the Access Policies tab of your newly created API.
  2. Click Add Policy.
  3. Enter a name and description. Optionally customize Assign to.
  4. Click Create Policy.
  5. Click Add rule.
  6. Enter your preferred rule options and click Create rule.

Enable the Okta Integration in Snowflake

Run the following SQL query in Snowflake:

Copy 
CREATE OR REPLACE SECURITY INTEGRATION external_oauth_demo
    type = external_oauth
    enabled = true
    external_oauth_type = 'okta'
    external_oauth_any_role_mode = 'ENABLE'
    external_oauth_issuer = 'https://dev-XXXXXXXX.okta.com/oauth2/XXXXXXXXXXXXXXXX'
    external_oauth_jws_keys_url = 'https://dev-XXXXXXXX.okta.com/oauth2/XXXXXXXXXXXXXXXX7/v1/keys'
    external_oauth_audience_list = ('XXXXXX')
    external_oauth_token_user_mapping_claim = 'sub'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';

Replace "external_oauth_issuer", "external_oauth_jws_keys_url", and "external_oauth_audience_list" with your values that you can find in your Authentication server. You can access the Authentication server visiting a URL similar to https://dev-XXXXXXXX.okta.com/oauth2/XXXXXXXXXXXXXXXXX/.well-known/oauth-authorization-server

Integrate MicroStrategy with Okta OIDC for Snowflake

The following data source connection leverages MicroStrategy authentication so you don't have to log in multiple times to improve usability. This type of authentication is also known as single sign-on (SSO). The following steps require an Okta application and access to an Okta account. You must also include your environment URLs in the Okta redirect list, for example:

  • https://env-308140.customer.cloud.microstrategy.com/MicroStrategyLibrary/auth/oidc/login
  • https://env-308140.customer.cloud.microstrategy.com/MicroStrategyLibrary/static/oidc/success.html

Create and Map a MicroStrategy User to a Okta User

  1. Open the Web window.
  2. In the Navigation pane, click Environments.
  3. Log into your environment. You must have Administrator privileges.
  4. In the Navigation pane, click User and Groups.
  5. Click next to All Users.

  6. In the left pane, click Privileges and add the following privileges:
  • Access data from Databases, Google BigQuery, BigData, OLAP, BI tools
  • Create and edit database instances and connections
  • Create and edit database logins
  • Create configuration objects
  • Create dataset in Web
  • Configure project data source
  • Monitor Database Connections
  • Use Workstation
  1. In the left pane, click Authentication.
  2. Enter your Okta email address in Trusted Authenticated Request User ID.
  3. Click Save.

For more information on mapping existing users, see Mapping OIDC Users to MicroStrategy.

Integrate MicroStrategy with Okta Using OIDC

  1. Right-click your connected environment and choose Configure Enterprise Security > Configure OIDC.
  2. From the identity provider drop-down, select Okta.
  3. Enter the Client ID, Issuer, and add the session:role-any scope that you created above.

  4. Click Save.

    5. If you click Test Configuration, the test will not be successful.

  5. Restart the Library server.

Create an Enterprise Security Object

Follow the steps in Manage OAuth Enterprise Security with Identity and Access Management (IAM) Objects to create an enterprise security object.

Select Okta in the identity provider drop-down, and enter the Client ID, OAuth URL and Token URL for your Okta application. The URLs should be in the following format:

  • https://dev-33271016.okta.com/oauth2/microstrategy/v1/authorize
  • https://dev-33271016.okta.com/oauth2/microstrategy/v1/token

Create a Snowflake JDBC or ODBC Data Source with Oauth On-Behalf-Of

For more information on creating a Data source, see Create and Edit Data Sources.

  1. Open the Web window.
  2. In the Navigation pane, click , next to Data Sources.
  3. Choose Snowflake.
  4. Enter a Name.
  5. Expand the Default Database Connection drop-down and click Add New Database Connection.
  6. Enter a Name, select a JDBC or ODBC driver type, and enter the required connection information.

  7. Click Save.
  8. Select the Projects to which the data source is assigned and can be accessed.
  9. Click Save.

Test the Snowflake Data Source

  1. Open the Web window.
  2. Check that the environment is using the Default OIDC authentication mode.
    1. Click Environments in the Navigation pane.
    2. Right-click the environment you want to use and click Edit Environment Information.
    3. Check that the Authentication Mode is set to "Default OIDC".
  1. Login to your MicroStrategy environment using your Okta username and password.
  2. To test the data source in Library:
    1. Open MicroStrategy Library and click Log in with OIDC.
    2. In the toolbar, click , and choose Dossier.
    3. Click Blank Dossier.
    4. Click Create.
    5. Click New Data and select the Snowflake gateway.
    6. Choose Select Tables and click Next.
    7. Select the data source you created.
    8. The projects and datasets list displays.
  3. To test the data source in Web:
    1. In the Navigation pane, click , next to Dataset.
    2. Select the Snowflake gateway.
    3. Select the data source you created.
    4. The dataset displays.

 

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 dossiers and reports
    1. Authenticate to Snowflake from MicroStrategy Web
    2. Execute dossiers
  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 Dossiers 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 dossiers with their credentials.

Execute Dossiers

Execute a project schema based dossier.

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

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

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