Using a Service Principal to Refresh a Dataset via a Data Gateway

Introduction

This week we’ve seen ChatGPT go viral, and I’m sure we’ve all been having fun asking it to write code, a poem, or to give some questionable life advice. Copilot’s been feeling a little left out it seems. Since this blog is written in Markdown in VS Code, it offered to help out writing this intro.

Using a Service Principal to refresh a dataset via a Data Gateway has a number of benefits…It’s a great way to automate the refresh of a dataset, and it’s a great way to ensure that the refresh is done with the correct permissions.

User accounts expire, become locked, are deleted, are forgotten about, are shared with too many people, are shared with too few people, are shared with the wrong people, are shared with the right people but the wrong people have access to the password, are shared with the right people but the wrong people have access to the password and the right people have access to the password but the password is wrong.

I thought I’d share the process as it’s a bit of a faff to get working.

Well it’s got my style sussed.

The Plan

The steps required to provisioning a datasource on a gateway to use a Service Principal are much like those for a standard user account:

• Add the account to the Gateway Datasource.
• Take ownership of the Dataset in the Service
• Fix up credentials and bind the Dataset to the Data Gateway Datasource as the new owner

But as ever, the details, the details.

The Power BI service UI doesn’t let you do most of these tasks when the account you’re trying to add is a Service Principal. However Powershell does. I’m not too much of an awesome POSH scripter, so what follows is Good Enough To Get The Job Done™️

Prerequisites

First, I’d suggest you watch this Guy In A Cube video. Adam explains the process really well, and it’s a great starting point.

Then, you’ll need:

• A Service Principal aka App Registration

  • https://learn.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal

• The Allow service principals to use Power BI APIs Power BI Tenant setting enabled. You will need to add the Service Principal to the group set here to grant permissions to take over the Dataset later. You can remove the account from the group after we’re done.

  • https://learn.microsoft.com/en-us/power-bi/enterprise/read-only-apis-service-principal-authentication

• DataGateway, MicrosoftPowerBIMgmt, and Az Powershell modules

  • https://learn.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps
  • https://learn.microsoft.com/en-us/powershell/gateway/overview?view=datagateway-ps
  • https://learn.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-9.2.0

You’ll also need to configure your backend database to allow access by the Service Principal. For example in Azure SQL DB, this would be

CREATE USER [PowerBI Gateway SPN] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [PowerBI Gateway SPN];

(replace the ROLE with something more suitable)

See

• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#create-contained-users-mapped-to-azure-ad-identities and
• https://learn.microsoft.com/en-gb/azure/data-factory/connector-azure-sql-data-warehouse?tabs=synapse-analytics#service-principal-authentication

for more details on adding Service Principals to Azure SQL DB and Azure Synapse Analytics.

GUIDs, GUIDs Everywhere

Power BI loves a GUID (it’s gooo-id, not gue-id btw), and we’ll need more than a few before we’re done. First up, the relevant GUIDs from the Service Principal: the Tenant Id, Application Id, and the Object Id from the Enterprise App, not the App Registration We’ll also need the client secret to authenticate the Service Principal.

Don’t worry, I literally mashed the keyboard to make up that secret and the GUIDs are all faked with [guid]::NewGuid()

There’s a ton of ways to store the client secret. This example doesn’t, rather it prompts for those 40 random chars:

 $spnCred = Get-Credential $AppId 

And remember kids, you most definitely shouldn’t store secrets in clear text in a posh script file. Use keyvault or something. Don’t do this instead do this

Next, we’ll need the GUIDs for the Group (Workspace) and Dataset. These can be found in the URL when you’re looking at the dataset in the Power BI service - the first is the group, the second is the dataset.

And finally the Gateway Cluster and Cluster Datasource GUIDs. You used to be able to find these in the URL, but since the new gateway UI, you’ll need to use the Power BI cmdlets to get them.

I’m not a fan of putting GUIDs in code or config; I prefer my config to be a little easier to remember. Since we’ll be using the Power BI cmdlets, we can use the names of the Service Principal, Workspace, Dataset and Datasource to lookup the GUIDs we need. And besides, we’d need to use Powershell to find the Gateway GUIDs anyway, so we might as well use it to lookup the others too.

$spnName   = "PowerBI Gateway SPN"
$groupName = "SPN and Gateway Workspace"
$dsName    = "AdventureWorksLT"
$dsnName   = "demo-spn-gateway-dsn"

That’s better. Now, we can lookup those GUIDs, starting with the App Registration details.

$tenantId, $spn =  ( Start-Job -ScriptBlock {
    $tenantId = (Connect-AzAccount).TenantId
    $spn = Get-AzADServicePrincipal -DisplayName $spnName
    Disconnect-AzAccount
    return $tenantId, $spn
} | Wait-Job | Receive-Job )

