SQL Server Data Gateway Offline

[cnormand127]

Hi,

We have been having that issue for a long time now. We have setupa data gateway in power BI that links to our SQL server (no personal) and 50% of the time the gateway status is set to offline so scheduled refresh fail to execute.

This is the error message detail:

Status code: 400
Error Code: DM_GWPipeline_Gateway_CancellationOrTimeoutError
Time: Mon Jul 03 2017 10:56:37 GMT-0400 (Eastern Daylight Time)
Version: 13.0.1781.239
Thank you,Our SQL server is live and there is no problem to connect to it. Could someone please let me know why are those gateway are offline and what can be done to have them online at all times? Also is there any support provided by Microsoft regarding powerbi? The forum are the only place I could find to ask for help....

Chris

[v-yuezhe-msft]

@cnormand127,

Firstly, please make sure that you installed the gateway on a computer that is always turned on and connected to the Internet. And please check if there are any issues about the network between the machine that installed gateway and the machine that installed SQL Server.

Secondly, go to Services and restart your gateway service.

Thirdly, do you use "import" mode to connect to SQL Server in Power BI Desktop? Do you import large amount data from SQL Server and define a complex data model in Power BI?



Regards,

[cnormand127]

Hi Thanks you for your response. I do not use my own gateway (PowerBI - Personal) but I use a direct Data gateway to our SQL server. I thought this wouldn't go thru my desktop data gateway, would it? 

In regards to PowerBI Desktop, yes, I use Import. Our queries are not that complex and can return up to 500,000 records. Should I use DirectQuery?

Thank you,

Chris

[v-yuezhe-msft]

@cnormand127,

What do you mean by "direct data gateway"? Do you mean "On-premise gateway"? And what do you mean by "desktop data gateway"?

Besides, your dataset is not large, you don't need to use DirectQuery.

Regards,

[cnormand127]

In powerBI online, when you select a dataset and go to settings, You select gateway, you have two choices:

- Use Your Data Gateway (Power BI - Personal)

- Use a Data Gateway

I selected the second one, not the first one. Then if I go to Settings > Manage Gateway. The SQL Server gateway fails and the error message is : 

Cluster URI: https://wabi-us-east2-redirect.analysis.windows.net
Status code: 400
Error Code: DM_GWPipeline_Gateway_CancellationOrTimeoutError
Time: Wed Jul 05 2017 11:10:55 GMT-0400 (Eastern Daylight Time)
Version: 13.0.1781.239

My reports couldn't refresh for the last 5 days and it's happening a lot.... 

[v-yuezhe-msft]

@cnormand127,

Could you please restart the On-Premise gateway Services?

Also make sure that you installed the gateway on a computer that is always turned on and connected to the Internet. And please check if there are any issues about the network between the machine that installed gateway and the machine that installed SQL Server.

Regards,

[cnormand127]

I did restart the gateway service but it doesn't change anything. the only way for me to update the online reports is to open PowerBI Desktop, refresh the dataset and publish again....

What I don't understand is that in the gateway setting of the dataset, it seems that the data are pulled from my SQL server directly and are not going thru my local personal gateway (see image below)

I thought only the first radio button would force it to use the local gateway but from what I understand, the second one  (Use a Data gateway) was a direct connection (created in Settings > Manage Gateway)

[v-yuezhe-msft]

@cnormand127,

Do you use tha latest version of on-premises gateway? How about you install personal gateway and use it to refresh your dataset?

Regards,

[cnormand127]

Yes I use the latest version but once again with my setting, I do not think I'm going thru my personal gateway. It appears to be in direct connection to my SQL server. Can someone confirm that? This is really confusing, nothing indicates how both option are used... 

I rather use direct connection to avoid going thru another PC, avoiding then connection problem or another level of problem. But at this time, I'm not sure what to do, I have to publish the reports everyday to refresh them which is pretty crazy.... I guess I'll try other solution

[v-yuezhe-msft]

@cnormand127,

Please review this article about how gateway connects to your on-premises SQL Server. In your scenario, I would recommend you check if you can use personal gateway to refresh the dataset.

Regards,
Lydia

[cnormand127]

I did review the article but this doesn't help. I tried everything (personal gateway, gateway) but nothing works. Only way to refresh my reports is to publish it again.... so it's pretty painfull to do it everyday.

the personal gateway always shows offline. but it is connected and when I run a diagnostic test connection, it's successfull! So I really don't know what's wrong here...

[v-yuezhe-msft]

@cnormand127,

A similar issue about offline gateway has been reported internally, I will update here once I get feedback.

Regards,

[cnormand127]

Hi Is there any news on that? All gateway are offline and I'm tired of publishing all my reports everyday to get them refreshed. Also is that the only support available for powerBI? I certainly will be willing to pay for that service if it was working and support was available.... But at this point, my reports are just not refreshing and it's very painfull.

Hope I can hear some good news soon

[v-yuezhe-msft]

@cnormand127,

Please install the July release of the On-Premises Data Gateway and check if the issue still persists.

Regards,
Lydia

[cnormand127]

Does not work....

And once again I do not use on premise gateway, I'm using direct gateway to our SQL server located on AZURE!!!!!

I guess this will never be solved... And without support.... even harder to resolve.

Very dissapointed

[v-yuezhe-msft]

@cnormand127,

You still get the error when using the July release of gateway to refresh your dataset, right? If so, I would recommend you open a support ticket on the support.powerbi.com site.


Regards,

[cnormand127]

Well, Your website won't let me contact you. I am on the power BI pro TRIAL and when trying to access the support it says:

"Looks like you haven't subscribed to Power BI Pro"

So basically I can't contact you! But I am a pro user who's on the trial and I would definitly pay if it was working but I just can't create a support ticket or contact you to fix my issue....

It's a vicious circle