Question: 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

[Poojara_D12]

Hi @kankamon 

You're encountering a common challenge when configuring dataset (semantic model) refreshes in Power BI Service, especially when connecting to Azure SQL Database using Microsoft Entra ID (formerly Azure AD) authentication. In Power BI Desktop, using your personal Microsoft Entra credentials works fine during development, but once the report is published to the service, the dataset requires a gateway-less cloud connection and an explicit data source credential to perform scheduled or programmatic refreshes.

When refreshing a dataset connected to Azure SQL, Power BI supports two main Entra ID-based authentication options: OAuth2 (delegated user identity) and Service Principal. As you've noted, OAuth2 requires the service to connect using user context, which in practice may involve dynamic outbound IPs—leading your data engineer to worry about whitelisting all of Power BI's regional IP addresses, which change over time and are hard to manage securely.

On the other hand, the Service Principal option offers a more scalable and secure approach because it allows app-based authentication without relying on individual users. However, the limitation you're concerned about—the 2-year lifespan of a client secret—is valid, as Microsoft enforces this default expiration for security reasons. To address this, the best long-term solution is to replace the client secret with a certificate-based authentication, which can offer a lifespan of up to 10 years, making it far more sustainable and secure. This approach removes the need for frequent key rotation and is well-suited for enterprise-grade automation and unattended refresh scenarios.

So, in your case, the recommended approach would be to use a Service Principal with certificate-based authentication to connect to Azure SQL. This balances security, maintainability, and operational stability, avoiding the risks and complexity associated with IP whitelisting or short-lived secrets. Your Azure admin can register a certificate in Entra ID and update the Azure SQL server to allow Service Principal access with appropriate permissions.

[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?

[v-nmadadi-msft]

Hi @kankamon ,

Currently, the available authentication options are Basic, Anonymous, OAuth2, and Service Principal. If you choose to authenticate using a Service Principal, a Service Principal Key is required. If these options do not meet your requirements, and If any alternate feature is important for your functionality Please consider sharing your suggestion in the Power BI Ideas forum
 Fabric Ideas - Microsoft Fabric Community,
where the product team actively monitors user feedback. Ideas with strong community support are more likely to be considered for future implementation. Posting there helps ensure your request reaches the right audience and contributes to shaping the product roadmap.

Thank you

[Deku]

If you want to use oauth you could create a user principal (UPN)