topic Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication in Developer

Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

jaredneedshelp — Tue, 21 Sep 2021 22:14:24 GMT

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/862285

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#?

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

V-lianl-msft — Fri, 24 Sep 2021 04:18:46 GMT

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-powershell

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

jaredneedshelp — Fri, 24 Sep 2021 16:56:06 GMT

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

}

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

VarinNahdarp — Fri, 10 Feb 2023 18:52:13 GMT

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?

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

jaredneedshelp — Fri, 10 Feb 2023 22:50:09 GMT

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.

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

pvuppala — Tue, 18 Apr 2023 03:05:13 GMT

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.

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

jaredneedshelp — Tue, 18 Apr 2023 14:39:12 GMT

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

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

pvuppala — Wed, 19 Apr 2023 13:53:37 GMT

Thanks Jared,

Are you able to share some documentation with Python process?

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

jaredneedshelp — Wed, 19 Apr 2023 14:32:19 GMT

What I did was just based on this

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

Re: Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

pvuppala — Wed, 19 Apr 2023 14:51:57 GMT

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.