Forum Discussion
Cloud data source refresh is not allowed in gateway setting
Hi everyone!
I have designed a report, which gets data from out sql-server via gateway and the report also includes two XLS-Files from our sharepoint, which get loaded with ...
= Excel.Workbook(Web.Contents("\\SERVER\\ABC.xlsx"), null, true)
Updating the report from these sources works perfectly fine and looks like this in the settigs of the semantic model:
I created a new version of the report, which is exactly the same, but i made one change: I append the data loaded from the sql-server with the xls-data. After that i couldn't update the report anymore and i got the message "Cloud data source refresh is not allowed in gateway setting". Our admin fixed that after reading this: CLICK
He activated "Allow user's cloud data sources to refresh through this gateway cluster", but now i get a new error:
"DM_GWPipeline_Gateway_InvalidConnectionCredentials" / "The credentials provided for the Web source are invalid"
We don't understand that, because in the last version of the report (with no appending / joining) we use exactly the same credentials. Can someone explain this?
Any chance you got a response back from MS on this or figured out what the issue was?
I have a feeling my environment has been opted into the Gateway for Sharepoint mentioned above and I can't for the life of me get it to authenticate after the latest Gateway update.
Yes, we talked to Microsoft about it. And we solved it together!
I don't know the architecture on MS, but the solution is a bit strange (for me):
As soon as data from the gateway (internal SQL DB) and data from the SharePoint / cloud connection are combined within the report, the gateway needs a firewall activation to the cloud connection, in our case to our SharePoint. After that, it works without any problems.Why it has also worked before without this firewall activation (only if you don't mix the data in the report) remains a mystery to me about the MS architecture.
And: The fact that the red cross can still be seen in the screenshots gateway section is normal, according to MS.
Case closed 😀
Greetings,
Lars
The support has sent these infos to us:
#######################################
- The gateway server is unable to access the Sharepoint site, which is a requirement for this functionality. Is is enough to open the connection from the gateway server to the Sharepoint URL via TCP 443.
- Please find the following documentation about communication settings that are required for the gateway to work correctly.
Adjust communication settings for the on-premises data gateway | Microsoft Learn
Specifically see this section outlining endpoints that are essential for the gateway to function correctly (including refrence to login.microsoft.com):
- You can carry out a network ports test on the gateway server (described above) to check what it does / does not have access to.
#######################################
After that it works. Hope it helps!
Greetings,
Lars
15 Replies
Yes, we talked to Microsoft about it. And we solved it together!
I don't know the architecture on MS, but the solution is a bit strange (for me):
As soon as data from the gateway (internal SQL DB) and data from the SharePoint / cloud connection are combined within the report, the gateway needs a firewall activation to the cloud connection, in our case to our SharePoint. After that, it works without any problems.Why it has also worked before without this firewall activation (only if you don't mix the data in the report) remains a mystery to me about the MS architecture.
And: The fact that the red cross can still be seen in the screenshots gateway section is normal, according to MS.
Case closed 😀
Greetings,
Lars
- BIBB
Advocate I
Hey lars-kettermann, the issue you face is a bit obscure, and it concerns how the mash-up engine works when you publish a semantic model into the service.
If you combine sources in a query, and one requires a Gateway, then all connections in that query must go through the same Gateway. The explanation can be found in the "data exfiltration" data flow documentation:
"If a dataflow needs to access a network isolated data store (and is therefore bound to a gateway), all data access is then required to flow through the gateway."
https://learn.microsoft.com/en-us/power-query/dataflows/data-exfiltration-best-practicesAlthough the document is about dataflows, this is how the mashup engine behaves for any power query program, no matter if this comes from a PBI semantic model.
I hope this helps! Great to hear thanks Lars, sounds like what I will need to do.
Can you recall what firewall rule (destination etc) you needed to put in or any docco you might have followed?
Or was it just the Sharepoint root site?
The support has sent these infos to us:
#######################################
- The gateway server is unable to access the Sharepoint site, which is a requirement for this functionality. Is is enough to open the connection from the gateway server to the Sharepoint URL via TCP 443.
- Please find the following documentation about communication settings that are required for the gateway to work correctly.
Adjust communication settings for the on-premises data gateway | Microsoft Learn
Specifically see this section outlining endpoints that are essential for the gateway to function correctly (including refrence to login.microsoft.com):
- You can carry out a network ports test on the gateway server (described above) to check what it does / does not have access to.
#######################################
After that it works. Hope it helps!
Greetings,
Lars
You just had to check these two options in the power BI section on you gateway settings !
- lbendlin
Super User
Don't use the Web connector. Use the Sharepoint Folder connector.
With the Sharepoint-folder-connector ...
= SharePoint.Files("https://mysharepoint.xyz/sites/MyShareThere/", [ApiVersion = 15])... it looks like in this screenshot:
Import settings
Unfortunately, that doesn't work either. Same error pattern. Important: As a test, I removed the table "XLS_SQL" from the report that makes the mixture of SQL and XLS after the import. After deleting that table, it works again immediately without any changes and the same connection credentials.
Maybe this detailed error description will help?
{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the SharePoint source are invalid. (Source at https://XXX.yy/sites/MYSHARE.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginPath","detail":{"type":1,"value":"https://XXX.yy/sites/MYSHARE"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"https://XXX.yy/sites/MYSHARE"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"SharePoint\",\"path\":\"https://XXX.yy/sites/MYSHARE\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}} Table: XLS_SQL.So the problem only arises when i mix the cloud data (XLS / Sharepoint) with the gateway data (SQL).
- lbendlin
map the sharepoint to the cloud connection
