Service principal access to dedicated capacity XMLA endpoint

Key takeaways

• You need a service principal for unattended XMLA writes: Refreshing or deploying a dataset from a pipeline or the command line can't use interactive sign-in, so a service principal is the only option on an MFA-enabled tenant.
• Start by enabling XMLA read/write on the workspace's capacity: Most Tabular Editor scenarios against a Premium or Embedded workspace need the XMLA endpoint set to Read Write first.
• Then register the service principal and grant it access: Create the app registration and secret, put it in a security group, allow that group to use the Power BI APIs, and make it a workspace admin.
• Finally, connect with a service principal connection string: Tabular Editor and its command line connect with the app ID, tenant ID, and secret to read, write, or deploy the model.

This summary is produced by the author, and not by AI.

Introduction

This article provides step-by-step instructions on how to enable service principal access to a Power BI workspace in dedicated capacity (Power BI Premium or Embedded A SKU).

In the following, I will try to highlight the minimal number of steps necessary to enable service principal authentication. You can find all the details in the official documentation: Automate Premium workspace and semantic model tasks with service principals.

Why a service principal?

In order to set up unattended jobs or pipelines that perform XMLA write operations (refresh, deploy, etc.) on a dataset in a Power BI workspace, we have to use a service principal. Specifically, if we want to use Tabular Editor’s command-line interface to perform a model deployment, we don’t have any other options, as the command line doesn't allow interactive authentication, which is required on any tenant that has multifactor authentication (MFA) enabled.

These are the steps we need to go through in order to set this up. Note, these steps have to be performed by a user with Power BI administrator and Azure Active Directory administrator permissions:

Enable XMLA read/write access on a Power BI workspace

For most scenarios that involve Tabular Editor, we need to enable XMLA read/write on our Power BI workspace.

1. In the Power BI admin portal, go to Capacity Settings. If your organization uses Power BI Premium, locate the capacity that hosts your workspace under the “Power BI Premium” tab. If your capacity is a Power BI Embedded or A SKU, locate it under the “Power BI Embedded” tab. Click on the capacity name.

2. Expand the “Workloads” section. Scroll down and locate the XMLA endpoint dropdown. Set it to “Read Write”. Note: At the time of this writing, there’s a bug that requires you to also disable the “Dataflows” workload. Click “Apply”.

3. At this point, you should be able to connect using interactive (personal) authentication and make changes to datasets in the workspace using Tabular Editor, provided your user is an administrator of the workspace. Use the following string as the “server name” when connecting:

powerbi://api.powerbi.com/v1.0/<organization name>/<workspace name>

Warning: Once you make a change to a dataset hosted in an XMLA write-enabled workspace using external tools such as Tabular Editor, downloading the original .pbix file from the dataset is no longer possible. XMLA read/write has since reached general availability, but this limitation on the Power BI Service remains; see the linked workaround if you need the file back.

Create a service principal

1. In the Azure Portal, go to Azure Active Directory. Take a note of the Tenant ID. You will need it later, when specifying the connection string.
2. Go to “App Registrations”, click “New registration”.

3. Provide a name for the service principal. Leave the account type setting as single tenant and the redirect URI blank. Hit “Register”.
4. Take a note of the Application (client) ID. This will also be needed later, when specifying the connection string.

5. Click on “Certificates & Secrets” and then “New client secret”. Description is optional. Set the expiration as desired (but remember that you’ll have to update any connection strings that use the service principal later on, when the secret expires).
6. Write down the secret. You won’t be able to retrieve it later on, and it is needed when specifying the connection string.

Create a security group and include the service principal

1. Go back to Azure Active Directory in the Azure Portal. Click on “Groups”. Then “New Group”.
2. Leave the “Group type” as “Security”, give it a name and an optional description.
3. Go to “Members” of the newly created group. Click “Add members” and then search for the service principal you created above, using its name as the filter string.

Enable service principal API access

1. Log in to PowerBI.com as a user with admin access (that is, the user has the “Power BI administrator” permission assigned in Azure Active Directory).
2. Go to the Admin Portal, click “Tenant Settings”.
3. Scroll down to locate the “Developer section”. Expand “Allow service principals to use Power BI APIs”.
4. Enable the setting. Under “Apply to”, make sure “Specific security groups (Recommended)” has been selected, and enter the name of the security group you created in the previous step. Click “Apply”.

Set service principal as workspace admin

1. Go to the Power BI Workspace. Click “Access”.
2. Type the name of the service principal into the email address field. Set the dropdown below to “Admin”. Click “Add”

Connect with Tabular Editor

Sometimes, it can take a few minutes for all of the settings in the above steps to come through, so go grab a cup of coffee. When you come back, you can test the service principal connection using Tabular Editor. Provide the following connection string as the “server name” when connecting:

Provider=MSOLAP;Data Source=<xmla endpoint>;User ID=app:<application id>@<tenant id>;Password=<application secret>

Make sure to replace the placeholders with their actual values:

• <xmla endpoint> (same as when connecting manually: powerbi://api.powerbi.com/v1.0/<organization name>/<workspace name>)
• <application id> (from the service principal)
• <tenant id> (from Azure Active Directory)
• <application secret> (from the service principal)

Remember – if you want to make a change to the model, you only need to hit Ctrl+S to save the change directly back to the Power BI Premium capacity (this is probably not the best thing to do on a production dataset…). Alternatively, you can deploy the model metadata as a new dataset by going to the Model > Deploy menu (or hit F6), and then use the same connection string for the server name.

You can also perform the deployment using Tabular Editor's command-line interface, which is what you typically need for CI/CD integration. For example, to deploy a local Model.bim file as a dataset named AdventureWorks use the following command. The -O switch allows you to overwrite an existing dataset with the same name:

start /wait TabularEditor.exe Model.bim -D "Provider=MSOLAP;Data Source=<xmla endpoint>;User ID=app:<application id>@<tenant id>;Password=<application secret>" "AdventureWorks" -O

That’s it! Feel free to post questions below or on GitHub.

For further reading

• The XMLA endpoint for Power BI: how it works and how to use it (Tabular Editor). Background on what the XMLA endpoint is and why a workspace needs read/write access, useful context before configuring service-principal access.
• Tabular Editor CLI documentation (Tabular Editor). Covers the current cross-platform CLI used for model deployment and automation.
• Automate Premium workspace and semantic model tasks with service principals (Microsoft Learn). Microsoft's reference for automating Premium workspaces and semantic models with service principals, with the current syntax and requirements.
• You're Deploying it Wrong! – AS Edition (Part 1) (Tabular Editor). Walks through the deployment approaches an XMLA-enabled service principal makes possible, from manual saves to scripted CI/CD pipelines.

In conclusion

And that’s the whole setup. It’s a fair few steps across the Power BI and Azure portals, but you only have to do it once. After that, you’ve got a service principal that can refresh, deploy, and edit your datasets without anyone signing in, which is exactly what you need for CI/CD or any other unattended job. Enable XMLA read/write, register the service principal, give it access to the workspace, and you’re ready to point Tabular Editor or a pipeline at your Premium capacity.