Power BI is on a roll these days, enabling even more Azure Analysis Service (Azure AS) capabilities through XMLA endpoints. Just a few days ago, we announced Power BI support for service principals over XMLA. Now, we are announcing support for user impersonation using the EffectiveUserName connection-string property. This is truly exciting because it opens the door for mass migrations of tabular Analysis Services solutions that rely on EffectiveUserName-based impersonation to Power BI. It also opens new options for administrative tools and scripts to connect to datasets on Power BI Premium using different user identities! Impersonating another user can be as easy as specifying that user’s UPN on the connection string. No Azure Active Directory (AAD) authentication or delegation token exchange necessary.
The following screenshots illustrate the approach. In Analysis Services, the EffectiveUserName connection-string property requires Analysis Services server admin permissions (rather than database admin). The equivalent in Power BI is a workspace admin. As you can see in the screenshot to the right, I am a workspace admin in my AdventureWorksTest workspace, and I added Christian as a workspace member. This workspace resides on Premium Per User (PPU), but it could also be hosted on a Premium capacity so that my sample application can connect to the dataset through XMLA endpoints.
As you can see in the code above, impersonating Christian is as easy as specifying Christian’s UPN as the EffectiveUserName and Power BI impersonates the request because I am a workspace admin. For your reference, below is the code for this little console app. The UserName() DAX function returns the name of the effective user, which provide a quick way to validate that Power BI applied the EffectiveUserName property.
using System;
using Microsoft.AnalysisServices.AdomdClient;
namespace EffectiveUserNameDemo
{
class Program
{
static void Main(string[] args)
{
using (AdomdConnection connection = new AdomdConnection())
{
connection.ConnectionString =
"Data Source=powerbi://api.powerbi.com/v1.0/myorg/AdventureWorksTest;initial catalog=AdventureWorks;" +
"EffectiveUsername=christian@contoso.com";
connection.Open();
using (AdomdCommand cmd = new AdomdCommand(
"EVALUATE SUMMARIZECOLUMNS (\"User Name\", UserName())", connection))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
}
}
Console.WriteLine();
Console.WriteLine("Press any key to exit!");
Console.ReadLine();
}
}
}
Note that the user account that you specify in the EffectiveUserName property must exist in your Azure Active Directory tenant and the account must have permissions to access the target workspace, otherwise Power BI cannot impersonate the user account. Note also that you cannot access Single Sign-On (SSO) enabled data sources in DirectQuery mode when using EffectiveUserName. However, if you have configured Row Level Security (RLS) in your dataset, you can use EffectiveUserName to quickly test that your RLS rules are applied correctly.
And that’s it for a whirlwind tour of EffectiveUserName support in XMLA endpoints on Power BI Premium. We have plans to add this capability also to the Power BI REST API for DAX Queries, so stay tuned for even more user-impersonation flexibility, exceeding the EffectiveUserName capabilities that SQL Server Analysis Server and Azure AS have to offer. If the lack of EffectiveUserName support in Power BI was holding you back from migrating your tabular workloads to Power BI, the road is clear, so move ahead in order to reap in all the enormous and ever-expanding capabilities that Power BI offers.