Cloud data source refresh is not allowed in gateway setting

[lars-kettermann]

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?

[lgaust · Accepted solution]

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.

[lars-kettermann · Accepted solution]

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

[lgaust]

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?

[lars-kettermann]

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

[lgaust]

Lars - you are amazing ; thanks so much!

I reckon that'll be it as I know we couldn't browse Sharepoint from that VM.

Hope you have a great break this holiday season.

Thanks,

Liam

[lars-kettermann]

Thanks! I hope you can do it. Have a nice Christmas season to you and your family!

Greetings,

Lars

[lbendlin]

Don't use the Web connector.  Use the  Sharepoint Folder connector.

[lars-kettermann]

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

[lars-kettermann]

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.

[lars-kettermann]

"What are the privacy levels for these two sources" > Normally organisation, but i have already testet all possible combinations.

"Yours is just an append. in case of merges you ...": What do you mean by merges? Joining instead of appending? From my point of view it can't get worse 😉

What i don't get is, that everything works perfect with exactly the same configurations, but only if i append / mix those source inside the report after importing suddenly "The credentials provided for the SharePoint source are invalid". For me this is completely unlogical ☹️

I'm giving up for now and will send MS a ticket. Thank you for your help and patience!

[lgaust · Accepted solution]

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.