Using SSO Between Power BI and Snowflake

Customers love Snowflake Cloud Data Platform for its secure and easy access to any data with infinite scalability. Single sign-on (SSO) is one of the features that enable secure and easy access to data. We partnered with Microsoft to bring this seamless access experience to you when connecting to Snowflake from Microsoft Power BI. To help with this, we’re announcing a preview of SSO between Power BI and Snowflake.

Microsoft has released a native Power BI connector for Snowflake that supports SSO for users connecting to Snowflake from Power BI Desktop or the Power BI service. It solves an important use case for joint customers to integrate their identity provider (IdP) for authentication, such as Azure AD (AAD), Okta, and others, while providing a seamless SSO experience.

We’ll first start with Power BI Desktop and then move to the Power BI service.

If you use Power BI Desktop version December 2019 and above, you’ll see an additional option called Microsoft Account when connecting to Snowflake.

If you connect using the Microsoft Account option, an in-app browser window will open redirecting you to authenticate using your AAD tenant. If AAD is your main IdP, you will need to enter your AAD or linked Active Directory credentials. If your identity admin has integrated AAD with another IdP such as Okta, you’ll be redirected to that IdP for authentication.

To achieve the experience described above, you need to perform a one-time setup in Snowflake. Log in as an ACCOUNTADMIN in your Snowflake account, and create the security integration described in this doc. Ensure that the Power BI user exists in Snowflake and that it also matches the user in AAD. You can set up user-matching rules in multiple ways through security integration settings, but the most common way is to use the UPN value in AAD to match with the login_name of the Snowflake user. Now, you can use AAD or the chained IdP for the connection from Power BI into Snowflake for both direct query and import experiences.

Here are a few similarities and differences between older versions of Power BI Desktop (versions prior to Dec. 2019) and newer versions:

• The role used for the connection is the default_role set for the Snowflake user. If the default_role isn’t set for the user, the public role is used for the connection.
• If you are using AWS PrivateLink or Azure Private Link to connect to Snowflake, you can use your account’s PrivateLink or Private Link URL to connect from Power BI Desktop, but not from the Power BI service.
• If you are using the generic ODBC connection to connect to Snowflake (such as to pass additional connection parameters not exposed through Power BI Desktop), you can continue to do so using local authentication, but you can’t make use of the new authentication using the Microsoft Account feature.

• With older versions of Power BI Desktop, you had to download the Snowflake driver. With Power BI Desktop Dec. 2019 and above versions, you no longer have to download the Snowflake driver because it is embedded with the desktop version.
• When you upgrade Power BI Desktop from an older version to the Dec. 2019 version or a later version, the connection settings are automatically picked up by the embedded Snowflake driver. So, no additional steps are required for the Power BI Desktop user unless you want them to switch from local authentication to SSO powered by the Microsoft Account option.

Next, let's look at the Power BI service.

When you publish a Power BI Desktop data set or report to the Power BI service, the user credentials don’t propagate to the Power BI service. You need to set up authentication for the connection separately in the Power BI service. In the past, you had to set up an on-premises gateway, download the Snowflake ODBC driver, and set up the connection with Snowflake using a service account. Now, the Power BI service embeds the Snowflake ODBC driver and directly connects to the Snowflake account, eliminating the need for setting up the gateway.

If you want to continue using local authentication for Snowflake but you just want to switch from an on-premises gateway to the direct connection, you can disable the gateway in the Power BI service by selecting “Edit credentials” for the data set and picking “Basic” as the authentication method, as shown in the screenshots below:

If you want to use a common service account to connect to Snowflake while not using local authentication (that is, a password in Snowflake), select OAuth from the “Authentication method” drop-down menu, but don’t select the checkbox for “End users use their own OAuth2 credentials when accessing this data source via DirectQuery.” This allows you to use AAD (or the chained IdP) to authenticate the service account user to Snowflake.

Next, let’s cover the SSO experience for the business users of your Power BI tenant when they access Snowflake in DirectQuery mode. Let’s say you have 1,000 users in your organization who need to connect to Snowflake, and you want them to connect using SSO so they aren’t presented with any additional authentication prompts. With SSO, you can have each user’s connection scoped down to their role instead of using the over-privileged role used with a service account. You can also track queries issued by each of these 1,000 users in Snowflake. To achieve this, use the following instructions.

First, you’ll need to ask your Power BI administrator to enable the “Snowflake SSO” option in the Power BI Admin portal.

Then, set up the security integration in Snowflake following the information in this doc. If you’ve already set it up for Power BI Desktop, you can skip this step. The same integration is used for both Power BI Desktop and the Power BI service. Ensure that the matching user attributes in the security integration are set up correctly, so incoming users specified in the AAD token can be used to look up a user in Snowflake.

Next, create Power BI service business users who will be making direct queries into Snowflake. A good practice is to set up automated user synching between AAD or your IdP using System for Cross-domain Identity Management (SCIM). You can read more about SCIM here.

Now, instruct your report publishers to navigate to Workspace → dataset and select “Edit credentials” → OAuth from the “Authentication method” drop-down menu. Then instruct them to select the checkbox for “End users use their own OAuth2 credentials when accessing this data source via DirectQuery.”

Voila! Now, your business users can connect from the Power BI service to Snowflake using SSO.

Notes:

• When you select the checkbox, Power BI assumes you are using AAD (OAuth2) credentials for DirectQuery mode.
• If you have an account-level network policy set on your Snowflake account, you will need to add to that network policy the IP range of the Azure data center where your Power BI tenant is running, or you can set it for only the users who will be connecting to Snowflake from the Power BI service. Refer to the Power BI documentation or contact Power BI support to get Power BI tenant IP range.
• AWS PrivateLink and Azure Private Link are not supported with direct connection from the Power BI service to Snowflake. If you need to use either of these two services, use the Power BI gateway from your network to connect to Snowflake’s PrivateLink or Private Link account URL. You won’t be able to make use of the SSO feature with the gateway.

AAD powers the login for the Power BI service. So, when users log in to the Power BI service, the service possesses an AAD token. This token was generated for use by the Power BI service only, and it can’t be sent “as is” to Snowflake. So, the Power BI service exchanges this token with AAD to get a new token that can be sent to Snowflake. Power BI sends the new token to Snowflake, which validates the token using the claims present in it and matches it with the security integration and the user. Here’s a detailed flow diagram of the process.

1. User logs in to the Power BI service using AAD.
2. (Optional) If a customer's IdP is Okta, Active Directory Federation Services (ADFS), and so on, AAD takes the user through Security Assertion Markup Language (SAML) authentication process with the IdP before logging the user into the Power BI service.
3. When a user connects to Snowflake, the Power BI service asks AAD to give it a token for Snowflake.
4. The Power BI service uses the embedded Snowflake (SF) driver to send the AAD 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 database (db) session for the Power BI service. Then, the Power BI service uses the db session to send queries until the idle session timeout (4 hours) occurs. When the session times out, then Power BI repeats steps 3–5 to get a new db session seamlessly.

Snowflake uses the issuer (iss) claim in the token to find the matching security integration. From the security integration, it pulls the signing certificate URL, and it uses the certificate to verify the token signature. Once the integrity of the token is verified using signature verification, it checks for an audience value match and token expiry, and then it pulls the username from the token to match with the Snowflake user. Once the match is found, it uses the user’s default_role to create the Snowflake session.

Yes, scheduled reports in the Power BI service are supported with SSO. At the scheduled time when Power BI runs the report, it uses the same mechanism to obtain a new AAD token for Snowflake and sends it to get a Snowflake session required to run the query.

For more information, see the following:

• Snowflake documentation
• Power BI Service documentation
• Power BI Desktop documentation