cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaredneedshelp
Frequent Visitor

Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

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
 
 
$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 
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#?
1 ACCEPTED SOLUTION
jaredneedshelp
Frequent Visitor

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

 

#Tried the NuGet package manager

 

[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";
    }
}

View solution in original post

2 REPLIES 2
jaredneedshelp
Frequent Visitor

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

 

#Tried the NuGet package manager

 

[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";
    }
}

View solution in original post

V-lianl-msft
Community Support
Community Support

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors