Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

[jaredneedshelp]

I am trying to figure out how we can update credentials for a power bi datasource in our gateway via the API only using powershell, we want to be able to automatically rotate credentials just running the powershell script. 

In our testing we have had some sucess doing the following:

Connect-PowerBIServiceAccount -Credential (Get-Credential)

(Eventually we will use permanent credentials here, but we are still in a testing phase)

$headers = Get-PowerBIAccessToken

Write-Output $headers

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/{gatewayID}/datasources/{datasource id}"

$body = "{

  `"credentialDetails`": {

    `"credentialType`": `"Basic`",

    `"credentials`": `"{Encrypted Credentials}`",

    `"encryptedConnection`": `"Encrypted`",

    `"encryptionAlgorithm`": `"RSA-OAEP`",

    `"privacyLevel`": `"None`",

    `"useEndUserOAuth2Credentials`": `"False`"

  }

}"

$api_return = (Invoke-RestMethod -Uri $uri –Headers $headers –Method Patch -Body $body -ContentType application/json)

This works if we generate the encypted credentials via a c# script in .net and copy those over that you can find in a lot of the documentation and other fourm pages, but we would like to have it all be done automatically in powershell. The most promising answer I say similar to this was in the following form page 

https://community.powerbi.com/t5/Developer/Power-BI-REST-API-via-Powershell-Create-Datasource/m-p/86...

which was trying to convert a c# script to powershell. I have not been able to get their code to work properly, it gives errors on the copy stage of the script and the credentials it generates are rejected by the api, but I feel like its close. 

Has anyone had sucess in being able to do the encryption process in powershell and passing it to the api without using c#?

[pvuppala]

I"m running into some "timeout" issues when I submit for Encrypt credentials using that python app via localhost:5000.  This is the error

Error retrieving Access token
HTTPSConnectionPool(host='login.microsoftonline.com', port=443): Max retries exceeded with url: /common/oauth2/v2.0/.well-known/openid-configuration (Caused by ConnectTimeoutError(, 'Connection to login.microsoftonline.com timed out. (connect timeout=None)'))

Not sure whats causing this..I will try to look at the .NET Core option today for encryption.

[jaredneedshelp · Accepted solution]

This is how we eventually got it working in just powershell, if anyone is interested:

Get-PackageSource

#Tried the NuGet package manager

#https://docs.microsoft.com/en-us/nuget/install-nuget-client-tools

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Find-PackageProvider -Name "NuGet" -AllVersions

# Install the Power BI package into the current working directory if it's not already installed

if (!(Test-Path ".\Microsoft.PowerBI.Api.3.28.1" -PathType Container)) {

    Install-Package -Name Microsoft.PowerBi.Api -ProviderName NuGet -Scope CurrentUser -RequiredVersion 3.28.1 -SkipDependencies -Destination . -Force

}

# Install the Client Runtime package, a dependency of the Power BI package

if (!(Test-Path ".\Microsoft.Rest.ClientRuntime.2.3.21" -PathType Container)) {

    Install-Package -Name Microsoft.Rest.ClientRuntime -ProviderName NuGet -Scope CurrentUser -RequiredVersion 2.3.21 -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.21\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.28.1\lib\net48\Microsoft.PowerBI.Api.dll"

}

else {

    $pbipath = Resolve-Path ".\Microsoft.PowerBI.Api.3.28.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 = (username)

$password =(password)

# Input gateway public key object (retrieved from Get Gateway or Get Gateways API).

$gatewayPublicKey = @{

    exponent = "exponet";

    modulus  = "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/PowerB...

$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";

    }

}

[VarinNahdarp]

I am trying to implement this with OAUTH2. Is the flow for OAUTH2 similar to Basic Auth? Can you provide some pointers for OAUTH2 implementation?

[jaredneedshelp]

I haven't tried it using OAUTH2, and we actually ended up switching over to doing it in python. But from some inital searching I imagine you could change the body to be like this:

$body = @{ "type" = "OAuth2", "typeProperties" = @{ "clientId" = "<client-id>", "clientSecret" = "<client-secret>", "tokenUrl" = "<token-url>", "resource" = "<resource>" } }

But I can't say for sure, sorry. 

[pvuppala]

We're dealing with this encryption so that we can create a data source via API's  I haven't had much luck with the API call.  Were there any updates/improvements to this PowerShell Script?  Please share any thoughts as we're trying to automate some of this process to extract password from our internal vault and call "Create datasource" API but it doesn't seem like its a simple call.

[jaredneedshelp]

We abandonded the powershell script to use python instead, and I haven't done the create datasource, just rotating an existing one, sorry

[pvuppala]

Thanks Jared,

Are you able to share some documentation with Python process?

[jaredneedshelp]

What I did was just based on this 

https://github.com/microsoft/PowerBI-Developer-Samples/tree/master/Python

[V-lianl-msft]

Here is a related blog, hoping to help you:

https://endjin.com/blog/2020/12/how-to-update-credentials-for-an-on-prem-power-bi-data-source-using-...