MicroStrategy ONE

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

Learn how to integrate MicroStrategy with Snowflake for Singe-Sign On (SSO) with SAML authentication.

  1. Create an Azure AD Enterprise Application and enable single sign-on with SAML authentication for JSP Web and Mobile
  2. Integrate a MicroStrategy Library SAML environment with Azure AD
  3. Create Snowflake OAuth applications and integrate with MicroStrategy
  4. Execute a SQL against the metadata warehouse to support storing long tokens
  5. Create Snowflake database instances

Create an Azure AD Enterprise Application and Enable Single Sign-On with SAML for JSP Web and Mobile

Steps 1 and 2 are dependent on each other. The generated metadata XML files from these steps are required to continue. Please switch operation as needed.

  1. Create an Azure AD Enterprise Application and assign users to your application.

    1.1 Follow the Microsoft documentation to configure SAML-based single sign-on to non-gallery applications.

    The Application Name cannot include spaces, otherwise you will not be able to proceed after uploading the SPMetadata.xml file.

    1.2 Add users or user groups to your enterprise application.

  2. Create single sign-on with SAML authetication for JSP Web and Mobile.
    • Create SAML configuration files for your application.
    • Register the application with your Identity Provider (IdP).
    • Establish trust to the MicroStrategy Intelligence Server.
    • Link SAML users to MicroStrategy users.

    2.1 Refer to Enabling Sing Sign-On with SAML Authentication and select from the following topics:

    MicroStrategy only supports JSP Web. IIS is not supported.

    2.2 Or you can generate and modify configuration files, create an Azure AD Enterprise Application, manage the SAML signing certificate, modify the xml to enable SAML with Azure AD, and establish trust between the web server and Intelligence Server.

    Generate and Modify Configuration Files

    1. Open <web application_path>/saml/config/open in your browser.
    2. Enter an Entity ID and click Generate config. The Entity ID is the same as the Application Name created in IdP.

      The URL is as follows:

      • For Web: https://<FQDN>:<port>/MicroStrategy/saml/config/open
      • For Library: https://<FQDN>:<port>/MicroStrategyLibrary/saml/config/open
      • For Mobile: https://<FQDN>:<port>/MicroStrategyMobile/saml/config/open
    3. Modify MstrSamlConfig.xml according to the information from IdP.

      3.1 Locate the XML file in Azure Active Directory > Enterprise applications > <your application> > Single sign-on.

      3.2 Modify the values in userInfo. The values can be found via the App Federation Metadata URL.

      3.3 Get the Admin Group ID from Azure AD. Go to Azure Active Directory > Groups > <your admin group> > Object Id.

      3.4 Check to see if the following 2 sections exist in [Tomcat]\webapps\MicroStrategy\WEB-INF\classes\resources\SAML\SpringSAMLConfig.xml.

       <!-- Handler deciding where to redirect user after successful login -->
          <bean id="successRedirectHandler" class="com.microstrategy.auth.saml.SAMLSuccessRedirectHandler">
              <property name="defaultTargetUrl" value="/"/>
      </bean>
       <!-- Loads implicit OAuth configuration XML -->
          <import resource="custom/SAML2OAuth.xml"/>

      If they do not exist in SpringSAMLConfig.xml, please add them.

      Create an Azure AD Enterprise Application

      Follow the Microsoft documentation to configure SAML-based single sign-on to non-gallery applications.

      1. Edit the Basic SAML Configuration.

        1.1 Upload the metadata file created in Generate and Modify Configuration Files, SPMetadata.xml.

      2. Configure User attributes and claims.

        2.1 Add a new group claim or user claims.

        Manage the SAML signing certificate

        1. Download the Federation Metadata XML from Azure Active Directory > Enterprise applications > <your application> > Single sign-on.

        2. Rename the XML file to IDPMetadata.xml.
        3. Upload the XML file to the MicroStrategy/WEB-INF/classes/resources/SAML folder.

          For MicroStrategy Library, upload the file to the MicroStrategyLibrary/WEB-INF/classes/auth/SAML folder.

        Modify the XML file to enable SAML with Azure AD

        1. Locate web.xml found in [Tomcat]/MicroStrategy/WEB-INF/web.xml and perform the following:
          • Uncomment the following to enable SAML authentication mode for the file:

            <!-- ================== SAML Support ================ -->
            
            <context-param>
                <param-name>contextConfigLocation</param-name>
                <param-value>classpath:resources/SAML/SpringSAMLConfig.xml</param-value>
            </context-param>
            
            <context-param>
                <param-name>contextInitializerClasses</param-name>
                <param-value>com.microstrategy.auth.saml.config.ConfigApplicationContextInitializer</param-value>
            </context-param>
            
            <filter>
                <filter-name>springSecurityFilterChain</filter-name>
                <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
            </filter>
            <filter-mapping>
                <filter-name>springSecurityFilterChain</filter-name>
                <url-pattern>/servlet/*</url-pattern>
            </filter-mapping>
            <filter-mapping>
                <filter-name>springSecurityFilterChain</filter-name>
                <url-pattern>/saml/*</url-pattern>
            </filter-mapping>
            
            <listener>
                <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
            </listener>
            
            <!-- SAML Config GUI -->
          • Optional: Comment or delete the security constraints for Administrator, TaskAdministrator, and TaskDeveloper.

      Establish trust between the Web Server and Intelligence Server

      1. Log in to MicroStrategy Web with your Azure AD account in the admin group.
      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.

Integrate a MicroStrategy Library SAML environment with Azure AD

The configuration for integrating a MicroStrategy Library SAML environment is similar to the steps in Create an Azure AD Enterprise Application and enable single sign-on with SAML authentication for JSP Web and Mobile.

  1. Generate and modify configuration files.

    1.1 Open https://<FQDN>:<port>/MicroStrategyLibrary/saml/config/open to generate configuration files.

    1.2 Modify MstrSamlConfig.xml according to the information from IdP.

    The file path is [Tomcat path]\MicroStrategyLibrary\WEB-INF\classes\auth\SAML.

  2. Create an Azure AD Enterprise Application for Library and Manage the SAML signing certificate.

  3. Create a trusted relationship to establish trusted communication between the Library Web Server and Intelligence Server.

Create Snowflake OAuth Applications and Integrate with MicroStrategy

  1. Configure Snowflake OAuth integration with Azure AD to create OAuth Applications.

    Refer to Configure Microsoft Azure AD for External OAuth.

    1.1 Select the following OAuth flow described in the Pre-Requisites section: The authorization can grant the Oauth client an access token on behalf of the user.

    1.2 Complete the steps accordingly.

    Based on the Snowflake documentation, you will create two applications, Snowflake OAuth Resource Application and Snowflake OAuth Client Application. When configuring the Client Application, add the following redirect URL: https://[MicroStrategy Web Server Hostname]/MicroStrategy/servlet/mstrWeb?evt=3172.

    1.3 Go to the Snowflake OAuth Client Application > Authentication. Locate the Implicit grant section and select the ID tokens checkbox.

  2. Configure SAML2OAuth.xml to fetch the ID tokens.

    2.1 Open and edit the following files:

    • [MicroStrategy Web Root]\WEB-INF\classes\resources\SAML\custom\SAML2OAuth.xml

    • [MicroStrategyLibrary Root]\WEB-INF\classes\auth\SAML\custom\SAML2OAuth.xml

    2.2 Comment out the following section:

    <!-- Beans to add an additional step to fetch idToken after SAML login -->
    
        <bean id="oAuthTokenProvider" class="com.microstrategy.auth.saml.implicitoauth.MicrosoftAzureAD">
            <property name="authorizationEndpoint" value=""/>
            <property name="clientID" value=""/>
            <property name="redirectUri" value=""/>
            <property name="responseType" value="id_token"/>
            <property name="scope">
                <list>
                    <value>openid</value>
                    <value>email</value>
                    <value>profile</value>
                    <value>offline_access</value>
                </list>
            </property>
        </bean>
    

    2.3 Complete the fields for authorizationEndpoint, clientID, and redirectUri.

    You can find the required information on Azure AD where the Snowflake OAuth Resource Application and Snowflake OAuth Client Application were created.

    • For authorizationEndpoint,go to App > Overview > Endpoints, copy the Oauth 2.0 authorization endpoints (v2) and paste it in the file.

    • For clientID, go to App > Overview > Application (client) ID, copy the ID and paste it in the file.

    • For redirectUri, go to App > Authentication, copy the URL and paste it in the file. If nothing is available in the Redirect URIs list, manually add:

      • For MicroStrategy Web: https://[MicroStrategy Web Hostname]/MicroStrategy/auth/SAMLOAuthRedirect.jsp
      • For MicroStrategy Library: https://[MicroStrategy Library Hostname]/MicroStrategyLibrary/auth/SAMLOAuthRedirect.jsp

  3. Restart Tomcat for the MicroStrategy Web and Library configurations to take effect.
  4. Execute a SQL against the Metadata Warehouse to Support Storing Long Tokens

    Back up the metadata warehouse prior to performing any operation. Use MicroStrategy DB Query Tool or other database connectors to connect to the MicroStrategy metadata repository and execute the following query.

    1. Execute the query based on your metadata:

      • For MySQL:

        ALTER TABLE DSSMDACCPROP MODIFY PROP_VAL TEXT NOT NULL;
        
        INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXACCPROPVALCOLUMNLEN', '30000');
      • For SQL Server:

        ALTER TABLE DSSMDACCPROP ALTER COLUMN PROP_VAL TEXT NOT NULL;
        
        INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXACCPROPVALCOLUMNLEN', '30000');
      • For PostgreSQL:

        ALTER TABLE DSSMDACCPROP ALTER COLUMN PROP_VAL TYPE VARCHAR(90000);
        
        INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXACCPROPVALCOLUMNLEN', '30000');

      For other types of warehouses, please contact MicroStrategy Support for assistance.

    2. If you executed the above SQL against the metadata after the Intelligence Server started, you should restart the MicroStrategy Intelligence Server for the changes 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.1 Click New to create a new database connection.

        1.2 In the Database connection name field, type in a name.

        1.3 Select the DSN.

        1.4 Go to the Advanced tab.

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

        1.6 Click OK.

        1.7 In the Database login, enter a name.

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

      • In MicroStrategy Web:

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

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

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

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

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

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.1 In the Database instance name field, type in a name.

      1.2 From the Database connection type drop-down, select Snowflake.

      1.3 Click New to create a new database connection.

      1.4 In the Database connection name field, type in a name.

      1.5 Select the DSN.

      1.6 Create a database login and save your settings.

  2. Create an OAuth authentication database connection.

    • In MicroStrategy Developer:

      2.1 Click New to create a new database connection.

      2.2 In the Database connection name field, type in a name.

      2.3 Select the DSN.

      2.4 Go to the Advanced tab.

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

      2.6 Click OK.

      2.7 Click New.

      2.8 In the Database login, enter a name.

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

  3. Create connection mappings.

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

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

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

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

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

Related Content

Integrating MicroStrategy with Snowflake for Single Sign-On using Okta

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