Power BI to Snowflake via AAD SSO with MFA

Question

We're trying to connect Power BI Desktop to our Snowflake instance using Azure Active Directory (AAD) Single Sign-on (SSO), and running into a snag. Our best guess at this time is that it's because we use multifactor authentication (MFA) in our AAD.

Preamble

We followed the instructions from Microsoft at this link: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-snowflake
And performed the prep work in Snowflake from this link: https://docs.snowflake.com/en/user-guide/oauth-powerbi.html
We specified the snowflake server contoso_dw.canada-central.azure.snowflakecomputing.com and the warehouse DEV_WH
We specified Direct Query because Import isn't supported (per https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-snowflake)
We validated that the user can log in to the snowflake UI directly using SSO
We're using Power BI desktop release 2.88.1385.0 64-bit (Dec 2020) <-- latest release at the time of writing.

Problem

We verified that the user is able to connect directly to snowflake using AAD SSO with MFA. We validated that the server and warehouse specified are the same in the snowflake UI and in Power BI Desktop. We specify the Microsoft account when prompted for credentials in Power BI, and it does prompt for a login through Microsoft which appears to succeed. Despite all of the above, we receive the following error:

ODBC: ERROR [28000] Incorrect username or password was specified.

We noticed that this specific error is listed here https://docs.snowflake.com/en/user-guide/oauth-powerbi.html#error-messages, but we've dismissed this as a red herring because the user can login with the same AAD SSO credentials directly into the Snowflake web UI.

Our suspicion is that this is because of the MFA requirement on our AAD. Has anyone else experienced the same behavior? Can anyone verify if this is an MFA side effect?

SSO is between user and AD. I don't see why that should affect Snowflake requesting an auth token from AD. I'm assuming you have setup snowflake network policy accordingly? — Mitch Wheat
Authentication is between User and AAD. SSO is between AAD and Snowflake via JWT or SAML (I think it's JWT). — HeyZiko
I don't know if this is really in scope of StackOverflow, but I have exact the same problem. Hopefully someone finds solution. — ex4

ex4 ex4 · Accepted Answer · 2021-02-09T16:36:47

SSO login to Snowflake portal isn't same as SSO in PowerBI.

You need to configure two OAuth app registrations in Azure portal as descripted in https://docs.snowflake.com/en/user-guide/oauth-azure.html . Read instructions carefully, you really need to create both app registrations as told on that article.

When you end up to "Step 4: Create an OAuth Authorization Server in Snowflake", use command below to to create security integration to Snowflake. It is from https://docs.snowflake.com/en/user-guide/oauth-powerbi.html . You have needed <AZURE_AD_ISSURE> info if you followed previous article.

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';

The PowerBI users cannot change their role, so make sure that their default role in Snowflake is the same as you configured in Step 1 to manifest OR configure your OAuth AppRole allow any role like this:

{
            "allowedMemberTypes": [
                "Application"
            ],
            "description": "PowerBI users",
            "displayName": "PowerBI Users",
            "id": "<your unique id here>",
            "isEnabled": true,
            "lang": null,
            "origin": "Application",
            "value": "session:ROLE:ANY"
        }

My last error message was that user doesn't have role granted (which they did for sure), but when I re-granted role to user on Snowflake it finally worked.

It is pain to get PowerBI + Snowflake + Azure AD SSO work together, but it is possible. And MFA works as well.

You probably need to add your AAD users to app you created in Step 2 in Azure portal Enterprise Apps. I didn't test that yet though, my own account works because I am an owner of the app I just created.

Power BI to Snowflake via AAD SSO with MFA

1 Answers