Forum Discussion
Does Desktop Dev need to run on same machine as Gateway? 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.
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
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.