Issues with Power BI Gateway Configuration for DirectQuery

[TherueBG]

Hi everyone,

I am facing some challenges with my Power BI setup and would appreciate any help or guidance. About 4-5 people have taken a look at this so far and no one has been able to solve it

Background

I am trying to set up a Power BI environment where the Power BI Online Service connects to a machine in our data center(Not on site, its managed by an external company and therefore not on the same network). Here are the steps I have taken so far:

1. Installed On-Premises Data Gateway:

   • The gateway is installed on the same machine as the database.
   • Installed a gateway on my laptop for testing. Laptop is on another network to the server
2. Tested the connection
   • I can connect to the SQL database fine from my laptop and in the PowerBI Desktop environment
   • When I add this to the PowerBI Service it will connect via the gateway hosted on my laptop. This is trying to connect via the server's external IP as that is what I connect through on my laptop
   • as long as the gateway on my laptop is running this functions as expected (Not a long term solution)
3. Using the gateway installed on the server
   • The second gateway is installed on the server that the SQL database is hosted on
   • Both Gateways show online in the PowerBI Service
   • When uploading to the Powerbi Service the data source will always be the external IP as that's what I'm connecting to in powerbi desktop  and will connect through my laptops gateway as that is the one with the external IP as a source
     
4. Issues when swapping the gateway
   • if I try to swap the gateway in the power bi service from the one on my laptop to the one on the server i see the following and cannot select the option for the server side gateway 
     

     If i try to add that IP to the gateway it fails, to connect

   • i can get it to connect by adding localhost as the server IP but this doesn't reflect the IP from the desktop version so wont allow me to select it in the drop-down

5. Work Arounds Tried
   Dataflows: Can connect via local host through the gateway, but can't use direct query which is a must for our requirement
   LocalHost: Works to connect, but can't be linked to the model as doesn't match the connection information from the desktop
   Connecting via local host in powerbi desktop: Ive tried changing the source to localhost or the servers internal IP but cant save this in the report as my laptop won't connect to the server to pass authentication.
   
   Any suggestions or workarounds like how to change the data source in powerbi service would be fantastic!
   
   
   Any help with this would be much appreciated as this has a lot of people stumped. I feel like we are missing something obvious but I'm unsure. I know we could just run the gateway on an azure machine like we are on my laptop, but Microsoft support have said this should function fine running on the same server as the SQL database
   
   
   

[GilbertQ]

Hi @TherueBG 

What sometimes happens when you're trying to connect to a local sql server, you can't connect to the ip address Because it's not connecting back to itself and that is why I failed. What suggest just trying the app address of 127.0.0.1 and see if that works.