Solved: Dataset gateway not configured correctly

Anonymous · ‎02-09-2021

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 · ‎02-10-2021

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.

View solution in original post

Anonymous · ‎02-10-2021

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 · ‎02-09-2021

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

Anonymous · ‎02-09-2021

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.

Dataset gateway not configured correctly

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024