Connect to Excel file in SharePoint library using Service Principal

[sujaza]

All

I have been trying to connect to an excel file hosted in a Sharepoint library to my Power BI Report using Service Principal rather than individual's account/credential. This is to replace the individual accounts/credentials for dataset ownership and  to run dataset refreshes using the Service principal. 

I have registered an azure app and set up API permissions to the Sharepoint site and Power BI service. 

Under API Permissions , chosen Graph API Permissions - Sites.Selected and Files.Read.All for Sharepoint and Tenant.ReadWrite.All and Workspace.ReadWrite.All and Dataset.ReadWrite.All under Power BI. 

Also, enabled usage of Service principal in the Power BI Admin center. 

The service principal has been given admin access to the workspace where the Power BI report is published .

The app registration and permissions are done in the Sharepoint site where the data resides.

When attempting to replace dataset credentials in Power BI service , by choosing the 'Service Principal' option, and if I provide the Tenant ID, App Id and Client Secret, it accepts the credentials if the data is coming from a Sharepoint list. However, if the dataset is an Excel file hosted in a Sharepoint document library, it throws an error that says, " Failed to update data source credentials: The credentials provided for the Web source are invalid..... Status code: 400".

Interestingly, I can takeover dataset ownership with Power Shell and run data refresh successfully using Power Shell. Why am I not able to edit dataset credentials from the Power BI service's Data set settings menu when the data set is in a Sharepoint library?

This is a puzzle now, as the list and the library are located within the same Sharepoint site. 

What am I missing here? Any pointers would be appreciated.

Thanks

Suja

[v-achippa]

Hi @sujaza,

As we haven’t heard back from you, we are closing this thread for now. If you have any further issue, please raise a new thread in the community forum, we are ready to assist you on it.

Thank you for being part of Microsoft Fabric Community.

Thanks and regards,

Anjan Kumar Chippa

[sujaza]

Hi Anjan

Thank you for sharing this information. Based on your response, I did a test with the Sharepoint Folder connector and connected using the site url, then filtered down to the excel sheet that was the data source for the report. Then extracted the Binary content and used that in the report. Power BI added additional function/query tables in the data source Queries space. 

Following this I published the report into a workspace. Using Semantic model settings , edited the data source credentials to that of the Service Principal. The credentials were accepted without any error. However, once I proceeded with a manual refresh of the semantic model , I received the following error for the Data refresh again!

"There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details.

• Data source errorThe credentials provided for the SharePoint source are invalid. (Source at (sharepoint site url listed here). The exception was raised by the IDbCommand interface. Table: Query1......."

Here , Query1 is the table generated by Power BI once I filtered down to the binary content and used in the report.

What am I doind wrong here? 

[v-achippa]

Hi @sujaza,

The error here during refresh is due to a runtime access issue with the binary content of the Excel file in SharePoint.When using the SharePoint Folder connector, power bi first accesses the document library and then reads the binary content of the excel file. This binary access step requires explicit permissions to read file contents, not just metadata.

While Sites.Selected and Files.Read.All permissions allow to see the metadata but the service principal also needs explicitly read access to the document library or file itself.

• Grant explicit read access to the service principal at the site or document library level using Microsoft Graph API. Assign read role to the app, this is mandatory when using Sites.Selected, without it power bi cannot read the file content.
• And also the root SharePoint site URL should be used in the SharePoint folder connector. Using a full file path may cause power bi to treat it as a Web connector internally, which does not support SPNs.

Thanks and regards,

Anjan Kumar Chippa

[sujaza]

Hi @v-achippa 

Thank you again for the response!

I have since amended the API permissions on the Service principal to have Sites.Read.All and Files.Read.all for Graph API - Application permissions.

Can you please elaborate on "Grant explicit read access to the service principal at the site or document library level using Microsoft Graph API".

I have used root Sharepoint url of the site to access the site in the Folder connector.

The error remains!

[v-achippa]

Hi @sujaza,

• Thank you for the response. Now that you have added Sites.Read.All and Files.Read.All permissions to the Service Principal, in most cases these should generally be sufficient for accessing the SharePoint content.
• When you were using Sites.Selected before, microsoft requires to manually grant access to specific SharePoint sites. Which means explicitly telling SharePoint like allow this app (Service Principal) to read this site.

This can be done by using a Microsoft Graph API POST request:

Endpoint: POST https://graph.microsoft.com/v1.0/sites/{site-id}/permissions

Request body:

{

  "roles": [ "read" ],

  "grantedToIdentities": [

    {

      "application": {

        "id": "client-id",

        "displayName": "app-name"

      }

    }

  ]

}

Replace ‘site-id’ with your SharePoint Site ID, ‘client-id’ with your Azure App (Service Principal) Client ID and ‘app-name’ with the display name of your registered app. This will explicitly grants read access to the SharePoint site for the Service Principal.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[sujaza]

I have since created a Microsoft support ticket and as per the MS Support team, authentication using Service principal is not supported for 'Sharepoint Folder Connector' as per the MS documentation below.

Power Query SharePoint folder connector - Power Query | Microsoft Learn

Interestingly, documentation on Web connector shows that this auth type is supported.

Power Query Web connector - Power Query | Microsoft Learn

However , I had no luck with both the connectors with Sharepoint.

[sujazv]

This is what Power BI adds to the queries tab once you extract the binary content of the excel file after using sharepoint folder connector.

[sujazv]

Thank you for sharing this information. Based on your response, I did a test with the Sharepoint Folder connector and connected using the site url, then filtered down to the excel sheet that was the data source for the report. Then extracted the Binary content and used that in the report. Power BI added some functions/query tables in the data source query space. 

Following this I published the report into a workspace. Using Semantic model settings , edited the data source credentials to that of the Service Principal. The credentials were accepted without any error. However, once I proceeded with a manual refresh of the semantic model , I received the following error for the Data refresh again!

"There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details.

• Data source errorThe credentials provided for the SharePoint source are invalid. (Source at (sharepoint site url listed here). The exception was raised by the IDbCommand interface. Table: Query1......."

Here , Query1 is the table generated by Power BI once I filtered down to the binary content and used in the report.

What am I doind wrong here? 

[v-achippa]

Hi @sujaza,

Thank you for reaching out to Microsoft Fabric Community.

Power bi UI does not natively support Service Principal authentication for SharePoint Excel file connections, even though it works in power shell. This could be due to some mismatch in authentication methods or insufficient API permissions.:

• Since you have the necessary permissions to the service principal and able to access both SharePoint and power bi services, ensure that these permissions have been explicitly granted admin consent.
• Reconnect the excel file in the desktop, use SharePoint Folder not Web Connector, for authentication to work properly in the power bi service UI and enter the root SharePoint site URL, not the full file URL.
• Reconfigure the service principal credentials, after publishing the report to the power bi service, go to the dataset settings and update the data source credentials to use the service principal authentication.
• Even if the service principal has access to the site, ensure it has explicit Read access to the document library

Since the power shell refresh works and the service principal is correctly configured, but the issue here is power bi service UI does not support Web Connector authentication with service principals. By using the SharePoint Folder instead of Web Connector ensures a proper authentication.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @sujaza,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa