MicroStrategy ONE

Integrate Azure SQL Database for Single Sign-On

Learn how to integrate MicroStrategy with Azure SQL Database for Single-Sign On (SSO) with OIDC.

Prerequisite: Configure Azure SQL Database OAuth Integration with Azure AD to Create OAuth Applications

You can create an OAuth application using the steps below or follow Microsoft's documentation.

  1. Navigate to the Microsoft Azure Portal and authenticate.

  2. Navigate to Azure Active Directory.
  3. Click App Registrations.

  4. Click New Registration.

  5. Enter a name for the client such as Azure SQL OAuth Client.

  6. Verify that Supported account types is set to Single Tenant.

  7. Click Register.

  8. In the Overview section, copy the ClientID from Application (client) ID. This is the <OAUTH_CLIENT_ID> mentioned in the following steps.

  9. Click Certificates & secrets and New client secret.

  10. Add a description of the secret.

  11. Select never expire. For testing purposes, select secrets that never expire.

  12. Click Add. Copy the secret. This is the <OAUTH_CLIENT_SECRET> mentioned in the following steps.

  13. For programmatic clients that request an access token on behalf of a user, configure Delegated permissions for Applications as follows:

    1. Click API Permissions.
    2. Click Add Permission.
    3. Search for Azure SQL Database.

    4. Click Delegated permissions.

    5. Select the permission related to the scope defined in the application that you want to grant to this client. Make sure to use https://database.windows.net/user_impersonation as the scope.

    6. Click Add Permissions.

    7. Click Grant Admin Consent to grant the permissions to the client. Permissions are configured this way for testing purposes. However, in a production environment, granting permissions in this manner is not advised.

  14. Switch to Authentication and Add a platform.

  15. Select Web and add redirect URIs. Here are some samples:

    • https://<FQDN>:<port>/MicroStrategy/servlet/mstrWeb?evt=3172

    • http://localhost/

  16. In the Implicit grand and hybrid flows section, select ID tokens.

Integrate MicroStrategy with Azure SQL Database for Single Sign-On with OIDC

Enable MicroStrategy Web OIDC with Azure AD

  1. Establish trust between Web server and Intelligence server.
    1. Log in to 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 input the necessary values.

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

    2. For the clientID, go to App > Overview > Application (client) ID. Copy the ID and paste it into Client ID.

    3. For Client Secret, go to App > Certificates & secrets. Copy the value and paste it into Client Secret.

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

    5. The Native Client ID is the same as the Client ID.

    6. There is no need to modify the Redirect URI and Scope.
    7. For the values in Claim Map, fill them as image.

    8. For Admin Groups, enter the groups' Object Id as the value. If the value is set, only users in the group can access the MicroStrategy Web Admin page.

  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.
    1. The clientId, clientSecret, nativeClientId, and issuer are the same as the ones used in the previous procedure for Web.

    2. For the redirect Uri, use <FQDN>:<port> to replace XXXXXXX in the sample below. Add the URL into Azure AD > Azure SQL OAuth Client Application > Authentication > Web Redict URLs.

      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"
          }
        }]
      }

  2. Set the OIDC authentication mode.

    1. Log in to MicroStrategy Library admin page.

    2. Connect to the Intelligence server.

    3. Choose OIDC authentication mode and click Create Trusted Relationship to create a trust relationship between Library server and MicroStrategy Intelligence server. Enter the administrator account and password.

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

Enable MicroStrategy Mobile OIDC with Azure AD

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

    1. Log in to the MicroStrategy Mobile admin page.

    2. Connect to the Intelligence server.

    3. In Trust relationship between Web Server and MicroStrategy Intelligence Server, select your Intelligence server and click Setup.

  2. Choose OIDC Authentication for the Login mode and enter the necessary values.
    1. These values are the same as the ones used in Web.

    2. Add the mobile redirect URI in Azure AD > Azure SQL OAuth Client Application > Authentication > Web Redirect URIs.

  3. Add com.microstrategy.mobile://auth into Azure AD > Azure SQL OAuth Client Application > Authentication > Mobile and desktop application.

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

Integrate MicroStrategy with Azure SQL Database for Single Sign-On with SAML

