Connect SharePoint List using Service Principal from Power BI service

[Anonymous]

Hi,

I'm using SharePoint list as data source in my Power BI report and I've to connect the SharePoint List through Service Principal in the Power BI Service for the connection configuration.

Following Pre-requisites are already taken care:

1) We have an Azure App registration in Entra ID

2) having the API permission site.selected with full control access

3)Service principal is created

4) Cloud security group is created (Security group in Entra ID) and assigned Service Principal inside this security group.

5) Enabled allow service Principal API in the Power BI admin portal tenant setting and the cloud security group is added under specific security group option.

But I'm facing "Failed to update datasource credentials . Credential of the sharepoint source are invalid." issue.

Can anyone pls. help me on solving this issue? 

Regards,

Selvi

[Nanireddy1999]

please anyone confirm if the above solution works. thank you

[Anonymous]

Hi @Anonymous,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue while connecting your SharePoint data source with your PowerBi. The solution has been provided by @rajendraongole1. If your issue is resolved then please accept it as solution.

I would also take a moment to personally thank @rajendraongole1, for his active participation and the solutions you’ve been sharing in the community forum. Your contributions makes a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[rajendraongole1]

Hi @Anonymous  - Please find the solved thread and related document

Solved: Credential issues in Power BI connecting to ShareP... - Microsoft Fabric Community

Register a Microsoft Entra app and create a service principal - Microsoft identity platform | Microsoft Learn

[Anonymous]

Hi @rajendraongole1 Thanks for your valuable input! I'm able to connect sharepoint from power bi desktop and same works in power bi service as well. Now in Power BI service, due to data security, we are trying to connect the sharepoint source using service principal instead of our mail id. Register a Microsoft Entra app and create a service principal - Microsoft identity platform | Micros... this was already taken care but still we are facing an issue to connect the sahrepoint using SPN. Could you pls. check on this and share your solution.

Regards,

Selvi

[rajendraongole1 · Accepted solution]

Hi @Anonymous - To connect to SharePoint from Power BI using a Service Principal Name (SPN) instead of personal credentials, follow these steps. It seems you've already registered the Microsoft Entra (Azure AD) app and created a service principal, so the issue might lie in permissions or configuration. 

Please chck the steps below:

1. Ensure Microsoft Entra App Permissions are Configured Correctly
Application Permissions:

Open the Azure portal.
Go to Azure Active Directory > App registrations > Select your app.
Navigate to API Permissions.
Add permissions for SharePoint:
API: Microsoft Graph
Permissions:
Sites.Read.All (to read all site collections and subsites)
Sites.Manage.All (if you need write permissions)
Click Grant Admin Consent for these permissions.
Set Up Roles on SharePoint Online:

Go to the SharePoint admin center: https://<yourtenant>-admin.sharepoint.com.
In Advanced Settings, grant the App-only access by assigning the service principal to the desired site.
Site Permissions:

Assign permissions directly to the app on the specific SharePoint site or library:
Go to the site > Settings > Site Permissions.
Add the service principal as a user (use the app ID).
2. Configure Power BI Service to Use Service Principal
Ensure you configure the data source correctly in Power BI:

Install Power BI Desktop and Test with Service Principal:

In Power BI Desktop, when connecting to SharePoint Online:
Use the Organizational Account option.
Provide the Client ID and Client Secret of your registered app.
Enable SPN in Power BI Service:

In Power BI Service, go to Settings > Admin Portal > Tenant Settings.
Enable Service principal authentication under Developer Settings.
3. Test SharePoint Connection in Power BI
In Power BI Service, follow these steps to set up the dataset connection:

Set Up the Dataset:

In Power BI Desktop:
Connect to SharePoint Online using the app's credentials (Client ID and Secret).
Publish the report to Power BI Service.
Configure the Dataset Credentials:

In Power BI Service:
Go to the Workspace where your dataset is published.
Select Settings for the dataset.
Under Data Source Credentials, choose OAuth2.
Use the Service Principal credentials (Client ID and Secret).
4. Troubleshooting Common Issues
Here are common causes of connection failures and their solutions:

Issue: Missing Admin Consent

Make sure admin consent has been granted for the app permissions in the Azure portal.
Issue: SharePoint Permissions Not Granted

Ensure that the service principal (app) has the required permissions on the SharePoint site or library.
Issue: Dataset Not Refreshing in Power BI Service

Check the gateway (if using one) and ensure that service principal authentication is enabled in the gateway settings.
Issue: Authentication Error

Ensure that you’re using the correct Tenant ID, Client ID, and Client Secret.
Verify the app registration details and ensure the client secret has not expired.

[Anonymous]

Hi @rajendraongole1 Thank you very much for the detailed info. This really helps.

I would like to clarify two points with you:

1) For the below API permission currently we have site.selected permission with full control permission. But we are not able to connect having this Permission. So it seems like we have to go with site.read.all Permission right with admin Consent? Pls. confirm. Do we need site.write.all as well?

API: Microsoft Graph
Permissions:
Sites.Read.All (to read all site collections and subsites)
Sites.Manage.All (if you need write permissions)
Click Grant Admin Consent for these permissions.

2. Test SharePoint Connection in Power BI

How to test the sharepoint connection using cliend id and secret key. Pls. share your inputs on this
In Power BI Service, follow these steps to set up the dataset connection:

Set Up the Dataset:

In Power BI Desktop:
Connect to SharePoint Online using the app's credentials (Client ID and Secret).
Publish the report to Power BI Service.
Configure the Dataset Credentials:

[Ariel_Rosario]

did you get any clarification here?