On Premises Gateway can't access the on-premises data source

[Anonymous]

I have been creating dataset combining data from Teradata, and files stored on SharePoint and OneDrive. For some of these datasets, the scheduled refresh via the On Premises gateway (which is currently running on my PC) works fine, but for others it worked fine for a week or so on scheduled refresh, but then started to always fail with the message;

Last refresh failed: Thu Mar 08 2018 15:52:05 GMT+0000 (GMT Standard Time)
We reached the data gateway, but the gateway can't access the on-premises data source.Hide details

Cluster URI:	WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:	ae324436-bb45-78da-f1a0-a9a742ad95b0
Request ID:	88a5239b-51aa-3771-b9b6-9520bc6afc1c
Time:	2018-03-08 15:52:05Z

I'm using Power BI Desktop Version: 2.55.5010.641 32-bit and 64 bit (February 2018)

On-Premises gateway is 14.16.6614.5

I've tried re-creating the datasets from scratch, and at first everything works fine, but as soon as I add substantial data from SharePoint or OneDrive, the update fails.

I can refresh the data using the Desktop without problem.

[roselilly23]

I was getting the same error message when trying to mashup data from azure sql datawarehouse with on prem sql server.

To solve my issue I had to:

• Update our enterprise gateway to the latest version
• Check the box "Allow user's cloud data sources to refresh through this gateway cluster. These cloud data sources do not need to be configured under this gateway cluster".
• Ensure the privacy settings for both datasources was set to "Organizational"

