Martin's Blog

This blog post is part of my “Automating Power BI deployments” series, and you can find a list of the other posts here.

Prerequisites

Before we can start interacting with the Power BI service, we’ll need to make sure that we have the required permissions on our local machine and install the Power BI Management module for PowerShell.

We looked at that in the previous blog post about PowerShell basics, but I’ll show it again for easy reference:

#This will show you all the existing execution policies
Get-ExecutionPolicy -List

#If you want to set it for the machine
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope LocalMachine
 
#If you want to set it for the current process
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process 

#If you want to set it for current user only
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser 

#Install the module in order to use the cmdlets
Install-Module -Name MicrosoftPowerBIMgmt -AllowClobber

#Import the module into the current session
Import-Module MicrosoftPowerBIMgmt

Interactive Connections

When running an interactive session, you can just call the cmdlet below without specifying any parameters.

Connect-PowerBIServiceAccount

Doing this will bring up a prompt for credentials, using MFA if it is required by your organization. If the connection is successful, the output window will display the GUID of the tenant and username you’ve used in the connection. Note that I am using VS Code to run these cmdlets…the output may look a little different to what you are seeing.

Automated Connections

If you want to fully automate your deployments, you will need to connect to the service without prompts or user interaction. The Connect-PowerBIServiceAccount cmdlet lets you pass a Credential parameter (username and password or service principal), but it requires a secure string and we’ll need to do a bit of work in order to get there.

In the code snippet below we create two variables (for the username and password), and convert the password to a secure string before we can generate a credential object to use in the cmdlet.

#Variables 
$PbiUser = "me@me.com"
$PbiPassword = "MyPassword"

#Create secure string for password 
$PbiSecurePassword = ConvertTo-SecureString $PbiPassword -Force -AsPlainText
$PbiCredential = New-Object Management.Automation.PSCredential($PbiUser, $PbiSecurePassword)

#Connect to the Power BI service
Connect-PowerBIServiceAccount -Credential $PbiCredential

This method does not work if multi-factor authentication (MFA) is required for the account you’re using, and you also cannot use an App password as you would with other applications like Outlook. You will either have to create a service account (with Power BI administrator permissions) that doesn’t require MFA, or create a service principal in Azure AD for these types of operations.

A service principal is really an identity that doesn’t require any interaction, and the *best option* (see note below) if you want to fully automate your scripts. The naming convention in the Azure portal is a bit confusing, because you have to go to App registrations in Azure AD to add one. Once you’ve added the service principal, assigned the Power BI administrator role to it and created a secret (password), you can use the following PowerShell code to connect to the service:

#Variables 
$AppId = "MyAppId"
$TenantId = "MyServicePrincipalTenantId"
$ClientSecret = "MyClientSecret" 

#Create secure string & credential for application id and client secret
$PbiSecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText
$PbiCredential = New-Object Management.Automation.PSCredential($AppId, $PbiSecurePassword)

#Connect to the Power BI service
Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $PbiCredential

As you can see from the above, the only difference here is that you use the App Id as username, the Client Secret as password and change the call to the cmdlet slightly to let it know that you’re using a service principal. You also have to provide the Tenant Id which you can find in the properties of the service principal (or App registration).

Note: At this point in time, you can only use a service principal with Power BI Premium workspaces and Power BI Embedded <sad trombone/>

Now that we’re connected to the service, we can start doing some cool things. In the next blog post we’ll create a new workspace to which we can deploy our reports…

Want to see the PowerShell scripts for this series? Get it from my GitHub repo here.