MicroStrategy ONE

Integrate MicroStrategy With Snowflake for Single Sign-On With OIDC using Azure AD

Learn how to integrate MicroStrategy with Snowflake for Single-Sign On (SSO) with OpenID Connect (OIDC) authentication.

  1. Prerequisite: Configure Snowflake OAuth integration with Azure AD to create OAuth applications.
  2. Enable MicroStrategy Web OIDC with Azure AD
  3. Enable MicroStrategy Library OIDC with Azure AD
  4. Configure seamless login
  5. Enable MicroStrategy Mobile OIDC with Azure AD
  6. Create Snowflake database instances
  7. Validate OIDC login mode

Prerequisite: Configure Snowflake OAuth integration with Azure AD to create OAuth applications

  1. Select the following OAuth flow described in the Pre-Requisite section of Configure Microsoft Azure AD for External OAuth: The authorization can grant the Oauth client an access token on behalf of the user.
  2. Complete the following steps:
    1. Based on the Snowflake documentation, create two applications: Snowflake OAuth Resource Application and Snowflake OAuth Client Application. When configuring the Client Application, add the following redirect URLs:
      • https://<FQDN>:<port>/MicroStrategy/servlet/mstrWeb?evt=3172
      • http://localhost
  3. Go to the Snowflake OAuth Client Application > Authentication.

  4. Under the Implicit gran and hybrid flows section, select the ID tokens checkbox.

Enable MicroStrategy Web OIDC with Azure AD

  1. Establish trust between the Web Server and Intelligence Server.
    1. Log into MicroStrategy Web.
    2. Connect to the Intelligence Server.

    3. Select your Intelligence Server and next to Trust relationship between Web Server and MicroStrategy Intelligence Server, click Setup.

    4. Enter the administrator account and password to establish trust.

  2. Set OIDC as the login mode and complete the required fields.

    For the MicroStrategy application redirect URI, the value must be added to Azure AD > Snowflake OAuth Client Application > Authentication > Web Redirects URIs.

    • For Client ID, go to the app > Overview > Application (client) ID, and locate the ID.

    • For Client Secret, go to the app > Certificates & secrets, and locate the secret. If necessary, create a new secret.

    • For Issuer, go to App > Overview > Endpoints, open the URL of OpenID Connect metadata document, and copy the issuer value. For example, https://login.microsoftonline.com/[Directory tenant ID]/v2.0.

    • For Native ID, use the same value as Client ID.
    • For Redirect URI and Scope, leave the fields unmodified.
    • For the Claim Map fields:
      • Full Name: name
      • User ID: upn
      • Email: email
      • Groups: groups
    • For Admin Groups, go the app > Groups > Overview, and locate Object Id. If the Object Id is a value set, only users in the group can access mstrWebAdmin pages.
  3. Restart Tomcat for the MicroStrategy Web configurations to take effect.

Enable MicroStrategy Library OIDC with Azure AD

  1. Create or modify MicroStrategyLibrary\WEB-INF\classes\auth\Oidc\OidcConfig.json.

    Copy 
    {
      "iams":[{
        "clientId":"XXXXXXX",
        "clientSecret":"XXXXXXX",
        "nativeClientId": "XXXXXXX",
        "id":"test",
        "issuer":"https://login.microsoftonline.com/XXXXXXX/v2.0",
        "redirectUri":"https://XXXXXXX/MicroStrategyLibrary/auth/oidc/login",
        "blockAutoProvisioning": true,
        "claimMap": {
          "email": "email",
          "fullName": "name",
          "userId": "upn",
         "groups": "groups"
        },
        "default": true,
        "mstrIam": true,
        "scopes": [
          "openid",
          "profile",
          "email",
          "offline_access"
        ],
        "vendor": {
          "name": "MicroStrategy IAM",
          "version": "Azure AD"
        }
      }]
    }
    • For clientId, clientSecret, nativeClientId, and issuer, use the same values used for OIDC configuration for MicroStrategy Web.
    • For redirectUri, use <FQDN>:<port> to replace XXXXXXX, and add the URL to Azure AD > Snowflake OAuth > Snowflake OAuth Client Application > Web Redirect URLs.

  2. Set OIDC authentication mode.

    1. Log into the MicroStrategy Library Admin page.

    2. Connect to the Intelligence Server.
    3. Go to the Library Server tab > Authentication Modes and select the OIDC checkbox.

    4. Click Create Trusted Relationship to create a trusted relationship between the Library Server and Intelligence Server.

    5. Enter the administrator account and password to establish trust.

  3. Restart Tomcat for the MicroStrategy Library configurations to take effect.