Prior to ticking the box it was saying my gateway was not set up properly and was suggesting that I add the azure sql datawarehouse to the gateway (which you can't do).

[GilbertQ]

Hi there

That is 100% correct because one is cloud and the other source is on-prem.

You have to make sure that the Gateway is configured to be able to access both sources before you can configure the scheduled refresh.

[jeffshieldsdev]

Same intermittent error for me.  Also using Teradata.

I'm aliasing my Teradata server using the HOSTS file, but all gateways in my cluster have the same HOSTS file.

[v-jiascu-msft]

Hi @Anonymous,

Did you update the credentials in the settings of datasets? Can your computer access the outside sources? The error message seems clear.

Best Regards,

Dale

[Anonymous]

Yes, I always check the credentials, and the computer can access the on premise datasources and the web/cloud ones too. When I treid rebuilding the dataset from scratch and using the gateway to update the dataset periodically as I went, the scheduled update would work properly, updating numerous times using the personal mode gateway, and then will cease to function at all, even if I then remove some of the queries I have added. I have tried deleteing the dataset and re-publishing it. 

[GilbertQ]

hi @Anonymous

Have you tried installing the On-Premise Data Gateway, but selecting the Enterprise Gateway Option to see if that works?

There has been a recent update where it now allows sources from On-Premise and Cloud

https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-february-update-is-now-available/

[Anonymous]

I have been using both on-premises gateway, and the personal mode version and both with success until now. I have both running concurrently on my PC as we have not rolled this product out to the live/production environment.

I'm not sure what you mean by the enterprise version. Is this a third flavour?

Just to empahsise, I have many other datasets which are successuly updating/refreshing using either of my two gateways, combining data from Teradata and SharePoint. This latest datset also used to update perfectly well, but as we added more and more queries it suddenly broke, and will not revert to successful updating when we remove queries.

[GilbertQ]

Hi there,

The two options when installing the On-Premise Data Gateway is the On-Premise Gateway (Previously Enterprise) or Personal Gateway.

Could it possibly be that when adding more sources it is timing out because it is taking too long to complete?

Also can you confirm you have installed the Feb 2018 version of the On-Premise Data Gateway?

[Anonymous]

I have been using both, but yesterday reinstalled the on-premises gateway (so latest version 14.16.6614.5). With other datasets, the refresh works fine both with our on premises Teradata database and when combining that with data from the cloud.

I don't think it is a timeout problem, as a different error is shown when that occurs, and also this failure is occuring variably between a few seconds and nearly a minute.

[edhans]

FWIW, same problem here. New report that combines SQL via an Enterprise Gateway cluster and a file on Sharepoint that is mashed up in Power Query.

It works fine on both the Sept 2018 and Oct 2018 desktop, but almost always fails within 2-3 seconds of starting. "We reached the data gateway, but the gateway can't access the on-premises data source."

The gateway cluster is fine. Dozens of other reports are working. I have validated my credentials to the Sharepoint server as well in the refresh properties for the dataset.

I've tried to run it 10-12 times. It has worked 2 times, and failed the rest. Seems to be a bug.

I currently have the Sept gateways installed. I don't think October's has been released yet.

[edhans]

Does anyone have this working where they can Combine Files (XLSX) from a Sharepoint Online library (Office 365) and mash it up against an on prem SQL Server? 

I've tried no privacy settings for both & organizational for both. This refreshes just fine on my PC in the desktop. This is just the service failing.

This fails 100% of the time for me. It fails instantly when I try to refresh. As soon as I change the file to not load that query into the Power BI DAX model, the report runs fine. I have the December 2018 Gateway in Enterprise mode.

[GilbertQ]

Hi @edhans

Have you enabled the following on your Gateway in the Power BI Service?

[edhans]

Yes. For grins I tried without it and it woudn't even let me configure the scheduled refresh because it said the gateway cluster didn't have access to that resource - or something to that effect. So yes, it is checked. It just will not work. It isn't even trying to work. It fails within 2-3 seconds.

[edhans]

The completed refresh is when I disabled load for the mashed up (Sharepoint Online + On Prem SQL) query. The "there is available gateway" is when I had the box unchecked about allowing this cluster to refresh cloud data.

[GilbertQ]

Hi there,

What happens if you just try with SharePoint?

And then if you can test on the On-Premise Data Gateway that the server can connect to the SharePoint server.

[edhans]

Just sharepoint works. Just On-Prem works. I can have both in the same report, but as soon as I merge/combine them in Power Query, it fails. If I keep them as separate queries, the load them separately, I can then join them in DAX, but that doesn't quite do what I need it to do.

Yes, the machine(s) the Gateway nodes are running on all have access to the Sharepoint site, and I've authenticated into the Sharepoint site. Again:

• Sharepoint Queries refresh fine if loaded separately.
• On Prem SQL data refreshes fine if loaded separately.
• Merge the queries in power query, and it fails within 2-5 seconds saying it reached the gateway, but cannot access the on-prem server, which is absurd as all of the on-prem queries work fine until I merge them with the Sharepoint stuff.

Right now my horrible horrible workaround is to load these tables in the new DataFlows and them mashup sharepoint and dataflows, but that is just awful.

Any ideas? I am wondering if there is some config deep in our tenant that is causing this. It was first set up in 2015. 

[Shelley]

@edhans  Any luck with this? I am having the same problem. I have lots of reports using data from our SQL server AND Excel files on Sharepoint and OneDrive, and they work just fine, refreshing over an Enterprise Gateway. However, this is the first report where I try to merge queries, looking up values in a SQL table onto the Excel tables, and now it will not refresh no matter what I try. Although oddly, I did have one random refresh on demand work in 6 minutes. It refreshes on my Desktop in 5 minutes. I too have a ticket in with Microsoft.

[GilbertQ]

Hi there

What I would suggest is to make sure for each data source that you have set it with the right Privacy Level settings. They both have to be the same in order for them to connect and merge correctly.

[Shelley]

@GilbertQ thanks for the idea. In the pbix file on my desktop, I went into Data Source Settings and set all sources (SQL, OneDrive, SharePoint) to Privacy of Organizational Account. Is this what you mean? I then published and the refresh again failed with the same error. "We reached the data gateway, but the gateway can't access the on-premises data source."

I then went into options and settings > options > privacy in the pbix file and clicked always ignore privacy level settings. Refresh again failed with the same error above.

Please let me know if I'm missing something in your suggestion. Thanks!

[jeffshieldsdev]

FYI, I also have an open support ticket regarding this error connecting to Teradata.