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.
