Fixing error in refresh (Dataflow consuming through SqlServer and SharePoint)

[ovonel]

I am trying to refresh a dataflow that has  3 entities.

When trying to refresh; it errors... By checking the refresh history I see the 3 entities failing with:

Error: The current operation was canceled because one or more operations failed... RootActivityId = … Request ID: ...

Error: The current operation was canceled because one or more operations failed... RootActivityId = … Request ID: ...

Error: Encountered user gateway exception: '[DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.CredentialError.DataSourceKind]=SQL [Microsoft.Data.Mashup.CredentialError.DataSourcePath]=XYZ123

So, per the error I assume there shall be a problem with the gateway connecting to SQL (that’s what the error actually says). But when I check the settings, it “errors” to SharePoint, not SQL:

I am confused, why the error points to SQL, but settings point to SharePoint? How can I fix this? Should I add Sharepoint to the Gateway? (I also don’t understand why there is a ‘data source credentials’ section, I thought it was managed through the gateway…

I also see this.. is the fix providing credentials to SharePoint through here?

On the other hand if the error is in SqlServer level? how can I fix it? Do I need higher permission or does the Gateway needs higher permissions in SqlServer?

[v-jingzhang · Accepted solution]

Hi @ovonel 

I think the error is probably caused by SqlServer data source rather than SharePoint data source. Usually you don't need to add a SharePoint online data source to the gateway as Power BI service can connect to SharePoint cloud service directly. Based on my test, although it shows X beside the SharePoint data source (because I don't add it to the gateway), I can still refresh the dataflow correctly.

The credentials for data sources added to the gateway are managed on Manage gateways page, while credentials for data sources not added to gateway are managed on dataflow settings page (the data source credentials section you see).

So I suggest you go to Manage gateways page (by clicking the gear icon) and check whether the gateway is connected to the SqlServer data source successfully. If not, it will show error message there. You can then troubleshoot according to the error message.

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.