Power BI SSO to Snowflake¶

General workflow¶

The following diagram summarizes the authorization flow to instantiate a Snowflake session from Power BI:

1. The user logs into Power BI service using Microsoft Azure Active Directory (Azure AD).

2. Optionally, Azure AD can verify the user through an IdP via SAML. Currently, Microsoft only supports Azure AD as the IdP for Power BI SSO.

3. When the user connects to Snowflake, the Power BI service asks Azure AD to give it a token for Snowflake.

4. The Power BI service uses the embedded Snowflake driver to send the Azure AD token to Snowflake as part of the connection string.

5. Snowflake validates the token, extracts the username from the token, maps it to the Snowflake user, and creates a Snowflake session for the Power BI service using the user’s default role.

Creating a Power BI security integration¶

To use Power BI to access Snowflake data through SSO, it is necessary to create a security integration for Power BI using CREATE SECURITY INTEGRATION as shown below.

The security integration must have the correct value for the external_oauth_issuer parameter. Part of this value maps to your Azure AD tenant. You can find this value in the About section of your Power BI tenant.

If your organization has an advanced deployment of the Power BI service, then check with your Azure AD administrator to get the correct value of the Azure AD tenant to use in constructing the Issuer URL.

For example, if your Azure AD tenant ID is a828b821-f44f-4698-85b2-3c6749302698, then construct the AZURE_AD_ISSUER value similar to https://sts.windows.net/a828b821-f44f-4698-85b2-3c6749302698/. It is important to include the forward slash (i.e. /) at the end of the value.

After constructing the value for AZURE_AD_ISSUER, execute the CREATE SECURITY INTEGRATION command.

If your Snowflake account or Microsoft Power BI service is in the Microsoft Azure Government cloud region, set the external_oauth_audience_list property value to https://analysis.usgovcloudapi.net/powerbi/connector/Snowflake.

Security integration for Microsoft Power BI

create security integration powerbi
    type = external_oauth
    enabled = true
    external_oauth_type = azure
    external_oauth_issuer = '<AZURE_AD_ISSUER>'
    external_oauth_jws_keys_url = 'https://login.windows.net/common/discovery/keys'
    external_oauth_audience_list = ('https://analysis.windows.net/powerbi/connector/Snowflake', 'https://analysis.windows.net/powerbi/connector/snowflake')
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_snowflake_user_mapping_attribute = 'login_name'

Copy

Microsoft Azure Government security integration for Microsoft Power BI

create security integration powerbi_mag
    type = external_oauth
    enabled = true
    external_oauth_type = azure
    external_oauth_issuer = '<AZURE_AD_ISSUER>'
    external_oauth_jws_keys_url = 'https://login.windows.net/common/discovery/keys'
    external_oauth_audience_list = ('https://analysis.usgovcloudapi.net/powerbi/connector/Snowflake', 'https://analysis.usgovcloudapi.net/powerbi/connector/snowflake')
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_snowflake_user_mapping_attribute = 'login_name'

Copy

create security integration powerbi
    type = external_oauth
    ...
    external_oauth_snowflake_user_mapping_attribute = 'email_address';

Using Power BI SSO with B2B guest users¶

To allow Azure AD business to business (i.e. B2B) guest users to access Snowflake using SSO from Microsoft Power BI, set the EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM property value to 'unique_name'. For example:

create security integration powerbi
  type = external_oauth
  enabled = true
  external_oauth_type = azure
  external_oauth_issuer = '<AZURE_AD_ISSUER>'
  external_oauth_jws_keys_url = 'https://login.windows.net/common/discovery/keys'
  external_oauth_audience_list = ('https://analysis.windows.net/powerbi/connector/Snowflake', 'https://analysis.windows.net/powerbi/connector/snowflake')
  external_oauth_token_user_mapping_claim = 'unique_name'
  external_oauth_snowflake_user_mapping_attribute = 'login_name';

Copy

For more information, see Understand the B2B User.

Modifying Your External OAuth Security Integration¶

You can update your External OAuth security integration by executing an ALTER statement on the security integration.

For more information, see ALTER SECURITY INTEGRATION (External OAuth).

Using secondary roles with Power BI SSO to Snowflake¶

The desired scope for the primary role is passed in the external token. This role is a specific role that was granted to the user (session:role:<role_name>).

By default, the default secondary roles for a user (i.e. the DEFAULT_SECONDARY_ROLES user property) are not activated in the session.

To activate the default secondary roles for a user in a session and allow executing the USE SECONDARY ROLES command while using External OAuth, complete this step:

1. Configure the security integration for the connection. Set the EXTERNAL_OAUTH_ANY_ROLE_MODE parameter value to either ENABLE or ENABLE_FOR_PRIVILEGE when you create the security integration (using CREATE SECURITY INTEGRATION) or later (using ALTER SECURITY INTEGRATION).

Using Client Redirect with Power BI SSO to Snowflake¶

Snowflake supports using Client Redirect with Power BI SSO to Snowflake.

For more information, see Redirecting client connections.

Using replication with Power BI SSO¶

Snowflake supports replication and failover/failback of the External OAuth security integration from a source account to a target account.

For details, see Replication of security integrations & network policies across multiple accounts.

Connecting to Snowflake from Power BI¶

For more details on how to connect to Snowflake from Power BI, refer to the Power BI documentation.

• Power BI Desktop documentation

• Power BI Service documentation

Using network policies with External OAuth¶

Currently, network policies cannot be added to an External OAuth security integration, which means that you cannot define a network policy that applies only to the Power BI integration. However, you can still implement network policies that apply broadly to the entire Snowflake account. For information about the Microsoft IP range that should be included in the network policy, see the Prerequisites section (in this topic).

Error messages¶

The following table describes error messages Snowflake returns while a user authenticates in Power BI:

Login history¶

If a user is able to access Power BI but not instantiate a Snowflake session, you can determine when the user last accessed Snowflake by running the following commands using any supported connector or the Snowflake web interface. Note that only successful authentications are logged.

use role accountadmin;
select *
from table(information_schema.login_history(dateadd('hours',-1,current_timestamp()),current_timestamp()))
order by event_timestamp;

For each result, evaluate the USER_NAME and FIRST_AUTHENTICATION_FACTOR columns.

• The USER_NAME value should align with the attribute mappings described the Prerequisites section.

• The FIRST_AUTHENTICATION_FACTOR should be set to OAUTH_ACCESS_TOKEN.