Forum Discussion

Koni's avatar
Koni
Regular Visitor
3 years ago
Solved

Gateway Datasource with SPN credentials

Hello,

 

we have a Power BI Data Gateway to connect to our databricks workspace.

When we publish a report we need to bind datasets to a gateway and a datasource.

This datasource is set up with oauth2 and credentials from a user who have permissions to query databricks.

 

Everything worked fine until this user changes his password. Every time this user changes his password he needs to set datasource credentials again.

 

Are we doing something wrong?

 

I tried to do it with an SPN instead of user credentials like this : https://community.powerbi.com/t5/Developer/Updating-OAuth2-Dataset-credentials-via-REST-API/m-p/1962112

But it seems it's not possible. The only thing I can do with the SPN is to take over a dataset and bind datasources. But setting datasource credential doesn't work.

 

How to do?

Need Help
  • Koni's avatar
    Koni
    3 years ago

    Indeed, documentation is not clear : "The encryption algorithm. For a cloud data source, specify None. For an on-premises data source, specify RSA-OAEP and use the gateway public key to encrypt the credentials."

    I think "on-premise" means "when you use data gateway". That's why I need to encrypt.

    It finally works with this script :

     

     

    $keyCreds = [Microsoft.PowerBI.Api.Models.Credentials.KeyCredentials]::new($patDatabricks)
$gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($gateway.publicKey.exponent, $gateway.publicKey.modulus)
$credEncrypt = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)

$credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
       $keyCreds, 
       [Microsoft.PowerBI.Api.Models.PrivacyLevel]::Organizational, 
       [Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted, 
       $credEncrypt)
$credentials = $credentialDetails.Credentials

$body = @{
    credentialDetails = @{
        credentialType      = "Key";
        credentials         = $encryptedCredentials;
        encryptedConnection = "Encrypted";
        encryptionAlgorithm = "RSA-OAEP";
        privacyLevel        = "Organizational";
    }
} | ConvertTo-Json

Invoke-PowerBIRestMethod -Url "gateways/$($gateway.id)/datasources/$($datasourceId)" -Method PATCH -Body $body

     

     

4 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Koni

    Based on my research on all the 3 kinds of Azure data bricks connector in the Power query connector that Power BI used to connect to the Azure data bricks. It seems if you are using the "Username" or "AAD" authorization kind, they will also only need your AAD personal/organization account as the credential instead of the SPN, you may check and explore if the "Personal access token" can be one solution if your situation and requirement is to avoid "Every time this user changes his password he needs to set datasource credentials again."

    Azure Databricks Power Query connector - Power Query | Microsoft Learn

     

    For more information, you can refer to :
    Authentication for Azure Databricks automation - Azure Databricks | Microsoft Learn

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Koni's avatar
    Koni
    Regular Visitor
    3 years ago

    It seems to be a good workaround. But when I try with a power script, I have an error.

    Documentation: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource

    $body = @{
    credentialDetails = @{
        credentialType      = "Key";
        credentials         = "{""credentialData"":[{""name"":""key"", ""value"":""$patDatabricks""}]}";
        encryptedConnection = "Encrypted";
        encryptionAlgorithm = "None";
        privacyLevel        = "Organizational";
    }
} | ConvertTo-Json

Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.value[1].gatewayId)/datasources/$($BounGateway.value[1].id)" -Method PATCH -Body $body | ConvertFrom-Json

     

    Invoke-PowerBIRestMethod: One or more errors occurred. ({
    "code": "DMTS_InvalidEncryptionAlgorithmError",
    "pbi.error": {
    "code": "DMTS_InvalidEncryptionAlgorithmError",
    "parameters": {},
    "details": [],
    "exceptionCulprit": 1
    }
    })
    Invoke-PowerBIRestMethod: Encountered errors when invoking the command: {
    "code": "DMTS_InvalidEncryptionAlgorithmError",
    "pbi.error": {
    "code": "DMTS_InvalidEncryptionAlgorithmError",
    "parameters": {},
    "details": [],
    "exceptionCulprit": 1
    }
    }

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      Hi , Koni  

      Thanks for the test and glad to hear that my previous posted method may help, for your current issue met in the PowerShell command, the error message "DMTS_InvalidEncryptionAlgorithmError" may indicates that the encryption algorithm specified in the PowerShell command is invalid. You can try changing the encryption algorithm to a valid one, such as "RSA-OAEP" or "RSA-OAEP-256". Here is an example of how to modify the PowerShell command to use "RSA-OAEP-256".

       

       

      Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

       

      Best Regards,

      Aniya Zhang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

       

       

       

      • Koni's avatar
        Koni
        Regular Visitor
        3 years ago

        Indeed, documentation is not clear : "The encryption algorithm. For a cloud data source, specify None. For an on-premises data source, specify RSA-OAEP and use the gateway public key to encrypt the credentials."

        I think "on-premise" means "when you use data gateway". That's why I need to encrypt.

        It finally works with this script :

         

         

        $keyCreds = [Microsoft.PowerBI.Api.Models.Credentials.KeyCredentials]::new($patDatabricks)
$gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($gateway.publicKey.exponent, $gateway.publicKey.modulus)
$credEncrypt = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)

$credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
       $keyCreds, 
       [Microsoft.PowerBI.Api.Models.PrivacyLevel]::Organizational, 
       [Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted, 
       $credEncrypt)
$credentials = $credentialDetails.Credentials

$body = @{
    credentialDetails = @{
        credentialType      = "Key";
        credentials         = $encryptedCredentials;
        encryptedConnection = "Encrypted";
        encryptionAlgorithm = "RSA-OAEP";
        privacyLevel        = "Organizational";
    }
} | ConvertTo-Json

Invoke-PowerBIRestMethod -Url "gateways/$($gateway.id)/datasources/$($datasourceId)" -Method PATCH -Body $body