We are using the Excel Online connector which is authenticated with a service account and uses delegated permissions to access Excel files on Sharepoint. The idea is that all relevant files will be shared with this account and then loaded into our DWH. The required files are visible on Sharepoint when siging in with the user:
But for some reason, they are not when using the Excel Online connector (with option Show shared documents = ‘True’). I know that the connector uses the /SharedWithMe (OneDrive) call to fetch shared items, since that is what I inferred from the logging.
This call indeed retrieves no results through the Graph Explorer, but the files are visible through another, similar, call on the Graph Explorer (the one from ‘Insights’):
Why are files visible on Sharepoint but not on OneDrive? Is there a way to work around this? I have seen use cases where they actually are visible on both Sharepoint and OneDrive and the connector is working properly.
How are permissions set on this service account for delegated access ? This could be a good question on MS SharePoint forum using the Graph Explorer example.
These are the User consent permissions on the Graph Explorer in AAD:
And the Admin consent permissions on the Graph Explorer:
I think the most important question here is: what causes the files to be invisible through the OneDrive call?
What about Sharepoint rights like these?
I have an app, where I specifically wanted to use Application rights.
It gave me all the files, whereas the one above only gave me the files in my own documents folder.
On the Azure App registration it is like this:
To be honest, I do not think it has something to do with these permissions. The issue is that the user that we are authenticating with is just not seeing the files on OneDrive, it only sees it on Sharepoint. Since CData uses the OneDrive /SharedWithMe call, we cannot see the file in TX. So the question is: why does the file not appear on OneDrive?
FYI: we have another user which has access to the file in the same way and that user can see it on both Sharepoint and OneDrive…
There is no difference between the two users rights, could it be something like the other user opening the files on their computer whereas the other one is not used for anything but authentication.
Not sure what you mean there, we are accessing onedrive.com to check whether we can see the files.
OK, let me know what they say.
You can also try the https://developer.microsoft.com/en-us/graph/graph-explorer and see if you can locate the files through that. Supposedly that is what the CData provider uses behind the scenes.