Contents
data/authors/Paul Logan.json

Removing personal Windows accounts from Power BI configurations

Software 674 words 4 minutes
Contents

When you push a semantic model to Power BI, the account used for publishing automatically becomes the owner of the semantic model. It doesn’t matter whether a service principal or a personal account deploys the semantic model: The one who publishes it, owns it – and this is not always a desired outcome. You could run into problems when the semantic model owner left the organization.

To avoid such issues, you can switch ownership of the semantic model to a dedicated service principal.

A service principal is an authentication method that can be used to let a Microsoft Entra application access Power BI service content and APIs.

https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal?tabs=azure-portal#get-started-with-a-service-principal

Screenshot of the required admin roles being ticked
Admin roles required
Screenshot of the required admin roles being ticked
Admin roles required
  • New EntraID App Service Registration This automatically creatres the service principal.
  • New EntraID Security Group
  • Add the app registered in step 1, the new service principal, as a member of the above security group. No additional API Permissions are required (discovered by running through this process umpteen times.)
  • Install the necessary Power BI Cmdlets for Windows PowerShell
# [Run PowerShell as Admin]
Install-Module -Name MicrosoftPowerBIMgmt

Log-in as the Service Principal

Connect-PowerBIServiceAccount -Tenant <your_tenant_id> -ServicePrincipal -Credential (Get-Credential)

In the App Registration within the Azure portal, fill the popup from the above command with:

  • Application (client) ID from the Overview screen
  • Secret Value from the Certificates & Secrets screen

Take ownership of the dataset with the logged in credential of the Service Principle

🤬Executing the URL provided in the above link failed: POST https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver

🤬 If the URL call fails, use the following command to get more details on the error:

> Resolve-PowerBIError -Last

Message        : Response status code does not indicate success: 404 (Not Found).
StackTrace     :    at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
Exception      : System.Net.Http.HttpRequestException
InvocationInfo : {Invoke-PowerBIRestMethod}
Line           : Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/groups/92fce635-ecd7-4f3f-b3c2-d3c27911e75
                 c/datasets/9e112f8e-ad47-4995-927b-a22c5a16db2b/Default.TakeOver" -Method POST
Position       : At line:1 char:1
                 + Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/groups/92 ...
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method POST | ConvertFrom-Json
# OR
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method POST

Above Failed

  • App Registration => added api permissions listed here
  • Only added the Service Principal/App Registration to the Security grouy now. Had to go into EntraID => Enterprise Applications => Add user/group

Failing due to the URL being used.

Invoke-PowerBIRestMethod -Url “groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver” -Method POST | ConvertFrom-Json

Cannot manage users on a Gateway cluster name = (Personal Mode)

  • Manage Connections and Gateways => Connections => New =>

Add the Service Principal to the Connection in the Manage Connections and Gateways => Manage Users => Share with Service Principal

Update Hybrid connection: https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-november-2024-release/

New Connection section on PowerBI: TenantID - easy Service Principal ID - Application (client) ID from the Overview screen for the App Registration pagon Azure portal

The Service Principal authentication method is only applicable to Azure SQL or SQL Server 2022 (16.x) and later. The SQL command for creating a user linked to the Service Principal throws an error on earlier versions of SQL Server

CREATE USER [OnPremDataGateway@contoso.com] FROM EXTERNAL PROVIDER;

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'PROVIDER'.

There’s still work to be done on the supported versions:

CREATE USER [OnPremDataGateway@contoso.com] FROM EXTERNAL PROVIDER;

Msg 37525, Level 16, State 1, Line 1
Commond 'CREATE USER FROM EXTERNAL PROVIDER' is not supported as Azure Active Directory is not configured for this instance.

The full list of requirements for an on-prem SQL Server 2022 or later are documented here

So, let’s go for a new(ish) type of user created in SQL Server - a contained user.

A contained user in SQL Server is a database-level user that does not rely on a server-level login. This makes the database more portable because the user credentials are stored within the database itself, rather than being tied to the SQL Server instance. Contained users are commonly used in contained databases, which are designed to minimize dependencies on the SQL Server instance.

Blog Tip
A tip
Blog Warning
A warning
Power BISQL Server