Forum Discussion
Cloud data source refresh is not allowed in gateway setting
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
Super UserDon'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
From my point of view, the SharePoint is already assigned to the cloud connection, as you can see from the "B"-mark with the green tick? There is no dropdown in the row of the "A"-mark for any mappings, but it doesn't seem to have a negative effect either:
I already mentioned that I got exactly the same report with exactly the same settings to work as soon as I no longer append the SQL and SharePoint-XLSX-data within the report after(!) the import. In the working version A, B and the rest looks exactly(!) the same in the working version of the report.Here an example what it looks like in the (after upload) not working version:
As soon as i delete the table "XLS_SQL" the report works with no problem.
If I just misunderstood your answer, it would be helpful to go into a little more detail 😊Thank you!
- lbendlin
The feeling is mutual. Hard to help if nothing is really visible.
Whenever you attempt to combine data from different sources you wake up the firewall dragon. What are the privacy levels for these two sources?
(Yours is "just" an append. in case of merges you actually need to create an actual gateway connection for your SharePoint source (!!!) and that is an irreversible step - the sharepoint source will continue to need that gateway connection for all eternity even if you remove your SQL data source)
Show your gateway config.