Sharepoint Data Source - Service Principal authentication failing - Invalid connection credentials

[jwinchester_WS]

Our BI team is trying to add a cloud connection in the PowerBI control center and is getting an error.  

The current working method is to use OAuth 2.0 and enter the developers credentials however this is not ideal for many reasons so they would like to use a Service Principal.  We set up an enterprise app in Azure and created a client secret. For testing purposes, we added Sites.FullControl.All  Application permissions and have granted Admin Consent.

We also enabled Service principals can use Fabric API's for the entire organization setting in PowerBI Admin Portal - Tenant Settings - Developer Settings

When we attempt to add the connection under Settings - Manage connections and Gateways - Connection, we get the following error - Unable to create connection for the following reason: Invalid connection credentials.

[v-jingzhan-msft · Accepted solution]

Hi @jwinchester_WS 

I think you might lack a step to provide the service principal with specific permissions in the SharePoint site. Just like if a user wants to access a SharePoint site, they should be added as owner or member in the site. A service principal should be added to the SharePoint site and granted specific permissions too. 

However, service principals are typically used for app-only access rather than being added as site members or owners in the traditional sense. You could try the methods provided in these threads or blogs below to give it permission on the SharePoint site.

Service principal access to sharepoint online - Microsoft Q&A

How to connect SharePoint online using service principal? - Microsoft Q&A

Setting up SharePoint app-only principal with App Registration

(To be honest, I have tried some methods of them but still failed to authenticate with service principal. I don't have much experience with SharePoint, so I'm not sure if I have made some wrong operations or the approaches might be ineffective.)

I hope my direction would be correct. If not, please correct me. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[nalinash]

Hi @jwinchester_WS 

I think you need to check application permissions first to ensure that the Service Principal has necessary permissions. As you had assigned Sites.FullControl.All . Would you like to double-check if this is sufficinet for your case. If you are accessing specific sites, you may need Sites.Read.All or Sites.ReadWrite.All permissions as well as it should be granted by Azure Active Directory Admin Center and verify the Admin Consent.

Second thing, you should provide the root url of your SharePoint Site like https://yourtenant.sharepoint.com/sites/your-site 

Best regards,

Ash

If this post helps you then please Accept it as Solution to help other members find it. Appreciate your Kudos!

[v-jingzhan-msft · Accepted solution]

Hi @jwinchester_WS 

I think you might lack a step to provide the service principal with specific permissions in the SharePoint site. Just like if a user wants to access a SharePoint site, they should be added as owner or member in the site. A service principal should be added to the SharePoint site and granted specific permissions too. 

However, service principals are typically used for app-only access rather than being added as site members or owners in the traditional sense. You could try the methods provided in these threads or blogs below to give it permission on the SharePoint site.

Service principal access to sharepoint online - Microsoft Q&A

How to connect SharePoint online using service principal? - Microsoft Q&A

Setting up SharePoint app-only principal with App Registration

(To be honest, I have tried some methods of them but still failed to authenticate with service principal. I don't have much experience with SharePoint, so I'm not sure if I have made some wrong operations or the approaches might be ineffective.)

I hope my direction would be correct. If not, please correct me. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[FabianSchut · Accepted solution]

As far as I know, you should not have to give the service principal permissions in the Sharepoint site if you use Sites.FullControl.All. This should only be necessary when you use Sites.Selected. The steps to give the service principal access to a specific site can be found here: https://marczak.io/posts/2023/01/sharepoint-graph-and-azure-sp/

[jwinchester_WS]

Thanks for this great link.  This solves a few problems I have been having with managing permissions on SharePoint sites.

[FabianSchut]

Hi, based on the URL in the last screenshot, you do not create a connection for a Sharepoint site, but the whole Sharepoint tenant. Can you try to change the URL to the site you are trying to connect to? The URL should look something like this: https://TENANT_NAME.sharepoint.com/sites/SITE_NAME