Forum Discussion

jaredneedshelp's avatar
jaredneedshelp
Helper I
4 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 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#?
Need Help
  • jaredneedshelp's avatar
    jaredneedshelp
    4 years ago
    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/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";
        }
    }

9 Replies

  • jaredneedshelp's avatar
    jaredneedshelp
    Helper I
    4 years ago
    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/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's avatar
      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's avatar
        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's avatar
    pvuppala
    Helper V
    3 years ago

    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.