Announcing support for service principals using the XMLA endpoint in Power BI Premium
With support for service principals over the Analysis Services protocol (aka XMLA), Power BI Premium closes a gap with Azure Analysis Services. In Power BI, you can now use service principals to automate common tasks such as deploying models, performing a data refresh, and applying model changes. Permissions are assigned to service principals through workspace membership, much like regular user accounts. Note that you must use a modern workspace experience. Legacy, group-based workspaces don’t support this capability. You can also grant service principals permissions for individual datasets. For information how to create a service principal in Azure Active Directory (AAD) and add it to a Power BI workspace, refer to the article Automate Premium workspace and dataset tasks with service principals in the product documentation.
In a nutshell, for you to be able to use service principals, a Power BI service administrator must enable the tenant setting to allow service principals to use Power BI APIs, as covered under Developer Settings in the product documentation. Next, having created a service principal for your client application, hosted service, or automation tools and scripts, you must grant the necessary access permissions at the workspace or dataset level, as the following screenshot illustrates.
In addition, your workspace must reside on a Premium capacity with the XMLA endpoint enabled, preferably in Read/Write mode. It is also advantageous to work with datasets using the enhanced metadata format. This is particularly required if you want to perform processing or other read/write operations. If you haven’t done so, enable the corresponding setting in Power BI Desktop under Preview Features, as in the following screenshot, and then publish your PBIX solution again to the Power BI service.
With all prerequisites in place, you can specify the application identity information in the connection string as follows:
- User ID=<app:appid@tenantid>
- Password=<application secret>
For example:
Data Source=powerbi://api.powerbi.com/v1.0/myorg/Contoso Team;Initial Catalog=PowerBI_Dataset;User ID=app:91ab91bb-6b32-4f6d-8bbc-97a0f9f8906b@19373176-316e-4dc7-834c-328902628ad4;Password=6drX...;
Constructing the connection string manually is necessary if you work with AMO and ADOMD, but you can also use PowerShell cmdlets as the following script illustrates.
Param (
[Parameter(Mandatory=$true)] [String] $AppId,
[Parameter(Mandatory=$true)] [String] $AppSecret,
[Parameter(Mandatory=$true)] [String] $TenantId
)
$PWord = ConvertTo-SecureString -String $AppSecret -AsPlainText -Force
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $AppId, $PWord
Invoke-ProcessTable -Server "powerbi://api.powerbi.com/v1.0/myorg/Contoso Team" -TableName "MyTable" -Database "PowerBI_Dataset" -RefreshType "Full" -ServicePrincipal -ApplicationId $AppId -TenantId $TenantId -Credential $Credential
And that’s it for a quick introduction of support for service principals over the Analysis Services protocol in Power BI Premium. We hope that this capability enables you to extend the reach of your custom client applications, hosted services, or automation tools and scripts to include the datasets you have on Power BI Premium capacities while at the same time lowering your maintenance overhead associated with these custom solutions.
