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

[kankamon]

Hi,

I'm very new to Power BI and have a question about the authentication methods available for refreshing a dataset (semantic model).

My dataset retrieves data from Azure SQL. I developed the report in Power BI Desktop and used Microsoft Entra ID for authentication.

However, after publishing the report to the workspace, I noticed that to refresh the dataset, I need to choose an authentication method — either OAuth2 or Service Principal. To use OAuth2, we would need to whitelist all Power BI IP addresses, which is not preferred by my data engineer.

That leaves us with the Service Principal option, but its key only lasts for 2 years, which isn't sustainable in our case, as it would require key rotation every two years.

Could you please suggest the best approach to authenticate in order to enable dataset refresh?

Thank you in advance

[v-lgarikapat]

Hi @kankamon ,

Apologies for the late reply.
I have included the troubleshooting learning document and the previously resolved thread. This may help you to resolve the issue.

Troubleshoot scheduled refresh for Azure SQL databases - Power BI | Microsoft Learn

Solved: SQL Azure Import Data Refresh - Microsoft Fabric Community

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[v-shamiliv]

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.

[kankamon]

I still didn't get a concluded solution

[Poojara_D12]

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.

[Nasif_Azam]

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

[kankamon]

Hi sir, thank you for your helpful answer.

Following your advice, I asked my engineer to create a certificate for our Service Principal instead of a client secret. However, we don't know where to use this certificate in the Power BI Service.

When we navigate to the data source credentials, the field labeled "Service principal key" seems to expect a client secret, not a certificate.

Could you please clarify where we should put the certificate on?

[Nasif_Azam]

Hey @kankamon ,

To use the certificate for your Service Principal in Power BI, follow these steps:

1. Upload the Certificate in Azure AD:

   • Go to Azure AD > App Registrations > Your Service Principal > Certificates & Secrets.

   • Upload your certificate there.

2. Configure Power BI:

   • In Power BI Service, go to Settings > Datasets > Data source credentials.

   • Choose Service Principal (App-only authentication).

   • Instead of using a client secret, Power BI will now use the certificate you uploaded in Azure AD.

Basically, the certificate is linked to the Service Principal in Azure AD, and Power BI will use it for authentication during the refresh.

For Detailed Information:

Create a Service Principal in Azure AD

Configure certificates for Azure AD App Registration

Configure Service Principal authentication for Power BI

Power BI Data Source Credentials Documentation

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

[BhavinVyas3003]

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.