Configure Seamless Login

To navigate between MicroStrategy Web and Library without having to re-authenticate, see How to Enable Seamless Login Between Web, Library, and Workstation.

Enable MicroStrategy Mobile OIDC with Azure AD

  1. Establish trust between the Web Server and Intelligence Server.

    1. Log into the MicroStrategy Mobile Admin page.
    2. Connect to the Intelligence Server.

    3. Select your Intelligence Server and next to Trust relationship between Mobile Server and MicroStrategy Intelligence Server, click Setup.

  2. Set OIDC as the login mode and complete the required fields using the same values used for OIDC configuration for MicroStrategy Web.

  3. Add the Mobile Redirect URI to Azure AD > Snowflake OAuth Client Application > Authentication > Web Refidirect URIs.

  4. In Azure AD > Snowflake OAuth Client Application > Authentication > Mobile and desktop applications, add com.microstrategy.mobile://auth as a redirect URI.

  5. Restart Tomcat for the MicroStrategy Mobile configurations to take effect.

Create Snowflake Database Instances

You can create Snowflake database instances with or without the project schema.

With the Project Schema

To use the project schema, you must have a basic authentication connection:

  • In MicroStrategy Developer:

    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 saved your settings.

  • In MicroStrategy Web:

    Database instances created via MicroStrategy can be used for the project schema, but cannot be used for connection mapping.

    1. In the Data Source dialog, select the Standard Connection option.

Without the Project Schema

To use the database instance without the project schema, you must either have basic or OAuth authentication.

  1. Create an OAuth authentication database connection:

    • In MicroStrategy Developer:

      1. Click New to create a new database connection.
      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;.

        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. In the Database login, enter a name.

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

    • In MicroStrategy Web:

      1. In the Data Source dialog, select the OAuth Connection option.

  2. Set OAuth Parameters.

    Users must have the Set OAuth parameters for Cloud App sources privilege under Client-Web.

    If you want to use the DB role in MicroStrategy Workstation, OAuth parameters must be set from Workstation. Oauth parameters in Web and Workstation are different set values.

  3. After the database instance is created, you can set the OAuth parameters in MicroStrategy Web.

    1. In the Database Instance menu, select Set OAuth Parameters.

    2. In the Authentication Type drop-down, select Microsoft Azure AD SSO.

    3. Fill out the required fields.

      You can find the required information on Azure AD where the Snowflake OAuth Application was created in Integrate a MicroStrategy Library SAML environment with Azure AD.

      • For Client ID, click on the app > Overview > Application (client) ID, and locate the ID.

      • For Client Secret, click on the app > Certificates & secrets, and locate the secret. If necessary, create a new secret.
      • For Directory (tenant) ID, click on the app > Overview, and locate the ID.
      • For Scope, click on the app > API permissions, click on the API/Permission name, and locate the URL. The URL is in the format like https://[AzureDomain]/[id]/session:scope-any.

      • The Callback URL is generated by default.

        For Web: https://[MicroStrategy Web Hostname]/MicroStrategy/servlet/mstrWeb?evt=3172

        For Workstation: http://localhost

        The callback URL should be added to the Snowflake OAtuh Client Application.

Create Connection Mappings (Optional)

If you have multiple MicroStrategy Users or User Groups and want to give access to the same database instance but with different database logins, see Controlling Access to the Database: Connection Mappings

In a primary database connection, users that are not mapped into the secondary database connection use the default database connection. In a secondary database connection, users in a specific group use the mapped database connection.

