Forum Discussion

msprog's avatar
msprog
Advocate III
1 year ago
Solved

Service principal for dataset datasource credential

Hey team, 

I have a file located in a sharepoint teamsite document library. 

In PowerBI desktop, i have used the web connector to directly read the file . I have also used the sharepoint folder to read this file. So I have 2 tables in the model both reading from the same file but with different connectors. 

I publish this onto the service. Now i want to use a service principal to refresh the dataset. 

I have created an app in Entra, gave the API permissions of Sites.ReadAll and Files.Read.All

 now when i configure in the powerBI service dataset data source i get this error.

Failed to update data source credentials: The credentials provided for the Web source are invalid. (Source at https://xxxxx.sharepoint.com/sites/xxxxx/Shared%20Documents/adhocfile/test.csv.)Show details

 

similary for the sharepoint source , there is an identical error: Failed to update data source credentials: The credentials provided for the SharePoint source are invalid.

 

I am not sure what i am doing wrong and begining to wonder if the service principal authentication is supported at all in the PowerBi service for Web and Sharepoint folder connectors. 

Please advise

Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi msprog,

    Apologize for the inconvenience caused. Thank you for your follow-up, that is a good question. We acknowledge that this limitation is not clearly documented at present.

     

    While Power BI Service does let you enter Service Principal details (like tenant ID, client ID, and secret) in the credentials dialog, it’s important to note that not all connectors support SPN-based authentication behind the scenes.

    In your case, both the Web and SharePoint Folder connectors rely on delegated OAuth2, which means they expect a signed in user contex.

    If using a Service Principal is a must for your scenario, here are a couple of potential workarounds:

    • Use the Microsoft Graph API in combination with Web.Contents() and an access token obtained via the client credentials flow (SPN). This would require setting up a gateway to support the refresh in Power BI Service.
    • Alternatively, you could build a custom connector that securely handles SPN token acquisition and integrates with your data source.

    Kindly refer to the below mentioned documentation links:
    OAuth 2.0 client credentials flow on the Microsoft identity platform - Microsoft identity platform | Microsoft Learn
    Web.Contents - PowerQuery M | Microsoft Learn

    If you have any questions, please do not hesitate to contact us, and we will be happy to assist you.
    Thank you for using the Microsoft Community Forum.

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    1 year ago

    Hi. Many users are talking about this issue, but I have only seen one person talking about a possible solution. He mentions that

    Setting the property "DisableCustomAppAuthentication" in your SharePoint tenant to "false" is doing the trick

    You can read the whole conversation here: https://community.fabric.microsoft.com/t5/Service/Adding-a-SharePoint-data-source-with-a-service-principal/td-p/3791170

    In addition, this is being investigated by microsoft. You can add information in the issue conversation here: https://community.fabric.microsoft.com/t5/Issues/Connect-to-Sharepoint-with-Service-Principal/idi-p/3968004

    I hope that helps,

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    Hi msprog,

    Thank you for reaching out to the Microsoft Fabric Community Forum, and we appreciate the detailed context you’ve provided along with the steps you've already taken.

    Based on your scenario, you are right to question whether Service Principal (SPN) authentication is supported for the Web and SharePoint Folder connectors in the Power BI Service. Below is a breakdown of the current limitations and recommended alternatives to help resolve the issue.

    Power BI does not currently support using a Service Principal (i.e., app-only token) for authenticating Web or SharePoint Folder connectors when configuring dataset refresh credentials in the Power BI Service. These connectors are designed to use OAuth2 delegated authentication, which requires signing in with a user account. Currently, the credential dialog in Power BI Service does not support entering a client ID/secret for app-only access.

    Using SharePoint Folder in Power BI
    Authentication in desktop apps - Power Query | Microsoft Learn

    Use Delegated OAuth2 (User Identity): As a supported approach, configure the dataset credentials using OAuth2 and sign in with a user account that has access to the SharePoint site. You can use Web.Contents() in Power Query with a manually obtained token via client credentials flow.
    Get file content from SharePoint using Microsoft Graph
    Access token request with client credentials flow
    Web.Contents - PowerQuery M | Microsoft Learn

    Please note that this would typically require: Custom M code to acquire and use the token. A gateway to support token logic and bypass test connection issues.

    Use a Custom Connector + Gateway (Enterprise Solution): You can develop a Power Query Custom Connector that handles SPN authentication using the Graph API, and deploy it via an On-premises Data Gateway.

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

    • msprog's avatar
      msprog
      Advocate III
      1 year ago

      Thanks for this input v-kpoloju-msft  , 

      Is there any MSFT doc that clearly mentions this limitation with Web and Sharepoint folder connector? if there is, can you please point me to that,

      Also you mention that  Currently, the credential dialog in Power BI Service does not support entering a client ID/secret for app-only access.  Sorry what do you mean by this?, I can enter  values for tenant id, Service Principal Id and Service Principal key.  Please can you explain

      thanks

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        Hi msprog,

        Apologize for the inconvenience caused. Thank you for your follow-up, that is a good question. We acknowledge that this limitation is not clearly documented at present.

         

        While Power BI Service does let you enter Service Principal details (like tenant ID, client ID, and secret) in the credentials dialog, it’s important to note that not all connectors support SPN-based authentication behind the scenes.

        In your case, both the Web and SharePoint Folder connectors rely on delegated OAuth2, which means they expect a signed in user contex.

        If using a Service Principal is a must for your scenario, here are a couple of potential workarounds:

        • Use the Microsoft Graph API in combination with Web.Contents() and an access token obtained via the client credentials flow (SPN). This would require setting up a gateway to support the refresh in Power BI Service.
        • Alternatively, you could build a custom connector that securely handles SPN token acquisition and integrates with your data source.

        Kindly refer to the below mentioned documentation links:
        OAuth 2.0 client credentials flow on the Microsoft identity platform - Microsoft identity platform | Microsoft Learn
        Web.Contents - PowerQuery M | Microsoft Learn

        If you have any questions, please do not hesitate to contact us, and we will be happy to assist you.
        Thank you for using the Microsoft Community Forum.