$spnAppId = $spn.AppId
$spnObjId = $spn.Id

I’ve wrapped this is in a Job as I found the various cmdlet’s authentication code didn’t play nice with each other. Logging in to Azure, Power BI and the Datagateway Service often caused random hangs and crashes of Powershell. This way, each is in a separate process.

We’ll come back to looking up the other GUIDs later.

Adding the Service Principal to the Data Gateway

First, we’ll add the Service Principal to the Data Gateway. This has to run as a Gateway Admin User (not a Service Account - these aren’t supported - don’t be fooled by the ServiceAccount bit in Connect-DataGatewayServiceAccount). Again, running in a separate process because reasons.

This code first connects then retrieves my Gateway Cluster. If you have more than one, you’d need to “left as an exercise to the reader” to find the correct cluster. The code then looks up the relevant datasource (again, I only have one).

After this, the Service Principal is added as a Gateway admin and as a Reader on the DSN. Why as an admin? Still working that one out, as I couldn’t get the script to work without.

$gw, $ds = ( Start-Job -ScriptBlock {

    Connect-DataGatewayServiceAccount | Out-Null

    # Lookup Gateway and Datasource GUID
    $gw = Get-DataGatewayCluster
    $ds = Get-DataGatewayClusterDatasource -GatewayClusterId $gw.Id | where { $_.DatasourceName -eq $Using:dsnName }

    # Add SPN as Gateway Admin
    Add-DataGatewayClusterUser -GatewayClusterId $gw.Id -PrincipalObjectId $Using:spnObjId -Role Admin 
    # Add SPN as Datasource User
    Add-DataGatewayClusterDatasourceUser -GatewayClusterId $gw.Id -GatewayClusterDatasourceId $ds.Id -DatasourceUserAccessRight Read -Identifier $Using:spnObjId

    #Disconnect
    Disconnect-DataGatewayServiceAccount | Out-Null
    return  $gw, $ds
} | Wait-Job | Receive-Job )

Updating the Workspace and Dataset

Let’s head back to the Power BI Service. Remember those nasty Group and Dataset GUIDs? Me neither. Let’s look them up. Again, you’ll need to authenticate as an actual Power BI user account, not a Service Principal.

Login-PowerBI

$group = Get-PowerBIWorkspace -Scope Organization -Filter "name eq '$groupName'"
$dataset= Get-PowerBIDataset -Scope Organization -GroupId $group.Id -Filter "name eq '$dsName'"

$groupId = $group.Id
$datasetId = $dataset.Id

Permissions

The Service Principal is subject to Workspace security like any other account. In order to take ownership of the dataset, the account will need at least Member role in the workspace. This is the API that requires an actual admin login, not a Service Principal it seems.

Here, we’ll add the Service Principle to the Member role using Admin - Groups AddUserAsAdmin REST API.

$request = @{
    "identifier"=$spnEntAppOid;
    "groupUserAccessRight"="Member";
    "principalType"="App";
   }
$url = "admin/groups/$groupId/users"
#The non-admin URL is $url = "groups/$groupId/users" 
#An account with workspace admin should be able to use this
Invoke-PowerBIRestMethod -Method POST -Url $url -Body (ConvertTo-Json $request) -ContentType "application/json"

Logout-PowerBI

And Finally

The final stage is to take ownership of the Dataset and re-bind to the gateway as the Service Principal. This requires us to connect as the Service Principal who is taking ownership (which is why we need to be in the Allow Service Principals to Access REST APIs setting)

After this, we can update the Dataset and bind to the Gateway.

Connect-PowerBIServiceAccount -Tenant $tenantId -ServicePrincipal -Credential $spnCred

#Takeover the dataset
$url = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/Default.TakeOver"
Invoke-PowerBIRestMethod -Url $url –Method POST –Verbose


# Rebind the dataset to the gateway

$url = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/Default.BindToGateway"
$request = @{
    "gatewayObjectId"=$gw.Id;
    "datasourceObjectIds"= @($ds.Id)
}
Invoke-PowerBIRestMethod -Url $url –Method POST -Body (ConvertTo-Json $request) –Verbose


Disconnect-PowerBIServiceAccount

And Finally Finally

Last of all, refresh the dataset. If everything has worked (and like Copilot said, it’s a bit of a faff), you should see the dataset refresh using the Service Principal credentials.

Acknowledgements

I…couldn’t have done this without the help of the following people. Thanks to Copilot for the original script and for the inspiration to do this.

Yeah, that’s enough AI for one day.