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
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.
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,
LydiaLydia, thanks for responding. Yes, a SQL Server is the only kind of data source. When I started developing the dashboard, I connected to the local database, and then when I published it somewhat automatically found the gateway on the same machine I had set up. But if I develop on that machine (call it Machine A) with a copy (Copy A) of the database, I can't find a way to change the connection to an identical schema database (Copy B on Machine B). I went through the steps mentioned above, which would seem the way to do it, but it didn't work.
With respect to the data settings in the PBIX file, the data source settings in Power BI desktop (the 'development' environment) still show the local SQL server. The options just seem to be to set to another local machine- I tried putting in the IP address of a foreign SQL server that had port 1433 exposed (e.g., I can log in with 'sa' via SSMS), and it went to a second page asking for credentials, but when I put in the sa credentials, it said it couldn't log in (even though SSMS worked). Note that machine didn't have a gateway installed, I was just trying to go direct to its SQL server for design just like my local.
At first, it just said it couldn't log in, then when I tried again, it suggested to confirm it's set up for remote connections (it is).Is there a special way to specify a known gateway when starting with "Get Data" in Dekstop?
Thanks again,
Steve
Wait, I think I got it:
1. Get data
2. Enter database server IP and database name
3. Get to 2nd screen, SELECT "DATATBASE" ON LEFT SIDE, then put in sa or database credentials (this is what I missed! It was still on "Windows" on the left side.
4. Accept warning about encryption.
5. Connected! Woot woot!
Now the next step is to install gateway on that server, develop, publish, and see if it finds the gateway. Sounds like I'm on the right path?
But if there's a good way to connect to an existing gateway from desktop for the initial design phase, that would be even better.