Reading Time: 6 minutes

The Power BI REST API opens a wealth of capabilities in consuming metadata about your content as well as batch processing of security changes, content deployment and backups of deployed reports. Although I talk specifically about Power BI, these methods and capabilities apply to many REST API services (Azure AD, the Graph API, etc).

Authentication

I Utilise PowerShell to invoke the REST API for its ease of use through functions within modules distributed via the PowerShell Gallery. There is, however, a significant draw back which is a consistent issue when working with any Azure service through its REST API: Authentication. The need for a pop-up prompt to authenticate against Power BI or any such service means automation is almost impossible.

…almost.

There are methods to circumvent this requirement, each with its own drawbacks.

The quick and dirty way is to pass credentials through an authentication API call to return an access token. This method requires the user’s password which would need to be either stored in plain text or encrypted and decrypted from a file, thus tying the process to a specific machine or server. I have previously utilised Window’s credential manager to manage this, eliminating the file dependency but the server/machine dependency remains.

There is also new functionality specific to Power BI, announced by Microsoft (Feb 2019) that allows you to use a Service Principal that, once set up, can authenticate to Power BI without a prompt. It requires a Power BI Admin to implement, AD groups set up and only works with the new v2 workspaces so each method has it’s failings.

How can Azure help?

We can utilise an Azure Automation account to run the PowerShell code and store the needed credentials and any other required parameters in the Azure Key vault. There is still a user dependency and password reset cycles to consider but it’s a solution that requires no further configuration changes within Power BI and can also be applied to any of the API’s I mention above. Most importantly, it removes the single point of failure and provides added security for the required credential.

Power BI Prep

We need to start by creating an app to facilitate authentication from the Power BI Developer Portal [LINK]. This process differs from other API endpoints where we create the app within App Registrations in the Azure Portal, but they all end up in that same part of the Azure Portal anyway.

1. Navigate to https://dev.powerbi.com/apps.
2. You may be prompted to login again. Once logged in you can create a new app.
3. Name your app so that it’s easy to identify in the Azure Portal. I’ve named this one “Power BI API Governance” to cover a wide range of uses under the API.
4. Make sure the app is set to a Server-side web application. This app type will generate both an applicationId and a clientSecret which we need for the unattended authentication.
5. Set the Redirect URL and Home Page URL to either the parent domain of the tenant or something relevant. This isn’t needed the way we’ll use the app. We aren’t redirecting anywhere after authentication but it’s good to set this to something to complete the configuration properly.
6. You then get to set the access the app has to the API. Here you can restrict it to just “Read” permissions or Read/Write across each component within Power BI.

1. Once you’ve configured all of the settings on this page, click Register to create the app.
2. At this point, you’ll be provided with your applicationId and a clientSecret. Make a note of both before navigating away from this page. You can retrieve the applicationId from the App Registrations page in Azure but there is no way to retrieve the clientSecret again once you navigate away.

Automation

This is where the steps become more generic and the process can be employed to other REST APIs within Azure.

We now create the Azure Automation account where we’ll setup the PowerShell runbook and store the Application ID and Secret in the Azure key vault along with the credentials we want to use.

1. From the Azure Automation Account blade, add a new Automation Account
2. Once you have added the Automation Account, Create a runbook with type PowerShell.

We now need to add our credentials and set the other required variables. All of these are set up inside the Automation Account. These are just name/value pairs and should look like the screenshot before when created. I have encrypted the client_secret so that is obfuscated in the UI.

With our environment created, credentials and variables setup we can piece it all together. With Azure Automation Accounts we can source our PowerShell script from a github repo which makes CI/CD a lot simpler.

From the Source Control option (under Account Settings), click Add and setup your source control repo. I’m using Github and I’ve saved off the PowerShell script to a subfolder called runbooks. Adding the source control location will automatically import all PowerShell scripts inside that folder.

Here is the authentication excerpt from that script, though the full script actually captures dataset refresh metadata and pushes it into another Power BI dataset.

#-------------------------------------------------------
# Author: Craig Porteous
# Presentation: Power BI and PowerShell: A match made in Heaven
# Script: Azure Automation runbook script to update push dataset
#-------------------------------------------------------
# Prerequisites
# Module: PowerBI-Metadata
# The following variables need to be set up in Azure Automation account:
# client_id - Set from the Power BI App created in the Developer portal
# client_secret - Set from the Power BI App created in the Developer portal
# workspace_id - Target & source workspace for datasets
# dataset_id - Source dataset we're pulling history from
# pushDataset_id - Target push dataset we're putting data into.
#-------------------------------------------------------
# set variables and retrieve automation credentials

# Credentials
$myCredential = Get-AutomationPSCredential -Name 'Power BI Login'
$userName = $myCredential.UserName
$password = $myCredential.GetNetworkCredential().Password

#Variables
$client_Id = Get-AutomationVariable -Name 'client_id'
$client_Secret = Get-AutomationVariable -Name 'client_secret'
$workspace_id = Get-AutomationVariable -Name 'workspace_id'
$dataset_id = Get-AutomationVariable -Name 'dataset_id'
$pushDataset_id = Get-AutomationVariable -Name 'pushDataset_id'

# Build authentication components
$tenantID = Get-AzureTenantID -Email $userName
$authority = "https://login.windows.net/$tenantID/oauth2/token"
$resourceAppID = "https://analysis.windows.net/powerbi/api"

#-------------------------------------------------------
# Get Authentication token

    $authBody = @{
        'resource'=$resourceAppID
        'client_id'=$client_Id
        'grant_type'="password"
        'username'=$userName
        'password'= $password #! THIS IS IN PLAIN TEXT!
        'scope'="openid"
        'client_secret'=$client_Secret
    }

    #! Clear password variable immediately after use
    $password = $null

#-------------------------------------------------------
#Authentiate to Power BI

    $auth = Invoke-RestMethod -Uri $authority -Body $authBody -Method POST -Verbose

    #! Clear auth array immediately after use
    $authBody = $null

    $token = $auth.access_token

    # Build the API Header with the auth token
    $authHeader = @{
        'Content-Type'='application/json'
        'Authorization'='Bearer ' + $token
    }

We’re now ready to create a schedule to execute our runbook. We also have options to trigger the runbook from a webhook too.

We now have a fully automated process, connecting to the Power BI API, authenticating without a prompt and performing a task against that API. By using the Azure key vault I’m able to secure my credentials easily and reliably without the risk of using on-premises servers and encrypted files.

For added security and reliability I would implement a service account with restricted permissions and no password expiry cycle.

Drawbacks

The main drawbacks to this approach of achieving unattended authentication with Power BI and PowerShell is the storage of user credentials. Although we use Azure key vault under the hood, it’s still not ideal. If we’re working in an organisation with blanked 2-factor authentication setup, we’re blocked at the first hurdle unless we can implement an exception for a service account. Despite these security concerns, the solution is still a much more robust option than using on-premises servers, encrypted text files or the Windows credential manager.

Conclusion

Speaking specifically about Power BI, there is no easy, one-click solution. Regardless of the approach, there is great opportunity to implement proper Power BI governance using the REST API and automating the collection of that metadata is a vital step which I hope can be simplified further in future updates.