Forum Discussion
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?
So here's basically how the gateways work, and why mine didn't work.
You develop in desktop by first connecting to a server, in my case on a different machine, on a different network, but reachable by an IP/port, e.g, 50.60.70.80,9999, database name, and SQL credentials.
When I tried to publish to a web server also hosting a gateway, PBI would give the gateway those credentials (50.60.70.80,9999, DB, and SQL credentials). however, due to the firewall port forwarding, the gateway couldn't connect locally to that SQL database.
BUT, when I used another machine as the gateway (foreign to the SQL server), where that connection string could work, when publishing, PBI sends the credentials to the gateway, gateway says, "sure, I can connect to that", and pop! Worked easily.
(I had also explicitly added those credentials in apps.powerbi.com as part of my debugging to make a new data source on that gateway, so that may be a necessary prerequisite).
I hope this helps somebody in the future.
11 Replies
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"
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.
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
