Forum Discussion
Unable to schedule refresh with sharepoint and database
I heard back from Microsoft that...
- Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway.
- As mentioned here, you can use a Personal Gateway to set up a scheduled refresh of a dataset that has both SharePoint Online and on-premise data sources. This is what they recommended.
- Microsoft is investigating the possibility of a change to allow OAuth with the On-Premises Gateway, but have have no timelines for it.
- They recommended that I continue to to use the On-Premises Gateway for datasets where it works, but use the Personal Gateway as a "workaround" for my one dataset that has both SharePoint Online and on-premise data sources
- Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway.
kevhav, yes, same issue, thanks for wording it a bit better.
I have set up a Personal Gateway for now, still hoping to make the "On-Premises Gateway" work though.
I heard back from Microsoft that...
- Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway.
- As mentioned here, you can use a Personal Gateway to set up a scheduled refresh of a dataset that has both SharePoint Online and on-premise data sources. This is what they recommended.
- Microsoft is investigating the possibility of a change to allow OAuth with the On-Premises Gateway, but have have no timelines for it.
- They recommended that I continue to to use the On-Premises Gateway for datasets where it works, but use the Personal Gateway as a "workaround" for my one dataset that has both SharePoint Online and on-premise data sources
I am also affected by this issue, as kevhav states:
"Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway."
Our use case is that we have a sharepoint folder with shared access where users can upload their own data and then integrate it with on-prem DW data. Without the integration in the on-prem gateway, it seems we don't have a way to automate this use case.
Is there a request in the feature backlog that I can vote for? It seems OAuth should be an important part of the on-prem gateway featureset, or if not, we should not recommend users to share their files in OneDrive/SharePoint.
Using the "Personal Gateway" worked for me. Be sure to note the "Personal Gateway" documentation regarding installing it as an admin.
It's a bit annoying that now I have both an "On-Premises Gateway" and a "Personal Gateway." They work a bit differently. But at least it works to automatically refresh my dataset whose data sources include both on-premise DBs and SharePoint Online files.
Microsoft had said they might add this capability to the "On-Premises Gateway" in the future. If that does ever happen, then I'll be happy to go back to having a single gateway.
