Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dataset gateway not configured correctly

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 🙂

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • avatorl's avatar
    avatorl
    Impactful Individual
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.