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:
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.
The following diagram summarizes the authorization flow to instantiate a Snowflake session from Power BI:
The user logs into Power BI service using Microsoft Azure Active Directory (Azure AD).
(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.
When the user connects to Snowflake, the Power BI service asks Azure AD to give it a token for Snowflake.
The Power BI service uses the embedded Snowflake driver to send the Azure AD token to Snowflake as part of the connection string.
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.
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.
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
addressPrefixeslist. Use these IP address ranges to create or update a network policy in Snowflake.
addressPrefixeslist 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.
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.
name, or the
upnattribute. If the
login_nameattribute is not defined, then the process defaults to the
- With the Power BI gateway
AWS PrivateLink and Azure Private Link are supported. If it is necessary to use either of these two services to connect to Snowflake, use the on-premises gateway to connect.
- Without the Power BI gateway
AWS PrivateLink and Azure Private Link are not 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_urlproperty (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.
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¶
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
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_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' 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_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' external_oauth_any_role_mode = 'ENABLE';
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
matches the user’s email field instead of the
login_name in Snowflake, then replace
email_address. For example:
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') external_oauth_token_user_mapping_claim = 'unique_name' external_oauth_snowflake_user_mapping_attribute = 'login_name';
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.
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:
DISABLEdoes not allow the OAuth client or user to switch roles (i.e.
use role <role>;). Default.
ENABLEallows the OAuth client or user to switch roles.
ENABLE_FOR_PRIVILEGEallows the OAuth client or user to switch roles only for a client or user with the
USE_ANY_ROLEprivilege. 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' ...
Using Secondary Roles with Power BI SSO to Snowflake¶
The desired scope for the primary role is passed in the external token: either the default role for the user (
a specific role that was granted to the user (
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:
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.
Connecting to Snowflake from Power BI¶
For more details on how to connect to Snowflake from Power BI, refer to the Power BI documentation.
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.
The following table describes error messages Snowflake returns while a user authenticates in Power BI:
Invalid access token or audience value.
Failed to update data source credentials: ODBC:ERROR  Invalid OAuth access token. [<number>].
Verify that the
AAD user not found in Snowflake account.
Failed to update data source credentials: ODBC:ERROR  Incorrect username or password was specified.
Verify that the user exists in Snowflake (either the
Snowflake user present, but disabled.
Failed to update data source credentials: ODBC:ERROR  User access disabled. Contact your local system administrator.
In Snowflake, run
Snowflake receives an expired AAD token from Power BI.
Failed to update data source credentials: ODBC:ERROR  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  OAuth Authz Server Integration is not enabled.
Default role is not set for the user.
Failed to update data source credentials: ODBC: ERROR  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 * from table(information_schema.login_history(dateadd('hours',-1,current_timestamp()),current_timestamp())) order by event_timestamp;
For each result, evaluate the
USER_NAMEvalue should align with the attribute mappings described the Prerequisites section.
FIRST_AUTHENTICATION_FACTORshould be set to