How to perform Power BI On-prem Data Gateway management using Service Principal?

Recently I was working on end-to-end implementation of Power BI DevOps that includes Gateway, Workspace and Dataset configuration programmatically using Powershell and Power BI REST API. One of the first steps, I stumbled upon was Gateway configuration and how its linked to data sources and dataset settings.

Problems

• How to map or bind data source from Data Gateway using Service Principal in dataset settings? This requires Service Principal to be added as a User in the Gateway data source.
• How to add Service Principal as a User in the Gateway data source programmatically using Power BI REST API and Service Principal? This requires Service Principal to be added as a Gateway Administrator.
• How to add Service Principal as a Gateway Administrator programmatically? Manually this cannot be done but programmatically this is possible. This is the root cause of the problem so we will start here.

Concept

Adding Service Principal as a Gateway Administrator

Currently, we can’t add Service Principal as a Gateway Administrator manually. If the Gateway is installed using Service Principal with Powershell script and Power BI REST API then the Service Principal becomes the Gateway Administrator. In addition, you can also add users to Gateway administrator using Powershell script. Below is the sample script that can be executed only in Powershell 7 and above.

#Enter Gateway details
$AppID = #Enter Service Principal Application ID.
$Cert = #Enter Service Principal Certificate Thumbprint.
$Tenant = #Enter Service Principal Tenant.

#Install DataGateway modules
Install-Module -Name DataGateway

#Connect using Service Principal credentials.
Connect-DataGatewayServiceAccount -ApplicationId $AppID -CertificateThumbprint $Cert -Tenant $Tenant

#Installs the latest Data Gateway. Accept all conditions.
Install-DataGateway -AcceptConditions

#Create RecoveryKey (You can use any string). It's like a password that will be required if you are configuring Gateway.
Add-DataGatewayCluster -Name "SK_Gateway_ServicePrincipal" -OverwriteExistingGateway -RecoveryKey (Read-Host "Enter RecoveryKey: " -AsSecureString)

#Add additional user as Gateway admin (Optional). Execute after you have installed the Gateway. 
$GCID = #Enter Gateway Cluster ID.
$PObjID = #Enter Object ID of the user. Make sure you find this under Overview > Managed Application in local directory
Add-DataGatewayClusterUser -GatewayClusterId $GCID -PrincipalObjectId $PObjID -Role "Admin" -AllowedDataSourceType $null

Once you have successfully installed Power BI On-prem Data Gateway and added user to it, you should be able to view something similar like below image under Settings > Manage gateways. The Service principal will show in red color. Overall, this is how you can have Service Principal as Power BI Gateway administrator.

Adding Data Source to Gateway using Powershell

In order to add data source to Gateway using Powershell, you will be using Service Principal to authenticate and run the script. You can use Service account for Data Source windows credentials type but those credentials need to be encrypted using RSA-OAEP asynchronous algorithm before sending it Power BI REST API to add the data source. We will be using .NET SDK helper class to perform the encryption. Below is the sample Powershell script.

[cmdletBinding()]
param (
# Service Principal details
 [Parameter(Mandatory=$true)]
 [string]$thmbprnt,
 [Parameter(Mandatory=$true)]
 [string]$AppId,
 [Parameter(Mandatory=$true)]
 [string]$tenant,
#Gateway details
 [Parameter(Mandatory=$true)]
 [string]$gatewayObjectID,
#Datasource details
 [Parameter(Mandatory=$true)]
 [string]$dsType,
 [Parameter(Mandatory=$true)]
 [string]$dsName,
 [Parameter(Mandatory=$true)]
 [string]$dsConnectionDetails
 [Parameter(Mandatory=$true)]
 [string]$username,
 [Parameter(Mandatory=$true)]
 [string]$password,
)

#Initiate connection using Service Principal
function init-connection() {
 try {
#Using Service Principal with certificate type authentication
     Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint $thmbprnt -ApplicationId $AppId -Tenant $tenant
 }
 catch {
     Write-Host "Failed to connect to Power BI"
 }
}

function end-connection() {
 try {

     Disconnect-PowerBIServiceAccount
 }
 catch {

     Write-Host "Failed to disconnect from Power BI"
 }
}

