Power BI SSO to Snowflake

This topic describes how to use Microsoft Power BI to instantiate a Snowflake session and access Snowflake using single sign-on (SSO).

In this Topic:

Overview

Snowflake allows Microsoft Power BI users to connect to Snowflake using Identity Provider credentials and an OAuth 2.0 implementation to provide an SSO experience to access Snowflake data.

This feature eliminates the need for on-premises Power BI Gateway implementations since the Power BI service uses an embedded Snowflake driver to connect to Snowflake.

General Workflow

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

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

  2. (Optional) If the identity provider is not Azure AD, then Azure AD verifies the user through SAML authentication before logging the user into the Power BI service.

  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.

Prerequisites

For your Snowflake account, please verify the following before using the Power BI SSO feature:

  • In Snowflake, if you’re using Network Policies, you can allow the Microsoft Azure IP range that includes the Azure region where your Snowflake account is hosted and any additional Azure regions as necessary.

    Important

    To create a network policy that is specific to Power BI for the Azure region where your Snowflake on Azure account is located, search the JSON download from Microsoft for your region.

    For example, if your Snowflake on Azure account is located in the Canada Central region, search the JSON download for PowerBI.CanadaCentral. Select the IP address ranges from the addressPrefixes list. Use these IP address ranges to create or update a network policy in Snowflake.

    If the addressPrefixes list is empty, please contact Microsoft to request an update.

    If you are using multiple Microsoft Azure services (e.g. Power BI, SCIM), contact your Azure administrator to verify the correct IP address ranges to ensure the Snowflake network policy contains the correct IP address ranges to allow users to access Snowflake.

  • If it is necessary to use either the Snowflake ACCOUNTADMIN or SECURITYADMIN role for a user, contact Snowflake Support.

    Note

    By default, the account administrator (ACCOUNTADMIN) and security administrator (SECURITYADMIN) roles are blocked from using Microsoft Power BI to instantiate a Snowflake session. If you have a business need to allow these roles, and your security team is comfortable with allowing it, please contact Snowflake Support to request that these roles be allowed for your account.

  • Either the login_name, name, or the email attribute for the user in Snowflake must map to the Azure AD upn attribute. If the login_name attribute is not defined, then the process defaults to the name attribute.

Limitations

Currently, the Snowflake Power BI SSO feature has the following limitations:

  • AWS PrivateLink and Azure Private Link are not supported. If it is necessary to use either of these two services to connect to Snowflake, use the on-premises gateway to connect. This pathway will not provide single sign-on.

Getting Started

This section explains how to create a Power BI security integration in Snowflake and how to access Snowflake through Power BI.

Creating a Power BI Security Integration

Note

This step is not required if you are using the Power BI gateway for Power BI service to connect to Snowflake or are using your Snowflake username and password for authentication.

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. Be sure to set the value for the external_oauth_audience_list security integration parameter correctly based on whether or not your Snowflake account is located in the Microsoft Azure Government cloud region.

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')
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';

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')
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';

Important

Only account administrators (i.e. users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

The security integration parameter values are case-sensitive, and the values you put into the security integration must match those values in your environment. If the case does not match, it is possible that the access token will not be validated, resulting in a failed authentication attempt.

Verify that all parameter values are an exact match. For example, if the <AZURE_AD_ISSUER> URL value does not end with a backslash and the security integration is created with a backslash character at the end of the URL, an error message will occur. It would then be necessary to drop the security integration object (using DROP INTEGRATION) and then create the object again with the correct URL value (using CREATE SECURITY INTEGRATION).

In your environment, if the user’s UPN attribute value matches the user’s email field instead of the login_name in Snowflake, then replace login_name with email_address. For example:

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

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.

Connecting to Snowflake from Power BI

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

Using Network Policies with External OAuth

Currently, network policies cannot be added to your External OAuth security integration.

If your use case requires OAuth and a Snowflake network policy, use Snowflake OAuth.

For more information, see OAuth and Network Policies.

Troubleshooting

  • Warehouse resumption. If a given user attempts to use a suspended warehouse, Microsoft Power BI displays an error message that is not described in Error Messages. Verify, and if necessary, configure the warehouse to resume automatically to resolve the error message. For more information, see Starting/Resuming a Warehouse.

  • While attempting to connect Power BI to Snowflake, errors may occur. Depending on the error message it may require troubleshooting in Microsoft, Snowflake, or both.

    • Error Messages describes common error messages Snowflake can return that display in Power BI.

    • Login History describes how to use Snowflake to verify whether or when a user last accessed Snowflake.

Error Messages

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

Behavior

Error Message

Troubleshooting Action

Invalid access token or audience value.

Failed to update data source credentials: ODBC:ERROR [28000] Invalid OAuth access token. [<number>].

Verify that the external_oauth_issuer parameter contains the correct value. . In Azure AD, verify the access token is current.

AAD user not found in Snowflake account.

Failed to update data source credentials: ODBC:ERROR [28000] Incorrect username or password was specified.

Verify that the user exists in Snowflake (either the name or login_name attribute value matches with the user’s UPN value in Azure AD).

Snowflake user present, but disabled.

Failed to update data source credentials: ODBC:ERROR [28000] User access disabled. Contact your local system administrator.

In Snowflake, run desc user <username> to verify if the disabled attribute is set to true. If you want this user to be allowed, run alter user <username> set disabled = true;. Try to access Snowflake from Power BI again.

Snowflake receives an expired AAD token from Power BI.

Failed to update data source credentials: ODBC:ERROR [28000] OAuth access token expired. [<number>].

Contact Snowflake Support.

Security integration not created or disabled in Snowflake account.

Failed to update data source credentials: ODBC:ERROR [28000] OAuth Authz Server Integration is not enabled.

Run desc <security_integration_name> to verify or recreate the security integration.

Default role is not set for the user.

Failed to update data source credentials: ODBC: ERROR [28000] No default role has been assigned to the user, contact a local system administrator to assign a default role and retry.

Set default role for the user.

Default role for the user is not granted to the user.

Test failed because of 250001 (08001): Failed to connect to DB: <host>. User’s configured default role ‘<ROLE>’ is not granted to this user. Contact your local system administrator, or attempt to login using a CLI client with a connect string selecting another role, e.g. PUBLIC.

Check the default role for the user and grant it to them.

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.