Articles

Embedding Power BI content with a Service Principal

Until now, embedding Power BI reports or dashboards into a web application or automating processes with the Power BI API required a master account. A master account is an actual Power BI account with a username and password that the embedding app uses to connect to the Power BI API. The master account that is granted a Power BI Pro license as well as access to the workspace.

That is wasteful, as it consumes a Power BI Pro license for each embedding application. It also complicate governance in enterprise context, as a specific process must be put in place to manage the lifecycle of those special type of ‘user’ accounts.

Enabling a service principal account can be tricky. Instructions are at https://docs.microsoft.com/en-us/power-bi/developer/embed-service-principal#get-started-with-a-service-principal. Here is an end-to-end PowerShell script that automates the process end-to-end, except for one step (STEP 4) that must be taken manually in the Power BI Portal Admin Settings.

Create a workspace v2 (‘New Workspace’) following the instructions at https://docs.microsoft.com/en-us/power-bi/service-create-the-new-workspaces. Service principals will not work with a traditional workspace.

Navigate to the workspace and take note of the Workspace ID in the URL. In this case the workspace ID is 9b7d30cb-8bd7-47fc-9b86-eba08f986e62.

Get your Tenant ID. This is shown as Directory ID in the Azure portal at https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Properties

Tenant ID

Open PowerShell ISE and paste the following script. Update your Tenant ID and Workspace ID. Select an appDisplayName and groupDisplayName or use the proposed values. Run up to step 3, then perform step 4 manually. Run step 5 (there is no need to modify the clientId and URI in step 5).

$ErrorActionPreference = "Stop"

# AAD tenant ID
$TenantId = '67e511b4-d156-43ae-bd61-6ab0a35146g1'

# Name of the AAD Service Principal that will be created
$appDisplayName = "PowerBITestAccount"

# Name of the AAD Security Group that will be created
$groupDisplayName = "PowerBIServicePrincipals"

# ID of the Power BI Workspace. NB: MUST BE A  WORKSPACE V2.
# See https://docs.microsoft.com/en-us/power-bi/service-create-the-new-workspaces
$workspaceId = '9b7d30cb-8bd7-47fc-9b86-eba08f986e62'


############## STEP 1 : Get AAD credential interactively from user

$cred = Get-Credential -Message "Enter a credential for an AAD Tenant Admin"


############## STEP 2 : Create service principal for Power BI

# Connect to AAD 
# Clone credential - workaround for https://github.com/Azure/azure-docs-powershell-azuread/issues/169
Connect-AzureAD -Credential (New-Object System.Management.Automation.PSCredential($cred.UserName, $cred.Password))

# Create a new AAD web application. URLs are not used, so their values don't matter
$app = New-AzureADApplication -DisplayName $appDisplayName -Homepage "http://example" -ReplyUrls "http://example"

# Creates a service principal
$sp = New-AzureADServicePrincipal -AppId $app.AppId

# Get the service principal key.
$key = New-AzureADServicePrincipalPasswordCredential -ObjectId $sp.ObjectId


############## STEP 3 : Create AAD security group with Power BI service principal

# Create an AAD security group
$group = New-AzureADGroup -DisplayName $groupDisplayName -SecurityEnabled $true -MailEnabled $false -MailNickName notSet

# Add the service principal to the group
Add-AzureADGroupMember -ObjectId $($group.ObjectId) -RefObjectId $($sp.ObjectId)


############## STEP 4 : MANUAL STEP - Enable security group section in Power BI Developer settings

# MANUAL STEP:

#As a Power BI admin, you need to enable service principal group in the Developer settings in the Power BI admin portal.
#Add the security group that you created in Azure AD to the Specific security group section in the Developer settings.
#See step 3 in doc at https://docs.microsoft.com/en-us/power-bi/developer/embed-service-principal#get-started-with-a-service-principal


############## STEP 5 : Add the service principal as an admin to the workspace

#
# Set well-known client ID for Azure PowerShell
$clientId = '1950a258-227b-4e31-a9cf-717495945fc2'

# Set Resource URI to Power BI API
$resourceURI = "https://analysis.windows.net/powerbi/api"

# Set Authority to Azure AD Tenant
$authority = 'https://login.microsoftonline.com/common/' + $TenantID

$AADcredential = [Microsoft.IdentityModel.Clients.ActiveDirectory.UserPasswordCredential]::new($cred.UserName, $cred.Password)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContextIntegratedAuthExtensions]::AcquireTokenAsync($authContext, $resourceURI, $clientId, $AADcredential).Result;

$token = [Microsoft.Open.Azure.AD.CommonLibrary.AzureSession]::AccessTokens['AccessToken'].AccessToken

$body = @{
    identifier = $sp.ObjectId
    groupUserAccessRight = 'Admin'
    principalType = 'App'
}
$json = $body | ConvertTo-Json

$url = 'https://api.powerbi.com/v1.0/myorg/groups/' + $workspaceId + '/users'
$headers = @{'Authorization' = "$($authResult.AccessTokenType) $($authResult.AccessToken)"}

Invoke-RestMethod -Uri $url -Headers $headers -Method Post -Body $json  -ContentType 'application/json'

Write-Output "Your application ID: " $sp.AppId

The script outputs your newly created Application ID. Navigate to your App Registrations at on the Azure Portal at https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps.

Find your app, click on it, click Settings, and under Keys, generate an application secret. You will need it for your application.

Testing an embedded app

Navigate to your workspace and deploy one of the Samples. You can also create your own report instead.

Navigate to the report and take note of the Report ID in the URL. In this case the report ID is 6aa839d2-ae86-40e6-a67c-0175e0505e33.

Clone the Power BI Developer Sample at https://github.com/Microsoft/PowerBI-Developer-Samples. Open the App Owns Data solution in Visual Studio. Edit the following sections of the Web.config file. Include your Application ID (for your service principal), workspace ID and report ID.

 <add key="AuthenticationType" value="ServicePrincipal" />
 <!-- Common configuration properties for both authentication types -->
 <add key="applicationId" value="8f9d9d66-75fb-4f31-82a2-eda9c3769399" />
 <add key="workspaceId" value="9b7d30cb-8bd7-47fc-9b86-eba08f986e62" />
 <!-- The id of the report to embed. If empty, will use the first report in group -->
 <add key="reportId" value="6aa839d2-ae86-40e6-a67c-0175e0505e33" />
   <ServicePrincipal>
    <add key="applicationSecret" value="aff=Rtg(bE)rkh32Nk532524G5Q;oU^1" />
    <add key="tenant" value="67e511b4-d156-43ae-bd61-6ab0a35146g1" />
  </ServicePrincipal>

Run the solution with IIS Express. Click the Embed Report button to test the functionality.

You can now adapt the sample to your application.

Of course, in a production set up, the application secret should not be embedded in your Web.config. You should use application environment variables, or better Azure Key Vault.

Alexandre Gattiker
Software Engineer at Microsoft, Data & AI, open source fan
https://cloudarchitected.com

Leave a Reply

Your email address will not be published. Required fields are marked *