Remote Desktop SQL Server Connection

[Ows]

Hello

I used to have on premises on my personal laptop but now I install it to our Remote Desktop where our SQL Database is.Because I want reports to be live all the time.Thats why I upgrade my account as well.

So here my problem;
My report on the desktop uses xxx.xxx.xxx.xxx server and APSData_2024 database

And I "enablefolding" to incremental refresh and hit  Get the Latest Data in "Real Time with DirectQuery"

When I try to publish my report I get error "Your file was published,but disconnected" So I go to web PowerBI and saw my SQL Gateway there.But It was giving error so I watched dozens of videos and add data source there.With;

Server yyy.yy.yy (to go to sql I also tried xxx.xxx.xxx.xxx but it didnt work)
database APSData_2024
authentication windows and my username and pass. This was successful.

And under connections tab I can see my latest source there.But still its not working. And I enable "allow user's cloud data sources to refresh through this gateway cluster"

Still not working I hope I can get some help here 😥

[Anonymous]

Hi @Ows ,

Did @AUDISU  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[AUDISU · Accepted solution]

Hi @Ows ,

You can not connect data getway untill you have the same server name in power bi report and Remote Desktop server.

Solution is create a parameter in your power bi report and store sql server (Personal Computer) in that parameter. Once you publish the reprt in the server change parameter values to Remote Desktop server.
Then it shows your getway to connect.

Thanks

[Ows]

Yes that worked thanks for that. @lbendlin Also thank you a lot for your help

[Ows]

I edit the blackouts I appreciate your efforts. 

[lbendlin]

switch the "Use an On-premise or VNet data gateway" toggle on.

[Ows]

I think I can be more clear with pictures.

[lbendlin]

You have not added the data source connection to the gateway yet. Note that the connection details must match between Desktop and Gateway.

[Ows]

how can I add datasource to the gateway?

[lbendlin]

Click the "Add to gateway"  link.

[Ows]

Okay it leads me here and I already done that

[lbendlin]

Go back to the Semantic Model settings and map that connection in the gateway section.

[Ows]

Yes unfortunately I still have the same error here  

Please correct me if I am wrong in this picture(SQLServer{"server":"IP Adress"......) IP address is same with the IP adress that I create report and where GW is installed.

But for add to gateway page I insert my local IP address

[lbendlin]

Both in the desktop and on the gateway connection management you should ideally specify the FQDN of the SQL server, not the IP address.

Did you add the connection to the "Power BI GW"  gateway?

All your blackouts are not helping with the troubleshooting.

[Ows]

Okay so one last question can I still use my desktop to PUBLISH reports or should I also PUBLISH reports from VM where gateway is?

[lbendlin]

You continue to use Power BI Desktop on your PC for that.   You can install Power BI Desktop on a gateway VM but that is not required in regular operations, only for troubleshooting  (and it eats 2.5 GB of disk space that you will rather want to give to the gateway log files).

[lbendlin]

but now I install it to our Remote Desktop where our SQL Database is

Don't do that.  Enterprise Gateway should be installed on its own VM.  

After publishing to the workspace, go to the Semantic Model settings page and rewire the gateway connection.

[Ows]

So you mean that I need to uninstall gateway from terminal server and install it to own server.
Can you inform me why we cant get the data from terminal server?

[lbendlin]

Gateway is extremely resource hogging and will starve all other apps/services of Compute and Memory.  As it should - that's what it is designed for.