Forum Discussion

Helper I

5 years ago

Solved

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

Need Help

jaredneedshelp
5 years ago
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";
    }
}

jaredneedshelp

Helper I

5 years ago

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

}

VarinNahdarp

Regular Visitor

3 years ago

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
Helper I
3 years ago
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
  Helper V
  3 years ago
  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
    Helper I
    3 years ago
    We abandonded the powershell script to use python instead, and I haven't done the create datasource, just rotating an existing one, sorry

Forum Discussion

Update Datasource Credential Using Only Powershell and RSA-OAEP Authentication

Related content

OAuth2 Authentication credentials

Service principal for dataset datasource credential

Power BI Services Connections Authentication Credentials

Sharepoint authentication error "We couldn't authenticate with the credentials provided"

We couldn't authenticate with the credentials provided. Please try again - Using right credentials