How to connect to a Sharepoint folder/file in Gen2 Dataflow using a service principle

[DShallcross]

Hi, 

I have an .xlsx file in a Sharepoint folder, I want to connect to this file in a Gen2 Dataflow using a service principle connection. So I can then use the data as inside the file as I wish for ETL purposes. I am already aware of how to access a Sharepoint file using organization credentials but I don't want to do it this way as multiple users will need to run the Dataflow. How do I use a service principle to connect to the sharepoint file in Fabric?

If not possible what are the alternatives?

[miguel]

@DShallcross  @kevin_chansukre If you're having issues with the SPN authentication, and you've verified that your account has all the correct permissions and roles and you can even connect to the SharePoint REST API to retrieve a file whereas with Dataflow you're unable to, please raise a support ticket so an engineer can do a deeper investigation and help you as soon as possible.

[v-tsaipranay]

Hi @DShallcross ,
Thank you for reaching out to the Microsoft fabric community forum.

Currently, connecting to a SharePoint file in a Gen2 Dataflow using a service principal is not supported. The SharePoint connector in Fabric only supports organizational (OAuth2) authentication.

Here are some alternative solutions you can consider:

• Move the file to OneLake or ADLS using a service like Azure Logic Apps or Power Automate authenticated via service principal, then connect to it in Gen2 Dataflow.
• Use Microsoft Graph API with a service principal to extract the SharePoint file and write it to OneLake, where Gen2 Dataflows can access it.

If service principal support for SharePoint in Gen2 Dataflows is critical for your scenario, we recommend submitting an idea on the Fabric Ideas - Microsoft Fabric Community to help prioritize this feature.

Hope this helps. Please reach out for further assistance.

Thank you.

[miguel]

This is incorrect.

@DShallcross Service principal is supported in Dataflow Gen2 as showcased in the screenshot below:

What error message are you receiving when you're trying to leverage this authentication kind with SharePoint folder?

[v-tsaipranay]

Hi @miguel ,

Thank you for sharing this and for the screenshot showing that connecting to the service principal in Dataflow Gen2 is possible.

However, after doing some research and reviewing several Microsoft documents, I did not find any official mention that the service principal is supported. Please refer to the document below that I used brefore my inital resposne : Set up your SharePoint folder connection - Microsoft Fabric | Microsoft Learn

It appears the Microsoft documentation was updated after I posted my previous answer. For more information, see the following link: Service principal support in Data Factory - Microsoft Fabric | Microsoft Learn

Thank you.

[DShallcross]

I second what @kevin_chansukre  has said. This seems to be just a placeholder - you can't currently connect with it. I had a similar error message as he did. 

[kevin_chansukre]

Its just there as a placeholder. Currently, I also face similar issues. Currently, we can't directly access the Sharepoints via Service Principal on the Dataflow Gen2. I manage to get all the rights credential and everything, but I can only see the files on the sharepoints but not access the file inside.

[miguel]

In theory, If you are able to see the files this means that the connector is working and the user has access to the site where files are hosted. If the authenticated user (in this case SPN) is unable to access a particular file or set of files, then it is typically a permission or role-level issue where the SPN is not setup correctly to download the files and/or the folder or the files don't have the correct permission or role set to be downloaded by the authenticated user.

Could you share the error message you're seeing when trying to access a file through the SharePoint connector?

[kevin_chansukre]

Hello @miguel 

For me its this:

The credentials provided for the SharePoints source are invalid.

I believe I also try adding in the Service Principal onto the Sharepoints access via this method from this documentation:

'Service principal access to sharepoint online - Microsoft Q&A'

Do this:

1. Grant Access to Specific SharePoint Site:
   • Navigate to the SharePoint site where you need to grant access.
   • Open the site with the URL format: https://[your_site_url]/_layouts/15/appinv.aspx.
   • Enter the Client ID (Service Principal ID) for your application and click Lookup.
   • Set the permissions in the AppInv page to grant the necessary access.

[U53R]

Hi, for me the error message is just "We could not evaluate this query due to invalid or missing credentials.". This error comes only when navigating to a specific file.

#"Filtered rows"{[Name = "file.xlsx", #"Folder Path" = "sharpoint_site_url"]}[Content]

The file exists and is visible when connected to sharepoint site files. 

SharePoint.Files("sharepoint_site_url", [ApiVersion = 15])

Anything special to keep in mind when giving SPN needed premissions?

[DShallcross]

Thank you for your response. Hopefully this feature is implemented at some point in the future, in the meantime I will explore my options.