Skip to content
Ed Freeman By Ed Freeman Software Engineer I
How to update credentials for an on-prem Power BI data source using PowerShell

Automating the updating of Power BI data source credentials is a common requirement in the application lifecycle management of a Power BI solution. However, on-premises data sources throw an extra spanner in the works: you must encrypt your credentials using the RSA-OAEP encryption algorithm before sending off the request to update the data source. There is currently no PowerShell module that encapsulates this logic into a set of handy cmdlets, so you must either implement the algorithm's logic yourself, or you can use the helper classes in the existing .NET SDK to do all the heavy lifting for you. This blog shows how to do the latter. Jump to the final PowerShell script.

Update Datasource REST API recap

To update a datasource, we can use the Update Gateway Datasource API. To use this endpoint, in general, we need a few things:

  • Gateway ID
    • Grab this from the Get Gateways API, or from the url when viewing the Gateway in the Power BI Service
  • Datasource ID
    • Grab this from the Get Datasources API, or from the url when viewing the datasource within the gateway in the Power BI Service
  • Credentials (Anonymous/Basic/Key/OAuth2/Windows)

But for an on-prem data source, we also need:

  • Gateway Public Key
    • Grab this object from the Get Gateway API, using the Gateway ID you've already obtained. Or grab it from the result of the Get Gateways API request if you're already using that - it's included there also.
  • Encrypted credentials
    • Keep reading this blog!

Once we have these things (and provided we have the correct level of access to the Gateway & data source), we send a PATCH request to

https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceId}

with the body:

{
  "credentialDetails": {
    "credentialType": "...",
    "credentials": "...",
    "encryptedConnection": "...",
    "encryptionAlgorithm": "...",
    "privacyLevel": "...",
    "useEndUserOAuth2Credentials": "..."
  }
}

How to RSA-OAEP encrypt the credentials using PowerShell

This is the tricky part. We need to RSA-OAEP encrypt the credentials we provide before sending the request payload to the Update Gateway Datasource API.

Luckily, the Power BI team has recently provided a brand new v3 .NET Power BI SDK which includes helper classes for easier encryption when creating the credential objects. They have provided an example of this being consumed in C# in this blog here: Configure credentials programmatically for Power BI.

Unluckily, these classes haven't yet been translated into corresponding PowerShell cmdlets. Trying to recreate these encryption helpers in PowerShell would take quite a bit of effort, so we really want to utilize this new SDK.

Script

The script below follows the method outlined in the blog I wrote about consuming NuGet packages in PowerShell to create the body for the request to update a data source. Unfortunately, it's a little cumbersome, but due to some current limitations in PowerShell's implementation of the NuGet package provider, this is the nicest we could make it look:

# Install the Power BI package into the current working directory 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, another 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 into the session
$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 into the session
$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)

# Input the credentials (this is using Basic credentials, but the same principle applies to the other types). Any sensitive info should be handled securely (using Azure KeyVault or Azure DevOps secret variables, for example) but for demonstration purposes, I've included a $password variable here so you can see how things work.
$username = ""
$password = ""

# Input gateway public key object (retrieved from Get Gateway or Get Gateways API).
$gatewayPublicKey = @{
    exponent = "AQAB";
    modulus  = ""
}

# Create the objects to perform the necessary encryption on the credentials. Again, since I'm using basic credentials, I'm constructing a new BasicCredentials class. Other classes can be found here: https://github.com/microsoft/PowerBI-CSharp/tree/bf7cdf047a0218f7a8555fa7966445812a043955/sdk/PowerBI.Api/Extensions/Models/Credentials
$gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($gatewayPublicKey.exponent, $gatewayPublicKey.modulus)
$basicCreds = [Microsoft.PowerBI.Api.Models.Credentials.BasicCredentials]::new($username, $password)
$credentialsEncryptor = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)

# Construct the CredentialDetails object. The resulting "Credentials" property on this object will have been encrypted appropriately, ready for use in the request payload.
$credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
    $basicCreds, 
    [Microsoft.PowerBI.Api.Models.PrivacyLevel]::Private, 
    [Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted, 
    $credentialsEncryptor)

# Construct the body for the request.
$body = @{
    credentialDetails = @{
        credentialType      = "Basic";
        credentials         = $credentialDetails.Credentials;
        encryptedConnection = "Encrypted";
        encryptionAlgorithm = "RSA-OAEP";
        privacyLevel        = "Private";
    }
}

# Write it out if you want to inspect it!
($body | ConvertTo-Json) > ".\UpdateDatasourcePayload.json"

That's the complex bit. Now we have the payload, we can make our Update Datasource request like so (after importing the Power BI PowerShell module and logging in using the Connect-PowerBIServiceAccount):

$gatewayId = ""
$datasourceId = ""
$relativeUrlPath = "gateways/" + $gatewayId + "/datasources/" + $datasourceId

Invoke-PowerBIRestMethod -Url $relativeUrlPath -Method PATCH -Body ($body | ConvertTo-Json)

And that's it. A successful update request for an on-prem data source in Power BI. I hope you've found this useful.

Power BI Weekly is a collation of the week's top news and articles from the Power BI ecosystem, all presented to you in one, handy newsletter!
How to consume a NuGet package in PowerShell

How to consume a NuGet package in PowerShell

Ed Freeman

Sometimes the logic for the bit of work you're doing in PowerShell won't be packaged into a handy PowerShell module with cmdlets to perform the operations you need to perform. But what if there is a .NET package listed on NuGet that does contain the assemblies you need for your work? How can we import that into PowerShell and make use of its classes and methods? This blog explains how to do just that, avoiding current pitfalls in PowerShell's implementation of the NuGet package provider.
How to create a Power BI workspace in an Azure DevOps Pipeline using Powershell

How to create a Power BI workspace in an Azure DevOps Pipeline using Powershell

James Broome

A Power BI based solution typically consists of a variety of technologies - for example Azure data platform services containing source data. As such, automation of Power BI resources needs to be considered as part of a wider DevOps strategy. This post describes the specific steps needed in order to fully automate the creation and security of Power BI workspaces using Powershell and Azure DevOps pipelines.
Testing Power BI Dataflows using SpecFlow and the Common Data Model

Testing Power BI Dataflows using SpecFlow and the Common Data Model

James Broome

Whilst testing Power BI Dataflows isn't something that many people think about, it's critical that business rules and associated data preparation steps are validated to ensure the right insights are available to the right people across the organisation. Data insights are useless, even dangerous, if they can't be trusted, so despite the lack of "official support" or recommended approaches from Microsoft, endjin treat Power BI solutions just as any other software project with respect to testing - building automated quality gates into the end to end development process. This post outlines an approach that endjin has used to test Power BI Dataflows to add quality gates and build confidence in large and complex Power BI solutions.

Ed Freeman

Software Engineer I

Ed Freeman

Ed is a Software Engineer helping to deliver projects for clients of all shapes and sizes, providing best of breed technology solutions to industry specific challenges. He focusses primarily on cloud technologies, data analytics and business intelligence, though his Mathematical background has also led to a distinct interest in Data Science, Artificial Intelligence, and other related fields.

He also curates a weekly newsletter, Power BI Weekly, where you can receive all the latest Power BI news, for free.

Ed won the Cloud Apprentice of the Year at the Computing Rising Star Awards 2019.