#Check pre-requisites. Install if not exist.
function check-prerequisites() {
try {
# Install the Power BI package if it's not already installed.
if (!(Test-Path ".\Microsoft.PowerBI.Api.3.18.1" -PathType Container)) {
    Install-Package -Name Microsoft.PowerBi.Api -ProviderName NuGet -Scope CurrentUser -RequiredVersion 3.18.1 -SkipDependencies -Destination . -Force
}

# Install the Client Runtime package, a dependency of the Power BI package.
if (!(Test-Path ".\Microsoft.Rest.ClientRuntime.2.3.22" -PathType Container)) {
    Install-Package -Name Microsoft.Rest.ClientRuntime -ProviderName NuGet -Scope CurrentUser -RequiredVersion 2.3.22 -SkipDependencies -Destination . -Force
}

# Install the Newtonsoft package, one more dependency of the Power BI package.
if (!(Test-Path ".\Newtonsoft.Json.11.0.2" -PathType Container)) {
    Install-Package -Name Newtonsoft.Json -ProviderName NuGet -Scope CurrentUser -RequiredVersion 11.0.2 -SkipDependencies -Destination . -Force
}

# Load the Client Runtime assembly 
$crpath = Resolve-Path ".\Microsoft.Rest.ClientRuntime.2.3.22\lib\netstandard2.0\Microsoft.Rest.ClientRuntime.dll"
[System.Reflection.Assembly]::LoadFrom($crpath)

# Load the Newtonsoft assembly.
$nwpath = Resolve-Path ".\Newtonsoft.Json.11.0.2\lib\netstandard2.0\Newtonsoft.Json.dll"
[System.Reflection.Assembly]::LoadFrom($nwpath)

# Conditionally choose the Power BI assembly to use, depending on whether you're using Windows PowerShell (version <= 5) or PowerShell Core (version >= 6)
if ($PSVersionTable.PSVersion.Major -le 5) {
    $pbipath = Resolve-Path ".\Microsoft.PowerBI.Api.3.18.1\lib\net48\Microsoft.PowerBI.Api.dll"
}
else {
    $pbipath = Resolve-Path ".\Microsoft.PowerBI.Api.3.18.1\lib\netstandard2.0\Microsoft.PowerBI.Api.dll"
}

# Load the Power BI assembly into the session
[System.Reflection.Assembly]::LoadFrom($pbipath)
}
catch {
       Write-Host "Failed to install/ check pre-requisites."
}

function add-datasource($gatewayObjectID, $username, $password) {
try {
     Write-Host "Get PublicKey for Gateway to extract exponent and modulus."
     $publicKey = get-gateway -gatewayObjectID $gatewayObjectID
     $Exponent = $publicKey.exponent
     $Modules = $publicKey.modulus

     $gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($Exponent, $Modulus)
     $windowsCreds = [Microsoft.PowerBI.Api.Models.Credentials.WindowsCredentials]::new($username, $password)
     $credEncrypt = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)

     $credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
       $windowsCreds, 
       [Microsoft.PowerBI.Api.Models.PrivacyLevel]::Private, 
       [Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted, 
       $credEncrypt)

     $credentials = $credentialDetails.Credentials
     
     Write-Host "Create Body Details for Power BI REST API"
     $BodyDetails = '{
                     "dataSourceType": "' + $dsType + '",
                     "connectionDetails": "' + $dsConnectionDetails + '",
                     "datasourceName": "' + $dsName + '",
                     "credentialDetails": {
                                            "credentialType": "Windows",
                                            "credentials": "' + $credentials + '",
                                            "encryptedConnection": "Encrypted",
                                            "encryptionAlgorithm": "RSA-OAEP",
                                            "privacyLevel": "Private"
                                          }
     Write-Host "Power BI REST API call"
     $url_addds = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayObjectID/datasources"

     Invoke-PowerBIRestMethod -Url $url_addds -Method Post -Body $BodyDetails -ContentType application/json
     
     }
catch {
        Write-Host "Failed to add data source."
      }
}

try {

     check-prerequisites
     init-connection
     add-datasource -gatewayObjectID $gatewayObjectID -username $username -password $password
     Write-Host "Data Source added to Gateway"
}
catch {
     Write-Host "Error adding data source to the gateway"
}
finally {
     end-connection
}

If the above script runs successfully then you should be able to view the data source added to the Gateway with Service principal as User of that data source. As the Service Principal is in User of data source, you should be able to bind/map data source using Power BI REST API and Service Principal authentication.

In order to add multiple data source of different types, you can use JSON file that can be read by Powershell to plug in the values. In addition, if you using CI/ CD pipeline (like Bamboo) for Gateway configuration then you can pass username and password as variables with Bamboo encrypted text in YAML files. This means no one can read the sensitive information except Bamboo pipeline. There are multiple ways to store sensitive information that can be read by Powershell script. You can use those methods as well if you are not using CI/ CD pipeline.

Adding User to Gateway Data Source

Once the data source is added to Gateway. You will want to add “Users” to that data source to make it accessible to other users who will be manually configuring Power BI datasets like binding/ mapping data source, setting refresh schedule, etc. You can use Powershell script and invoke Power BI REST API – Add Datasource User to add users to the data source.

Few points to note regarding JSON request body to be used,

• If you want to add individual then use “emailAddress”: “john@contoso.com”
• If you want to add group then use “emailAddress”: “ADGroup@contoso.com”
• If you want to add app/ service principal then use “identifier”: “3d9b93c6-7b6d-4801-a491-1738910904fd”

Conclusion

This solution provides a way to manage Gateway configuration using Service Principal. This will be important to perform Prod tasks without any need to provide Gateway administrator level access to Production Gateway or need to admins to perform Gateway tasks. In addition, Service principal will be required to be added as User of the Data Source to automate dataset binding/ mapping using that Data source. This will help in end-to-end implementation of CI/ CD pipeline for Power BI.