See Integrate MicroStrategy With Snowflake for Single Sign-On With SAML using Azure AD to integrate MicroStrategy with Azure SQL Database for Single Sign On with SAML.

Enable Seamless Login

See Enable Seamless Login Between Web, Library, and Workstation for more information.

Create Azure SQL Database DB Instances

Starting in MicroStrategy 2021 Update 4, you can leverage Azure SQL single sign-on (SSO) for both the ODBC and JDBC driver.

You can create Azure SQL Database DB instances with or without the project schema.

With the Project Schema

To use the project schema, you must create a basic authentication connection in MicroStrategy Developer or MicroStrategy Web.

In MicroStrategy Developer:

  1. In Database instance name, enter in a descriptive name.

  2. In Database connection type, select Azure SQL Database.

  3. Click New to create a new database connection.

  4. In Database connection name, enter a descriptive name.
  5. Select the DSN.
  6. Create a database login and save your settings.

In MicroStrategy Web:

  1. Choose Add Data > New Data to open the Data Source dialog.
  2. Select the Standard Connection option.

Without the Project Schema

Create an OAuth authentication database connection in MicroStrategy Developer or MicroStrategy Web.

In MicroStrategy Developer:

  1. Click New to create a new database connection.
  2. In Database connection name, enter a descriptive name.
  3. Select the DSN.
  4. Open the Advanced tab.
  5. In Additional connection string parameters, enter AADToken=?MSTR_OAUTH_TOKEN;. This acts as a placeholder that will be replaced by a real token when the user uses the Azure SQL Database DB instance.

  6. Click OK.
  7. In Database login, enter a name.
  8. Select Use network login id (Windows authentication).

In MicroStrategy Web:

  1. Choose Add Data > New Data to open the Data Source dialog.
  2. Select the OAuth Connection option.

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

  4. In Authentication Type, select Microsoft Azure AD SSO. Microsoft Azure AD SSO must be fully configured with SAML or OIDC.

  5. Fill out the required fields. To locate the Client ID, click on the app. Got to Overview > Application (client) ID, and locate the ID.
  6. For the Client Secret, click on the app. Go to Certificates & secrets, and locate the secret. If necessary, create a new secret.

  7. For the Directory (tenant) ID, click on the app. Go to Overview and locate the ID.

  8. For the Scope, use https://database.windows.net/user_impersonation.
  9. The Callback URL is generated by default.

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

    Workstation: http://localhost

    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.

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 Database instance name, enter a descriptive name.
    2. In Database connection type, select Azure SQL Database.
    3. Click New to create a new database connection.
    4. In Database connection name, enter a descriptive 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 Database connection name, enter a descriptive name.
    3. Select the DSN.
    4. Open the Advanced tab.
    5. In Additional connection string parameters, enter AADToken=?MSTR_OAUTH_TOKEN;. This acts as a placeholder that is replaced by a real token when the user uses the Azure SQL Database DB instance.
    6. Click OK.
    7. Click New.
    8. In Database login, enter a name.
    9. Select Use network login id (Windows authentication).
  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 connections in Project Configuration > Database instances > Connection mapping.

      3. Users in the SSO_End_User_DSNless_OAuth group use the AzureSQLSSODSNlessdatabase connection. Users in the SSO_End_User_DSN_OAuth group use the AzureSQLSSODSNdatabase connection. Other users use the default database connection. In this case, the AzureSQLSSODSNless database connection is used.

  4. Set OAuth parameters via MicroStrategy Web.

Validate the OIDC/SAML Login Mode Environment

  1. Check to see if the id token was saved into the user run time. Open the MicroStrategy Diagnostics and Performance Logging Tool and enable the Kernel XML API log.

  2. When logging into MicroStrategy Web, Library, or Mobile servers, the id token is saved into 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="eyJ0eXAiOiJKV1QiLxxxxxxxxxAccessToken" /></crs></cmd></st></sst></st>
  3. Check to see if Azure SQL Database SSO is working. In the MicroStrategy Diagnostics and Performance Logging Tool, enable the WSAuth > Info log.
  4. Check the WSAuth.log file. If you are using SSO, the log should look similar to the one below.
    5. 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 isEmpty=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 isEmpty=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=161465179