Authentication Method for Refreshing Power BI Dataset (Azure SQL Source)

Hey kankamon ,

You're navigating a common challenge in Power BI authentication for Azure SQL dataset refreshes. Here's a breakdown of the options and the best practice for long-term sustainability and security:

Available Authentication Options

1. OAuth2 (User Delegated)

• Pros:

  • Secure and easy to set up for testing and development.

  • Uses the user’s Entra ID credentials.

• Cons:

  • Requires whitelisting Power BI service IP ranges, which can be problematic in restricted environments.

  • Tokens are short-lived; not ideal for unattended refresh.

2. Service Principal (App-Only Authentication)

• Pros:

  • No IP whitelisting required.

  • Supports automated, unattended dataset refreshes.

  • Aligns with enterprise identity and access management practices.

• Cons:

  • The default client secret expires in 2 years.

  • Key rotation must be handled securely (manually or via automation).

Recommended Solution: Service Principal with Managed Identity or Certificate

To solve the key rotation issue, consider one of the following:

A. Use a Certificate Instead of a Client Secret

• Register a certificate with a 10-year expiration.

• Configure the service principal to use this certificate for authentication.

• Update the Power BI service to authenticate using the certificate.

• Benefit: Removes the 2-year key rotation issue.

B. Use a Managed Identity (if using Azure-hosted service like Azure Data Factory or Azure Functions)

• Power BI doesn’t yet support managed identities natively, but if you move the refresh logic to an Azure Data Factory pipeline or Logic App, you can:

  • Use a Managed Identity for secure and rotation-free authentication.

  • Call Power BI REST APIs to trigger dataset refreshes from there.

• Benefit: No secrets or certificates to manage.

Setup:

• In Power BI Service:

  • Go to Settings > Datasets > Data source credentials

  • Select Service Principal and use the correct App ID and secret/cert.

• Ensure your Service Principal has:

  • Entra ID permissions (Directory.Read.All)

  • Azure SQL Database permissions (db_datareader or custom as required)

  • Power BI workspace permissions (Admin or Contributor)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam