CODEWITHSUNDEEP
azure-active-directorypowerbisingle-sign-onsnowflake-cloud-data-platformmulti-factor-authentication
HeyZiko
HeyZiko

Reputation: 1720

Power BI to Snowflake via AAD SSO with MFA

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

  1. We followed the instructions from Microsoft at this link: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-snowflake
  2. And performed the prep work in Snowflake from this link: https://docs.snowflake.com/en/user-guide/oauth-powerbi.html
  3. We specified the snowflake server contoso_dw.canada-central.azure.snowflakecomputing.com and the warehouse DEV_WH
  4. We specified Direct Query because Import isn't supported (per https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-snowflake)
  5. We validated that the user can log in to the snowflake UI directly using SSO
  6. 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.

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?

Upvotes: 1

Views: 2789

Answers (1)

ex4
ex4

Reputation: 2448

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.

Upvotes: 1

Related Questions