PBI Gateway Pubic IP and Server Instance Connection

[Karljr]

My SQL Server is on a cloud server. It is not on a domain.  I conect to it via a public IP.  SQL server has allow remote coenection on and I can connect to it with the IP via SSMS.  I wrote a PBI report with a query to this SQL server with PBI desktop.  Datasource connects via public IP. So issues.  Ipublished this report to my PBI service to a workspace. Works fine. 

I added an enterprise gateway on my cloud server, so it can run all the time for data refreshes.  I see it under Manage gateway on PBI service.  

I go to my local machince, PBI service, Manage gateway and add a datasource.  Will not connect using IP.  I use sql server instance name and it connects fine.  

Go to PBI service and dataset says it is being refreshed. I hit manual refesh and it says updated. 

But I click on my report in workspace and it says cannot connect to datasource.  

I tried to update datsource to IP\instance name and will not connect datasource.  

My issue is the public IP and instance name but I canot find any help on this.  

[Karljr]

I figured it out.  Gilbert gave me soem ideas that pointed me in the right direction.  

PBI Desktop  I connected via IP address.

PBI Service Gateway needed to connect to datasource with the server name. 

When I published my report/data source it wanted IP but gateway was wanting name. 

So no connect to gateway. 

I solved by added a host table entry on my laptop that mapped the IP address to the server name.  

PBI desktop then connected so I can develop and test. 

Then publish it to PBI 

PBI gateway then sees server name and refreshed data from gateway.  

[GilbertQ · Accepted solution]

Hi @Karljr 

When configuring the data source it should work via the IP Address as long as your Gateway server can access the SQL Server via the same IP Address.

I would suggest logging into the Gateway server and see how you can connect via the IP Address and which username and password you use.

Then take those same details and create this in the data source in the Power BI Service under manage Gateways.

[Karljr]

My gateway looks good. It is on the same server as the SQL server.  Runs under my O365 login. Not sure what you mean by can it contact the SQL server by IP. It is on that server and shouldplus I don't see in gateway config here where to put that. 

My PBI gateway config is where the issue is I think.  As you can see when I create datasource it wants the instance name of SQL server and connects.  If I put the IP there it will not connect and gives me the message basically saying use the instance name. On my data source it says gateway is not config I guess because my data source was created by IP when I created it on PBI desktop.  

So how do I get the datasource in PBI to use the instance name not IP. PBI desktop that looks like to me I where I am off.   Am I correct? 

Dataset setting - trying to use IP from datasource in my PBI desktop created report

dataset settings

gateway on SQL server looks good to me

gateway on SQL server

gateway on PBI service finds gateway but only connects to my datasource wiht sql server instance name not IP. 

datasource on PBI service gateway