cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

Hi @edhans

 

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

 

image.png

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"

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

I'm not sure enabling this would have an effect on on-prem Teradata.

 

I have to manually refresh 10+ times before it finally works:

2019-02-15_08h45_50.png

edhans New Contributor
New Contributor

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

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 New Contributor
New Contributor

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

gateway failure.jpg

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.

Super User
Super User

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

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.
Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
edhans New Contributor
New Contributor

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

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. 

Highlighted
edhans New Contributor
New Contributor

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

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 Member
Member

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

@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.

 

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

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

Super User
Super User

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

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.
Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"