How do I configure the Service Principal credentials expiration on refresh to connect Power BI Sql Azure Data Source?

Question

I am using Powershell to update credentials on a Power BI datasource based on Sql Azure.

The credentials are that of a Service Principal's, I obtain a Sql access token and submit changes.

The payload lokos like this:

$UpdateUserCredential = @{
    credentialDetails = @{
        credentialType = "OAuth2"
        credentials = "{`"credentialData`":[{`"name`":`"accessToken`", `"value`":`"$token`"}]}"
        encryptedConnection = "Encrypted"
        encryptionAlgorithm = "None"
        privacyLevel = "None"
    }
} | ConvertTo-Json
Copy

The datasource is set to refresh every 10 days. Do I need to update the credentials programmatically when it expires each time - how often does the credential expire?

Is there a better way to retain credentials within Power BI for a datasource connection that can survive future scheduled refreshes without having to re-enter credentials? e.g using basic username password authentication perhaps, as opposed to OAuth?

Answer 1

Not an expert in powerbi, but for the service principal, you could create a secret i.e. credential whose lifetime is as long as possible with powershell New-AzureADApplicationPasswordCredential, make sure you have installed the AzureAD powershell module first. (In the portal, at most you can set it with 3 years)

Connect-AzureAD
New-AzureADApplicationPasswordCredential -ObjectId <app-registration-object-id> -EndDate ((Get-Date).AddYears(100))
Copy