Power BI REST API & PowerShell Update Datasource

[mark_carlisle]

We have a lot of data sources (~200) and an IS enforced policy to change passwords every 45 days. The current process is for someone to go through these data sources and input the new credentials as and when they change. I want to be able to improve the efficiency of this process by creating a PowerShell script.

I'm using the officially supported Power BI PowerShell module but I'm having difficulty building the Windows version of the -Body for the Invoke-PowerBIRestMethod command. Specifically the encryption of the credentials, highlighted in red below;

{
  "credentialDetails": {
    "credentialType": "Windows",
    "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"contoso\\john\"},{\"name\":\"password\", \"value\":\"*****\"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "RSA-OAEP",
    "privacyLevel": "Organizational"
  }
}

MS have provided documentation on how to achieve this but its written in C#. Is there anyway to integrate this code into a PowerShell script or is there a way to encrypt the string with RSA-OAEP encryption natively in PowerShell?

[vpartem]

Anyone have the RSA-OAEP encryption natively in PowerShell?

[matthew_davis02]

Hi there, came across this post when I was trying to follow the next step in the update process (calling step 5 in the documentation provided in this post).  I have written a program in python that calls a powershell script once the encryption step is necessary, below is the powershell script written.  Keep in mind that this is tailored for my needs (i.e. passing in gateway, datasource, username, password) from an API call.  Furthermore, this is the final steps of a processes wrapped in an AWS environment.  I got the source from here

---

@vpartem wrote:

Anyone have the RSA-OAEP encryption natively in PowerShell?

Function echoItem($user, $password, $exponent, $modulus)
{
"The username is {0}`nThe password is {1}`nThe exponent is {2}`nThe modulus is {3}`n" -f $user, $password, $exponent, $modulus
}
Function EncryptCredentials($user, $password, $exponent, $modulus)
{
# Install the Power BI package into the current working directory if it's not already installed
if (!(Test-Path ".\Microsoft.PowerBI.Api.3.20.0" -PathType Container)) {
    Install-Package -Name Microsoft.PowerBi.Api -ProviderName NuGet -Scope CurrentUser -RequiredVersion 3.20.0 -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.20.0\lib\net48\Microsoft.PowerBI.Api.dll"
}
else {
    $pbipath = Resolve-Path ".\Microsoft.PowerBI.Api.3.20.0\lib\netstandard2.0\Microsoft.PowerBI.Api.dll"
}

# Load the Power BI assembly into the session
[System.Reflection.Assembly]::LoadFrom($pbipath)
# Input gateway public key object (retrieved from Get Gateway or Get Gateways API).
$gatewayPublicKey = @{
    exponent = $exponent;
    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($user, $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";
        useEndUserOAuth2Credentials = "False"
        privacyLevel        = "Organizational";
    }
}

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