Integrate Snowflake OAuth Authentication Through AD FS

Learn how to integrate MicroStrategy with Snowflake for OAuth authentication through SAML support with Active Directory Federation Service (AD FS).

1. Configure the AD FS server application

2. Create a Snowflake database instance and configure OAuth

   • With the project schema

   • Without the project schema

   • Enable seamless login

3. Known limitations

4. Appendix

   • Configure AD FS SAML for MicroStrategy Web, Library, and Mobile

   • Configure Snowflake

     • SAML configuration

     • OAuth configuration

To troubleshoot an expired refresh token, see KB485176.

Configure the AD FS Server Application

1. In AD FS Management Console, add an application group.

2. Enter a name and select the Server application template.

   

3. Click Next.

4. Copy Client Identifier as Client ID.

5. Enter a redirect URI and click Add. For example, http://localhost.

   The redirect URI does not accept query parameters. In this case, ?env=3172 is removed in the AD FS setting. However, you must enter the full URL when adding an OAuth redirect URI, which is automatically regenerated by MicroStrategy. Some identity providers (IdP), like Twitter, prevent using such URI, but still accept the request.

   

6. Click Next.
7. Configure the application credential. In this example, the shared secret is used as the client secret.

   

8. Review your configuration and click Finish to save your application.
9. Right-click the application > Properties to add Web API.

   

10. Click Next.
11. Add Snowflake account identifiers.

    The URL is in the format of https://<account_identifier>.snowflakecomputing.com/fed/login. For more information, see Account Identifiers - Snowflake Documentation.

    

12. Assign the appropriate privilege and configure the application permission.
13. In Permitted scopes, select email, openid, profile, and user_impersonation.
14. Add the session:role-any customized scope.

    

15. Review and click Finish.
16. Issue Transform Rules for Web API applications as shown in the following screenshots.

    

    

    

    Add an extra transform rule to transform scope of access to ascope. See Configure Custom Clients for External OAuth — Snowflake Documentation for more information on the ascope rule.

    

Create Snowflake Database Instances

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

See Integrate MicroStrategy With Snowflake for Single Sign-On With SAML Using Azure AD to create a Snowflake Database Instance.

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. For JDBC connection, you must specify Login ID as the Snowflake login namea associated with the access token. For example, domain\snowflakeuser.

        

   • In MicroStrategy Web:

     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.

   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 AD FS.

      

   3. Fill out the required fields.

      The required information can be referenced when creating the AD FS server application in the previous steps.

      • For Client ID, click on Application Groups > Overview > Properties > Server application> Edit, and locate the ID.

      • For Client Secret, use the secret generated the first time you created the server application.
      • For OAuth URL, use https://<ADFS Server base URL>/adfs/oauth2/authorize.
      • For Token URL, use https://<ADFS Server base URL>/adfs/oauth2/token.
      • For Resource, use https://<account_identifier>.snowflakecomputing.com/fed/login. For more information, see Account Identifiers.
      • For Scope, use openid session:role-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 must be added to the Snowflake server client application.

Create Seamless Login

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

Limitations

• An end-to-end single sign-on (SSO) workflow is not supported.

• To use the database role in MicroStrategy Workstation, OAuth parameters must be set from Workstation. OAuth parameters in MicroStrategy Web and Workstation are of different set values.

• You must re-authenticate if the access token is expired. The default expiration time is 8 hours and can be configure in the AD FS server

Appendix

AD FS Configuration for SAML Authentication

See Integrating SAML Support with AD FS to integrate AD FS with MicroStrategy.

Snowflake Configuration

SAML Configuration

The following is a sample workflow for configuring OAuth for Snowflake.

1. Log in using the Snowflake Web Console.

2. Run the following query to create a Snowflake account.

   Copy

   create user "snowflakeuser@demo.com" password = 'PASSWORD' DEFAULT_ROLE = PUBLIC;
   grant role SYSADMIN to user "snowflakeuser@demo.com" ;
   grant role PUBLIC to user "snowflakeuser@demo.com" ;
   GRANT  MODIFY , MONITOR , USAGE , OPERATE ON WAREHOUSE "DEMO_WH" to role PUBLIC;

3. Specify the IdP information for Snowflake.
   Copy

   use role accountadmin;
   alter account set saml_identity_provider = '{
     "certificate": "<Certificate Content>",
     "issuer": "http://<ADFS Server base URL>/adfs/services/trust",
     "ssoUrl": "https://<ADFS Server base URL>/adfs/ls",
     "type"  : "ADFS",
     "label" : "ADFSSingleSignOn"
     }';
   

   See Configuring an Identity Provider (IdP) for Snowflake — Snowflake Documentation to obtain the certificate and replace <Certificate Content> with your certificate.

4. Enable Snowflake-initiated SSO.
   Copy

   use role accountadmin;
   
   alter account set sso_login_page = true;

OAuth Configuration

See Configure Custom Clients for External OAuth - Snowflake Documentation to configure OAuth for Snowflake.

1. Create an OAuth authorization server in Snowflake.

   external_oauth_rsa_public_key is retrieved against the certificate which is obtained in the previous step.

   Copy

   create or replace security integration adfs_oauth_mstr
       type = external_oauth
       enabled = true
       external_oauth_type = custom
       external_oauth_any_role_mode = 'ENABLE'
       external_oauth_issuer = 'https://<ADFS Server base URL>/adfs/services/trust'
       external_oauth_rsa_public_key = '<public key retieved from certificate>'
       external_oauth_audience_list=('https://<account_identifier>.snowflakecomputing.com/fed/login')
       external_oauth_scope_mapping_attribute = 'ascope'
       external_oauth_token_user_mapping_claim='sub'
       external_oauth_snowflake_user_mapping_attribute='login_name';

2. Modify your external OAuth security integration.

   Copy

   alter security integration adfs_oauth_mstr set 
       external_oauth_scope_mapping_attribute = 'scope';