How to add a Service Principal to all Power BI workspaces in one go

How to add a Service Principal to all Power BI workspaces in one go

I’ve created a PowerShell script that will add a given Service Principal to all (configured) Power BI workspaces. This can be useful or even required for all kinds of scenarios, but I recently needed such a script for my BPAA solution as it needs to talk to the XMLA endpoint of all data models in the Power BI Service and to be able to do that, the Service Principal needs to be a member of the workspaces.

Script details

Check out the AddServicePrincipalToPowerBIWorkspaces.ps1 gist on GitHub. It’s also framed below (same script).

This script will prompt for the (correct) ObjectId of the Service Principal (the one from “Enterprise applications” in Azure Active Directory), and it will prompt for the credentials of a Power BI Service Administrator.

Before you run the script, you can specify:

  • If you want the Service Principal to be added to workspaces in shared or premium capacity or both.
  • The type of role the Service Principal will get in all the workspaces.
  • If you want to force update that in case the Service Principal is already a member.

Important notes/disclaimers

Before running this script, make sure you read these notes/disclaimers first:

  • The given Service Principal will have permissions to access the data models in the workspaces it is added to. Please be incredibly careful and handle the secret of the Service Principal with care. Consider storing the details of the Service Principal, including the value of the secret in a private password manager or (Azure Key) vault.
  • Tip: consider removing the Service Principal directly after you are finished with the task that requires the Service Principal to be a member of the workspaces. I have a script to remove a Service Principal from all Power BI workspaces.
  • Note: this script only works with v2 workspaces (you can’t add a Service Principal to a v1 workspace).

Pass the sauce

This Post Has 7 Comments

  1. aspnet-scotland 2021-12-09 Reply

    Hi,

    Can your code be tweaked to add an Azure Active Directory security group containing multiple service principles for workspace access?

    Thanks.

      1. aspnet-scotland 2021-12-09 Reply

        I tried by both using “PrincipleType Group” in the cmdlet (using the security group object id instead of service principle) and using the below API code within your “ForEach-Object” loop but I receive “Bad Request 400” and “Forbidden” errors. Perhaps “Add-PowerBIWorkspaceUser” doesn’t allow security group object ids and “Invoke-PowerBIRestMethod” doesn’t have the scope required?…

        $accessToken = Get-PowerBIAccessToken

        Write-Host “Adding Security Group to: $WorkspaceName.”
        $Body = @{
        identifier= $PowerBIServicePrincipalObjectId
        groupUserAccessRight= $RoleType
        principalType= “Group”
        }

        $BodyJSON=$Body | ConvertTo-Json
        Invoke-PowerBIRestMethod -Headers $accessToken -Method Post -Url “/groups/$WorkspaceId/users” -Body $BodyJSON -ContentType:’application/json’ #Workspace ID
        Write-Host “Done.”

        Thanks.

  2. User 2022-07-09 Reply

    Hello,
    Is this possible to run this procedure without user interaction? I would like to automate the process of adding AAD group to the workspace by using Service Principal authentication (clientID and secret kept in Azure Key Vault).

    1. Dave Ruijter 2022-07-09 Reply

      Yes. Line 48 would have to be adjusted to authenticate using a Service Principal.

  3. User 2022-07-09 Reply

    Alright, thank you!

  4. Sania 2022-10-09 Reply

    Thanks you Dave for a wonderful Script. It makes my work easy!
    Need Small clarification:
    1. It is giving a warning that “only preview workspace are supported when -Scope Organization is specified” why like that?
    2. Also, is there any script which Filters to the needed workspace. For example: all workspaces with “BI” in the workspace name should be given.

Leave a Reply