For example, the administrator uses basic authentication, while other users use OAuth authentication. All users can use the project schema. You must set the default connection to use standard authentication for the Warehouse Catalog to work in Developer:

  1. Create a basic authentication database connection (default).

    • In MicroStrategy Developer

      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.

  2. Create an OAuth authentication database connection.

    • In MicroStrategy Developer

      1. Click New to create a new database connection.
      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;.

        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. :

  3. Create connection mappings.

    1. Assign the new traditional DBRole in Project Configuration > Database Instance > SQL Data warehouse.

      A default database connection mapping is created for all users when you select the database instance.

    2. Assign different user groups with basic and OAuth database connection in Project Configuration > Database instances > Connection mapping.

      :

      • Users in group SSO_End_User_DSNless_OAuth will use the Snowflake_SSO_DSNless_OAuth database connection.
      • Users in group SSO_End_User_DSN_OAuth will use the Snowflake_SSO_DSN_OAuth database connection.
      • Users in group SSO_End_User_JDBC_OAuth will use the SSO_End_User_JDBC_OAuth database connection.
      • Other users will use the default database connection. In this case, the Snowflake_SSO_DSNLess_Basic database connection is used.

  4. Set OAuth parameters via MicroStrategy Web. After the database instance is created, you can set the OAuth parameters in MicroStrategy Web.

    1. In the Database Instance menu, select Set OAuth Parameters.

    2. From the Authentication Type drop-down, select Microsoft Azure AD SSO.

    3. Fill out the required fields.

      You can find the required information on Azure AD where the Snowflake OAuth Application was created in Integrate a MicroStrategy Library SAML environment with Azure AD.

      • For Client ID, click on the app > Overview > Application (client) ID, and locate the ID.
      • For Client Secret, click on the app > Certificates & secrets, and locate the secret. If necessary, create a new secret.
      • For Directory (tenant) ID, click on the app > Overview, and locate the ID.
      • For Scope, click on the app > API permissions, click on the API/Permission name, and locate the URL. The URL is in the format like https://[AzureDomain]/[id]/session:scope-any.

      • The Callback URL is generated by default.

        The callback URL should be added to the Snowflake OAtuh Client Application.

