Reply
Frequent Visitor
Posts: 7
Registered: ‎01-17-2019

Does Desktop Dev need to run on same machine as Gateway? What am I missing?

So, I had been trying to build a dashboard and publish from, let's call it, PC#1.   The dashboard being developed has SQL instance .\SQL2012, databasename "Test".    Dashboard is built, tested, and we publish.

Of course, the web published version can't see PC#1\SQL2012, catalog "Test", and it gives me an error, saying the gateway doesn't have that path.   Couldn't figure out any way to 're-key' the dashboard to the gateway.

But if I go to PC#2 (gateway), instance SQL2016, catalog "Production", and put Power BI desktop there, and publish from there, it gives me the option to add PC#2\SQL2016, catalog "Production" to the gateway server, and everything works.

But that means I have to do all of my dashboard development, testing, and publishing from my production database server???    That seems kind of crazy.   What am I missing?

Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

The connection information used in your "Edit Queries" of your dataset must refer to the exact same path as what your gateway is going to use.

 

A common issue that arises when a person developes a solution on the same machine that hosts the data source is that often your stated Data Source path uses a local path address rather than an address that would be used by another machine.

 

For example, if you had a file on your hard drive, you might use the connection string "C:\Folder\File" where as an outside machine would use "\\Server\Folder\File"

 

My expectation is that you are having a similar issue with your SQL instance.  In your case, i'd be expecting your "Servername" field may current contain something like "Localhost" rather than "ServerAB123"


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Frequent Visitor
Posts: 7
Registered: ‎01-17-2019

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

[ Edited ]

Right, that's the exact problem, as far as I see it- that the development tool makes a literal connection string, and when the gateway runs on a different machine, the published page can't be converted to use the new data source.   It seems to require that the desktop/dev environment run on the same machine as the gateway and SQL server.

 

To be clear, I want to develop dashboards for implementation using gateways on the customer premises (e.g., I'm not even on the network of the final gateway, so even using a UNC path to \\server\resource isn't helpful... ).   It seems there should be some way to 're-key' the data source connection string after creating the dashboard, rather than it being hard-coded into the dashboard in Step One.

Moderator
Posts: 9,900
Registered: ‎03-10-2016

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

@sdrevik,


You don't have to run Power BI Desktop, Power BI gateway and SQL Server on a same machine. Just take the following points into consideration.

1. Make sure that you are able to access SQL Server database from the machines that installing Power BI Desktop and Power BI gateway. You can use SQL Server Management Studio to test the connection.

2. Ensure that the server name and database name you provide in Power BI Desktop and Power BI gateway are exact same, for more details, see https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-sql.

3. After adding SQL Server data source within Power BI gateway, you can modify the server name and database name in Power BI Desktop to match via Edit Queries->Data Source Settings-> Change data source option.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎01-17-2019

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

That still involves the customer exposing their SQL Server on the internet for Step 1 to be successful.   Most IT folks are going to give live birth to a cow when I ask them to do that.

 

Since most IT environments have the server name as the machine name (e.g., "DELL89234\SQL2012"), Step 2 requires that I temporarily change my development machine name to match their server.      That shouldn't be necessary either.

 

That being said, I did try this which SHOULD work, but doesn't (why?):
1.   Publish and get error about gateway

2.  Click link to   "open PBIX on powerbi"

3.  Open Datasets, select Dataset Settings for that published item.
4.  Expand Gateway connection, see message "Not Configured Correctly"

5.  Click right arrow, see connection string from old machine.
6.  Click "Add to Gateway"

7.  I am given the opportunity to put in new credentials for the gateway.  Looks good so far.  Put in good crednetials for gateway (same database, same data, I just backed up from dev machine and restored to gateway machine).
8.  It accepts it but.. nothing happens.
9.  Select Dataset->(Dataset name)->Refresh Now.    Indicator on far right just spins and spins and spins.
10.  Try to refresh the report... nothing.   Tried re-opening the report.   Nothing.

It's just stuck.   It LOOKS like I was able to change the data source.  It just doesn't work.

Frequent Visitor
Posts: 7
Registered: ‎01-17-2019

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

[ Edited ]

I tried to use your third step (Change Data Source) in desktop, that didn't seem to change it for the dashboard I was developing, but I went through the steps anyway, hoping it would add it to list for a new dashboard I could add.  (Is that the intent?  That we could use the gateway for a data connection to develop against?)

When I started a new dashboard, I was able to go to Get Data->Power BI Data Sets, and see a dataset from the failed publish (per the last post).  I selected it and got the error below.   It kept popping up the error no matter how many times I hit "cancel", so I had to hard kill the Power BI Desktop app process.   So, that doesn't appear to be the right way either.

 

Feedback Type: Frown (Error) Error Message:
An error occurred while loading the model for the item or data source ''. Verify that the connection information is correct and that you have permissions to access the data source.

 

Moderator
Posts: 9,900
Registered: ‎03-10-2016

Re: Does Desktop Dev need to run on same machine as Gateway? What am I missing?

@sdrevik ,

Does your dataset only contain SQL Server data source? Could you please share the data source settings in your PBIX file? Only when you add all the data sources within gateway, and the connection strings of the data sources are same in gateway and Power BI Desktop, you are able to use gateway to refresh the dataset.

In addition, do you connect to Power BI Service dataset in Power BI Desktop? Do you have appropriate permission on this dataset?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.