Turning on 2FA breaks PowerBI automation because of Microsoft.AnalysisServices.Authentication

[yoDon]

Issue:

1. 2FA not compatible with Microsoft.AnalysisServices.Authentication
2. 2FA not compatible with PowerBI automation using Microsoft.AnalysisServices.NetCore.retail.amd64

Steps to reproduce:

1. Set up Developer sandbox for PowerBI web premium and AAD
   1. Register for a free 60 day Microsoft developer sandbox to test PowerBI integration (that sandbox comes pre-configured with 2fa enabled) as described at https://radacad.com/power-bi-sandbox-an-environment-to-learn-power-bi-service-for-free.
   2. Go to https://powerbi.com and sign in as your sandbox user.
   3. In the upper right corner of the powerbi tab, select your avatar and click the Start Trial button.
   4. Switch your Power BI account to a premium account.
2. Get the URL for a Power BI Workspace Connection (eg. powerbi://api.powerbi.com/v1.0/myorg/whatever)
   1. Create a new workspace with premium access (automation doesn't work with "My Workspace").
   2. Create a data source and report in the new workspace.
   3. Click the Workspaces icon on the left of the power bi tab
   4. Mouse-over the name of the new workspace
   5. Click on the "..." icon and select "Workspace Settings"
   6. Select the "Premium" Workspace Settings
   7. Scroll to the bottom of the window and copy the Workspace Connection URL
3. Attempt to interact with the Power BI Workspace from C#
   1. See code below

Attempt 1: server.Connect()

Code:

using Microsoft.AnalysisServices.Hosting;
using Microsoft.AnalysisServices.Tabular;

...

var connectionString = "DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;User ID=whatever@whatever.onmicrosoft.com;Password=whatever;";
Server server = new Server();
server.Connect(connectionString);

Result: Exception: "A device-code authentication flow is needed and a notification callback was not provided!

Unable to obtain authentication token using the credentials provided. Your Active Directory tenant administrator requires Multi-Factor Authentication."

After lots of debugging into the compiled Microsoft.AnalysisServices code I was able to find the undocumented parameter through which callers are supposed to provide a notification callback. The missing parameter that needs to be provided to the Server constructor to prevent this exception is OnAuthenticationByDeviceCode.

Attempt 2: OnAuthenticationByDeviceCode

Code:

using Microsoft.AnalysisServices.Hosting;
using Microsoft.AnalysisServices.Tabular;

...

var connectionString = "DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;User ID=whatever@whatever.com;Password=whatever;";
Server server = new Server()
{
    OnAuthenticationByDeviceCode = (AuthenticationByDeviceCodeNotification arg) =>
    {
        Console.WriteLine("OnAuthenticationByDeviceCode");
    }
};
server.Connect(connectionString);

Result: Exception: "Unable to obtain authentication token using the credentials provided. Your Active Directory tenant administrator requires Multi-Factor Authentication.

Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access."

Attempt 3: User ID and invalid Password

Code:

using Microsoft.AnalysisServices.Hosting;
using Microsoft.AnalysisServices.Tabular;

...

var connectionString = "DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;User ID=whatever@whatever.com;Password=invalid";
Server server = new Server()
{
    OnAuthenticationByDeviceCode = (AuthenticationByDeviceCodeNotification arg) =>
    {
        Console.WriteLine("OnAuthenticationByDeviceCode");
    }
};
server.Connect(connectionString);

Result: Exception: "Unable to obtain authentication token using the credentials provided. If your Active Directory tenant administrator has configured Multi-Factor Authentication or if your account is a Microsoft Account, please remove the user name and password from the connection string, and then retry. You should then be prompted to enter your credentials."

Attempt 4: No User ID or Password

Code:

using Microsoft.AnalysisServices.Hosting;
using Microsoft.AnalysisServices.Tabular;

...

var connectionString = "DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;";
Server server = new Server()
{
    OnAuthenticationByDeviceCode = (AuthenticationByDeviceCodeNotification arg) =>
    {
        Console.WriteLine("OnAuthenticationByDeviceCode");
    }
};
server.Connect(connectionString);

Result: Exception: "Authentication failed: User ID and Password are required when user interface is not available."

Variant on attempt 4: I also tried providing empty User ID and Password properties in the connectionString ("DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;User ID=;Password=;") but that did not change the result.

Attempt 5: AAD Application ID

Code:

using Microsoft.AnalysisServices.Hosting;
using Microsoft.AnalysisServices.Tabular;

...

var connectionString = $"DataSource=powerbi://api.powerbi.com/v1.0/myorg/whatever;User ID=app:{ApplicationId}@{TenantId};Password={AzureADClientSecretValue};";
Server server = new Server()
{
    OnAuthenticationByDeviceCode = (AuthenticationByDeviceCodeNotification arg) =>
    {
        Console.WriteLine("OnAuthenticationByDeviceCode");
    }
};
server.Connect(connectionString);

ApplicationId, TenantId, and AzureADClientSecretValue came from creating an AzureAD Application, and creating a Client Credentials Secret within that AAD Application.

Result: Exception: "PowerBI Request Failed.

Action: Resolving the PBI workspace

Description:

Technical Details:

The remote server returned an error: (401) Unauthorized."