Unable to change gateway for dataset

[Anonymous]

Hello,

I'm having trouble selecting a gateway connection for one of my reports. I'm not sure why, as this report uses the same datasource as some other reports which refresh just fine.

I have a few reports published to the Power BI service. Each report connects to a single SQL node (let's call it node1 here) and pulls data from a single database (db1). Each report uses a personal gateway connection to refresh the data. It looks like I have two gateways up and running on my computer (comp1), but I only use the Personal Gateway.

The setup is shown below:

I tried to upload a new report which also pulls its information from db1 on node1. However, after I published the dataset, the gateway connection looked like this:

Gateway for the new connection (which does not work).

I can select the Personal Gateway, however if click "Apply," navigate away from the settings, then come back to this page, the gateway selection reverts to the user1_gateway.

When I expand "Show Details," I see the following message:

Processing error:

Your data gateway is offline or could not be reached. Ensure the computer running the gateway is on during the refresh period.

I only have one gateway installed on comp1, it is up-to-date, always on, and all my other reports can refresh. Any help in fixing this issue would be appreciated. Thank you.

[Anonymous]

For some reason, the first image was removed. This is what the connection settings look like for the functioning reports:

Thank you,
kellymj

[NickSter]

@Anonymous 

When you click managa gateways under actions (cog symbol) you open up the gateway cluster menu. On the left side you will see your gateways. When you click un the gateway you can open up all the datasources added to that cluster.

Are there any errors showing when you select the sources?

Also i looks like you checked the box "skip test connection" on your datasource. Is this on purpose?

[Anonymous]

Hi, thanks for your reply.

Here are screenshots of the gateway cluster and data source settings:

Gateway cluster settingsData source settings

Expanding "Show details" gives the following information:

Client version:

2002.2.018

user1_gateway:

The gateway is either offline or could not be reached.

The version of the gateway installed on comp1 is 3000.27.30, which makes me think the gateway on my machine does not correspond with this gateway cluster.

When I set all this up the first time, I had "skip test connection" checked, and I haven't been able to change it back since. When I uncheck this option and try to apply the settings, I get the following error message:

 Error after unchecking "Skip test credentials"

[venal]

@Anonymous 

Is the Gateway is installed in personal machine or server machine?

If it is the personal machine, try to configure the gateway in server machine and try to re-pro the issue.

If you have any concerns, please let us know.

BR

venal.

[Anonymous]

Hello, thank you for your suggestion.

The personal gateway is installed on my own machine abd I will have to contact the DB admins to configure the gateway on the server.

However, both the personal gateway and gateway cluster are running on my personal machine (as shown in the first image in my original post). I am confused by why I cannot select the personal gateway for this one specific report.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We noticed that your gateway cluster has a warning icon and seems it is offline, could you please try to verify the status of your gateway cluster in "Services" - "On-Premises data gateway", or open the gateway application?

If you cannot select Personal Gateway to use in PowerBI Service, we think maybe you are using Direct Query to connect your data source?Please also refer to this document about difference between two modes of gateway: https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode

Best regards,

[Anonymous]

Hello, thank you for your reply.

Strangely, the gateway doesn't show up in services, but the gateway application (and the PowerBI service) show the gateway is up and running.

I looked at the report again and found one of the queries was using Direct Query. I replaced it with a query which uses Import connectivity mode and the report successfully refreshed. Thank you!

[v-lid-msft]

Hi @Anonymous ,

The On-Premises Data Gateway has two different mode, one is Standard Mode, and another is Personal Mode, both can run in a same machine, but only Standard Mode can support DirectQuery and LiveConnection, each dataset can only use one gateway at same time. The Standard Mode run as a windows service, you can restart/stop it in the windows services manager, or using it's manage application: On-Premises data gateway, the manage application of personal mode is called On-Premises data gateway (personal mode), if you cannot find standard mode application or service in your computer, it mainly because it does not installed in this machine yet.

It should notice if you convert a data source from DirectQuery to Import, we cannot convert it back to DirectQuery unless import this data source again using DirectQuery.

Best regards,

[NickSter]

I have to be honest, i am not sure what is causing this error. You could try to update our gateway (https://powerbi.microsoft.com/en-us/gateway/)

thats somethin i did recently when i had some errors. But i am not sure if this wel resolve your problem. And it could take some fiddeling after upgrading when different datasets are also using this gateway.

[Anonymous]

I appreciate you taking the time to look at it anyways. Have a good one