Gateway configuration on external machine

[gvg]

Hi,

I have pbix file that accesses SQL database on external IP (I have configured port forwarding respectfully on that external machine). I then publish pbix file to the Service and the report works fine from there. However I can not set its automatic refresh in the Service. Tried installing Gateway on my machine, but when I want to add datasource (my SQL database) to the Gateway, it does not accept it. I believe it does not like external IP address provided. Would be gratefull for any hints, please.

[v-zhenbw-msft]

Hi @gvg ,

What error do you receive when you add the data source to gateway?

Keep the gateway as close to the data source as possible.

If you're planning to use Windows authentication, make sure you install the gateway on a computer that's a member of the same Active Directory environment as the data sources.

Could you please provide more detail about your error?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gvg]

Hi,
I get the message :
Unable to connect: We encountered an error while trying to connect to . Details: We could not register this data source for any gateway instances within this cluster.
However if I try to check mark "Use SSO via Kerberos for DirectQuery And Import queries" my Gateway says 'Connection successful' but refresh fails with the message 'The on-premises data gateway's service account failed to impersonate the user'.
Why should I keep my gateway as close to the data source as possible? This is just for productivity reasons I understand? If I am happy with long refreshes this should not be a problem?

[gvg · Accepted solution]

OK, it looks like you have to check "Skip Test Connection" and go straight to scheduling the refresh in the workspace. It seems that testing the connection during the dataset setup uses different connection algorithms that fail. Oddly enough scheduling the refresh without testing the connection works fine.

[amitchandak]

@gvg , Please check if the gateway is installed and working properly. On the machine from where you published power bi file

Refer this blog for setting . Scroll down where gateway shows X sign

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

if needed refer Troubleshoot gateway
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot
https://guyinacube.com/2016/09/21/troubleshooting-connectivity-tips/
https://www.youtube.com/watch?v=mGnNn6gnrKk

[gvg]

@amitchandak , well all this documentation deals with a local data base. I need to connect to the database located on external network (external IP). In my pbix file I get connected to that external database with something like this:

= Sql.Database("213.155.456.678:1433", "disXXX", [Query="SELECT ...

In Gateway FAQs I read:

Question: Does the gateway have to be installed on the same machine as the data source?
Answer: No, the gateway connects to the data source by using the provided connection information. In this sense, consider the gateway as a client application. The gateway just needs to connect to the specified server.

Therefore the database may be anywhere in the world I presume.