Dataset gateway not configured correctly

[Anonymous]

I want to set up a dataset that has a live data refresh. When data is added to the tables in Sql server, dataset and reports get automaticly refreshed.

What I did:

-I set up on-premises GW on the server which has SQL-server installed. GW is online and working.

-Sql Server and Sql Server Browser is started

-TCP 1433, UDP 1434 ports opened.

-On server I can connect to SQL database in SSMS with database name:  PcName\SQLEXPRESS

-On local machine I can connect to SQL database in SSMS with using "named instance" or DNS name

-in powerbi service in browser, I configured gateway using database name: PcName\SQLEXPRESS. Connection is successful and I get "Online you are good to go" message.

-in powerbi desktop I choose Direct Query and can only connect to data source for SQL server with  "named instance" name for sql server. Connection is successful and I get data from tables.

When I publish report and try to set up gateway for this Direct Query dataset, I get a message:

Screenshot link 

Any idea how could I set up dataset with live data refresh, and fix this problem?

Thanks 🙂

[Anonymous · Accepted solution]

OK so I found a way to fix this problem. What I needed to do is go to server side and open hosts file in c:\windows\system32\drivers\etc\hosts. Insert a new line in there for LOCAL SERVER IP that is resolving to SERVER DNS NAME. After doing that I can connect trough SERVER DNS NAME to SQL Server database on both local machine and remote server. And the connection to PowerBI dataset Is therefore also the same.

[avatorl]

Did you click on that arrow (triangle) button to the right from 'not configured...' message?

[Anonymous]

I get this message:

But I cannot add this data source to GW (error: 40 - Could not open a connection to SQL Server), because i can only use connection with GW to server with  PcName\SQLEXPRESS.

And on PowerBI desktop I can connect with the "instance name, dns server name", the one in screenshot.