Validate OIDC Login Mode

  1. Check the ID token saved in the user run time.
    1. Open MicroStrategy Diagnostics and Performance Logging Tool to enable Kernel XML API log.

    2. When logging into the MicroStrategy Web, Library, or Mobile server, the id token is saved in the user run time and logs, as shown below:

      Copy
      2021-03-05 04:03:58.020-05:00 [HOST:tec-w-XXX][SERVER:CastorServer][PID:191028][THR:194528][Kernel XML API][Trace][UID:0][SID:0][OID:0] XML Command: <st><sst><st><cmd><crs uid="XXX@gdmstr.onmicrosoft.com" twst="TokenE7061FDE496B0B87A797B6B4D00C3665" pwd="***" npwd="***" pgd="" clid="Server Machine: XXXX Client Machine: tempclient" clmn=" Client Machine Name: tempclient" amd="64" snf="33554432" rws="10" sws="1" mid="" clt="6" vr="11.3.0100.17108J"><reg_opt lcl_rsl="1"><reg_int lcl_id="1033" lcl_rsl="1"/><reg_num lcl_id="1033" lcl_rsl="1"/></reg_opt><u n="Snowflake User" eml="XXX@microstrategy.com" token="eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6Im5PbzNaRHJPRFhFSzFqS1doWHNsSFJfS1hFZyJ9.eyJhdWQiOiIxODk2OWQxYi1kNjExLTQ0MzYtYWJjZC1lZTQzODMxNzYxY2UiLCJpc3MiOiJodHRwczovL2xvZ2luLm1pY3Jvc29mdG9ubGluZS5jb20vNGNhODk0M2EtYzFhNy00YmZlLTg2OGUtYzViZGI0ZDU5ZmVlL3YyLjAiLCJpYXQiOjE2MTQ5MzQ3MzUsIm5iZiI6MTYxNDkzNDczNSwiZXhwIjoxNjE0OTM4NjM1LCJhaW8iOiJBVFFBeS84VEFBQUFxWk1ldlZ6RVJOcVovbU8rRTM1QTJueHpOK0Y0djMrNlBNWW51KzBuaHN5eW0vQUNZN3I1OUNEZ0V5d0I1VFFnIiwiZW1haWwiOiJ4aWh1YW5AbWljcm9zdHJhdGVneS5jb20iLCJuYW1lIjoiU25vd2ZsYWtlIFVzZXIiLCJub25jZSI6IlhVa3lYWDFjRlhHcm96SWRTNmxlN0VvOU5kakp1TEtzT3JZc1ZmQkpvTFkiLCJvaWQiOiJkMTIzYzE4Ny0wNWU5LTQwMWUtYTIwYy05OWQ4ODA5ZGM0MjAiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJzbm93Zmxha2V1c2VyQGdkbXN0ci5vbm1pY3Jvc29mdC5jb20iLCJyaCI6IjAuQUFBQU9wU29US2ZCX2t1R2pzVzl0TldmN2h1ZGxoZ1IxalpFcTgzdVE0TVhZYzVGQURrLiIsInN1YiI6IlVkanhTNEg0b1owWmZoUDJ0U3RLSHl6TXNqbnUtTzZfZGhyOWcwNzQwcXMiLCJ0aWQiOiI0Y2E4OTQzYS1jMWE3LTRiZmUtODY4ZS1jNWJkYjRkNTlmZWUiLCJ1cG4iOiJzbm93Zmxha2V1c2VyQGdkbXN0ci5vbm1pY3Jvc29mdC5jb20iLCJ1dGkiOiI5ZlhLZVY3eXlVbXdCZ0l3aEVNSUFBIiwidmVyIjoiMi4wIn0.I625c3oUxaz9fzCSjHauMDwyooDck9cXa4F0FPycMqwSRxEMqcNwObXFsEwlc8SZ3CXRIAK0QRbvTIsoqFQiPfqmohUUhYd48a3apgwLunsL_7Hj0MhWdSlmqZXFK6JleMj2Xeiqj4oTMyi9TPkH1vi7cpHSSx2_8-M6tYyPVR1JSkbk_2yc13xOuvgZus9LmTP9SmuToAeII56kz_Pg0OmDUqWkL0IhBWq9MXEMn-RVP6xU-hkHIPrYIQAgKjR2Snpc8A48hM9igJuRHl3igqW3GuPZvuLv5xYmGqcM212INafxZKFwVHZF0QQGOLOkfa8GmhKDGev3CMSPmZj_FQ" /></crs></cmd></st></sst></st>

  2. Check to see if SSO works for Snowflake.

    1. Open MicroStrategy Diagnostics and Performance Logging Tool to enable WSAuth > Info log.

    2. Open WSAuth.log and if SSO is being used, the log should have the following content:

      • id token isEmpty=false, usingSSO=true
      • Refreshed an access token using id token

      The log is shown below:

      Copy
      2021-03-01 21:23:13.999-05:00 [HOST:tec-w-XXX][SERVER:CastorServer][PID:191028][THR:176252][WSAuth][Trace][UID:D27A5347411556F271A147B8DE2A74B9][SID:5EDD58F04C30D75546CB6BC97AE45CF3][OID:230016B943FD7EB57237F6A7AA185AC2] To refresh an access token: access token isEmpty=true, refresh token isEmpty=true, id token isEmtpy=false, usingSSO=true
      2021-03-01 21:23:14.297-05:00 [HOST:tec-w-XXX][SERVER:CastorServer][PID:191028][THR:176252][WSAuth][Trace][UID:D27A5347411556F271A147B8DE2A74B9][SID:5EDD58F04C30D75546CB6BC97AE45CF3][OID:230016B943FD7EB57237F6A7AA185AC2] Refreshed an access token using id token
      2021-03-01 21:23:14.298-05:00 [HOST:tec-w-XXX][SERVER:CastorServer][PID:191028][THR:176252][WSAuth][Trace][UID:D27A5347411556F271A147B8DE2A74B9][SID:5EDD58F04C30D75546CB6BC97AE45CF3][OID:230016B943FD7EB57237F6A7AA185AC2] To refresh an access token: access token isEmpty=false, refresh token isEmpty=false, id token isEmtpy=false, usingSSO=true
      2021-03-01 21:23:14.300-05:00 [HOST:tec-w-XXX][SERVER:CastorServer][PID:191028][THR:176252][WSAuth][Trace][UID:D27A5347411556F271A147B8DE2A74B9][SID:5EDD58F04C30D75546CB6BC97AE45CF3][OID:230016B943FD7EB57237F6A7AA185AC2] The live access token: isExpired=false, expireTime=1614655378, currentTime=1614651794

Related Content

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