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

Use Managed Identity from a Fabric Premium workspace to connect to Azure SQL. Grant that identity access to the SQL DB. This avoids both IP whitelisting and service principal maintenance.

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

Hi kankamon 

Given your scenario—where the dataset connects to Azure SQL using Microsoft Entra ID in Power BI Desktop, but whitelisting Power BI IP addresses for OAuth2 isn't feasible and using a Service Principal raises concerns due to the key rotation—you’re encountering a common challenge in enterprise environments. The most sustainable and secure approach would be to use Managed Identity for authentication. Managed Identity is supported when your Power BI workspace is hosted on Fabric Capacity (F64, for instance), and it avoids the need to manage secrets or rotate keys. It authenticates the Power BI service itself directly with Azure SQL using Entra ID, and the identity is managed by Microsoft, so there's no client secret involved. To use this, you need to enable Managed Identity in the workspace settings, and then grant that Managed Identity the necessary roles (e.g., db_datareader) in your Azure SQL database. This approach is secure, supports automated refreshes, and removes the burden of IP whitelisting or key expiration. If you're not on Fabric or Premium capacity, however, then rotating a Service Principal key using tools like Azure Key Vault and scripting regular updates may be your fallback—though less ideal. Managed Identity is the recommended best practice when available.

Hi kankamon 

Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.