The Snowflake Data Cloud and Power BI are currently two of the hottest technologies in the data landscape, so it is no surprise that we see many enterprises using these two tools in tandem. Because these two tools are so popular, it is important for organizations to understand how they play together.
In this blog, I am going to talk about how to set up your Azure AD SSO authentication with Snowflake when using Power BI. We hope that these step-by-step instructions will ease the burden of integrating these two very powerful platforms.
Why Snowflake and Power BI Specifically?
As mentioned in the opening paragraph, Snowflake and Power BI are two of the hottest names in their respective fields. For a few years now, Snowflake has been taking the cloud data industry by storm. This robust platform showed huge growth and adoption which resulted in the largest software IPO in history in late 2020.
On the other side of the equation, Microsoft’s Power BI has been making huge inroads in the data analytics and visualization space. A lot of this has been due to the heavy investment that Microsoft has put into the tool that features monthly releases with an ongoing array of new features such as Field Parameters, Datamarts, and the new Azure DevOps extension.
With both of these platforms being popular in their respective fields, it makes sense that there are so many companies using them together. But just because they are often used together doesn’t mean that they integrate seamlessly.
Power BI being a Microsoft product has more seamless integration with tools like Azure Synapse Analytics, but do not fear – this blog post is here to help you on your journey of making these two platforms work together!
What is SSO, and Why Does it Matter?
Single Sign On (SSO) is an authentication method that enables an end user to authenticate into an application with one set of authentication credentials. Instead of having to authenticate into every database and dataset with individual credentials, a properly configured SSO enables one set of credentials to grant access to all of the necessary applications.
If you are deploying a solution across your organization, SSO is the best way to ensure a seamless user experience which will help with the adoption of said solution. The most common use case we see when using Snowflake and Power BI together is an organization that already leverages the 365 suite, which means that they are most likely already leveraging Azure Active Directory groups for things like licensing and access.
Because of this, we are going to show how to set up SSO to Snowflake using Azure AD groups in Power BI.
Steps to Enable SSO Across Platforms
Now that we understand why we’re talking about these two tools and our end objectives, let’s dive into the steps to enable this functionality. There are five main steps that you will need to take in order to achieve success.
- Configure Azure for SSO on Snowflake
- Configure Snowflake for SSO with Azure AD
- Configure Power BI Service for SSO with Snowflake
- Configure data sets with gateways on Virtual Machines (VMs)
- Load, Deploy, and Refresh datasets using gateways
To understand how these two systems will talk to each other, check out the diagram below from Snowflake’s documentation.
In short, when you authenticate with Azure AD in Power BI, a token is created that is then passed to Snowflake. Because you will have configured username mapping in Snowflake, this token is then used to authenticate the username in Snowflake and then passed back to Power BI once the authentication has been completed.
Configure Azure for SSO on Snowflake
Here are some step-by-step instructions to set up SSO in Azure:
Configure Snowflake for SSO with Azure AD
Once you’ve set up the SAML component in Azure and assigned users/groups to the application, now it’s time to set up Azure AD SSO on the Snowflake side of things. In Snowflake you’ll need to run a script that references the token that is created in Azure in order to map usernames from Azure to Snowflake. The script that you’ll need to run is below:
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';
Only Snowflake users who are account administrators or have a role with the global create integration privilege can run this script. You can find more information on how to set up Azure AD SSO in Snowflake, here.
A common question we get about setting up Snowflake SSO is whether or not Azure Private Link is supported. The answer is YES, this is supported. Once you have set up SSO with the normal set up, it should not be a big lift to get SSO set up with Private Link. You can find specific documentation from Snowflake about this, here.
Configure Power BI Service for SSO with Snowflake
Now that you have set up SSO capabilities on both the Snowflake and Azure sides, we need to make sure that we have set things up in the Power BI portal appropriately to support this capability.
Go into your Power BI Tenant Settings and go down to the Snowflake SSO and Azure AD Single Sign-On for Gateway settings. By enabling these two settings we are making sure of two things:
- The token information from Azure will be able to be sent to Snowflake
- We can use Azure AD SSO while utilizing an on-premise gateway. Please see the example below.
Configure Gateways and Test
Now that you’ve configured Azure, Snowflake, and Power BI to all accommodate SSO, you may need to install your on-premise gateway. Using a gateway is not necessary, but it is commonly used for security reasons for enterprise deployments and for traversing a private network (Azure Private Link as an example).
If you don’t have a need for gateways then you can start to connect to Snowflake and test your Microsoft authentication. Make sure that you’re signed into your account in the top right of Power BI Desktop and that you sue the Microsoft account selection when providing credentials.
If the SSO has not been set up properly, you will get an error message. The most common error message when access isn’t set up right is,
“We couldn’t authenticate with the credentials provided.”
This means that somewhere along the chain of authentication, the token or username is not validated properly. Make sure to check the setup on both the Azure and Snowflake sides then try again.
Tips and Reminders for Setting up SSO
Make sure to involve your Azure and Snowflake admins in the process because they may already have some of the infrastructure set up. These admins will most likely be the ones setting up the scripts and configurations, so make sure to coordinate with them to ensure that you are following the proper company procedures for making these changes.
Closing
Being two of the hottest platforms in the data industry, it’s no surprise that we see Power BI and Snowflake used together with a variety of clients. Setting up Azure AD SSO between the two tools can be confusing so we hope that this blog can help you in figuring out just how you need to configure your environment to make it work.
Once you are able to harness the power of Snowflake with Power BI, we know you’ll find it to be worth it!
As Snowflake Partner of the Year, phData is dedicated to enabling our clients with the power of Snowflake. Our Power Platform team is experienced and ready to help you in your Power BI & Snowflake journey. Make sure to reach out if you are needing assistance with any Power BI and Snowflake deployment!
