# 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:

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 (i.e users with the ACCOUNTADMIN system role) and security administrator (i.e users with the SECURITYADMIN system role) 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.

## Considerations¶

Using the Power BI gateway

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.

Without the Power BI gateway

AWS Privatelink and Azure Private Link are supported. For the Power BI Service and Power BI Desktop, create a network policy to allow the Azure Active Directory public IP address ranges. Note that network policies have a 100,000 character limit for the allowed IP addresses.

Tokens and Keys

Snowflake tries to verify Azure Active Directory through the URL value in the external_oauth_jws_keys_url property (shown below) or through the allowed IP addresses in the network policy, if the network policy exists. Microsoft updates its tokens and keys every 24 hours. For more information on the Microsoft updates, see Overview of tokens in Azure Active Directory B2C.

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

These examples also use the ANY role, which allows for role switching. For more information, see Using ANY Role with Power BI SSO to Snowflake.

Security integration for Microsoft Power BI

create security integration powerbi
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_audience_list = ('https://analysis.windows.net/powerbi/connector/Snowflake')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_any_role_mode = 'ENABLE';


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_audience_list = ('https://analysis.usgovcloudapi.net/powerbi/connector/snowflake')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_any_role_mode = 'ENABLE';


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


### 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_audience_list = ('https://analysis.windows.net/powerbi/connector/Snowflake')
external_oauth_token_user_mapping_claim = 'unique_name'


### Modifying Your External OAuth Security Integration¶

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

### Using ANY Role with Power BI SSO to Snowflake¶

In the configuration step to create a security integration in Snowflake, the OAuth access token includes the scope definition. Therefore, at runtime, using the External OAuth security integration allows neither the OAuth client nor the user to use an undefined role in the OAuth access token.

After validating the access token and creating a session, the ANY role can allow the OAuth client and user to decide its role. If necessary, the client or the user can switch to a role that is different that the role defined in the OAuth access token.

To configure ANY role, define the scope as SESSION:ROLE-ANY and configure the security integration with the external_oauth_any_role_mode parameter. This parameter can have three possible string values:

• DISABLE does not allow the OAuth client or user to switch roles (i.e. use role <role>;). Default.

• ENABLE allows the OAuth client or user to switch roles.

• ENABLE_FOR_PRIVILEGE allows the OAuth client or user to switch roles only for a client or user with the USE_ANY_ROLE privilege. This privilege can be granted and revoked to one or more roles available to the user. For example:

grant USE_ANY_ROLE on integration external_oauth_1 to role1;

revoke USE_ANY_ROLE on integration external_oauth_1 from role1;


Define the security integration as follows:

create security integration external_oauth_1
type = external_oauth
enabled = true
external_oauth_any_role_mode = 'ENABLE'
...


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

## 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

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.

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.

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.

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 *

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.