Gateway Datasource with SPN credentials

[Koni]

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

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?

[Koni]

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]

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 · Accepted solution]

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

[v-yueyunzh-msft]

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