PowerBI gateway

[charlesbatista2]

Hello, guys. Here's the issue: 

I have a database in a server where I have SQL server and MSSM installed, I don't have PowerBI Desktop installed in this server. 

I'm working on my PowerBI desktop which is installed in my PC at home. I installed a gateway in the server I mentioned previously and what I want is: create a report in my local PC, publish on PowerBI service and then set the dataset to request the updates from the server. Is that possible? Because the scenario I find is that when I publish a report, it goes with the origin path that comes from my local database which is exactly what I don't want. 

[lbendlin]

 I installed a gateway in the server I mentioned previously

Please do not install a gateway on a machine that is already running something else. Gateways need dedicated machines.

In your Power BI report connect to the SQL database on that SQL server (not to your local database).  Then in the gateway cloud management interface create exactly the same connection.  Then publish your report to the workspace and link the semantic model to the gateway.  After that you can enable scheduled refreshes.

[charlesbatista2]

I cannot connect the SQL server from that server to my Powerbi desktop because I cannot open ports of the server to connect it to my local PowerBI Desktop. 

[amjidali]

Hi Charles, 

I don't think It should be a complex setup, you don't need ot open the ports if you are using PowerBI on Server or Desktop. You jsut need to install the gateway. Gatway will automatically connect to powerbi Service using standard port 80. However if you are using Cloud Database then scenario is different.

Here is my Step by Step Guide on Setting Up PowerBI Gateway

[lbendlin]

 Gatway will automatically connect to powerbi Service using standard port 80.

not for a long time. Nowadays everything is exclusively 443.

[Shravan133]

• Ensure Gateway Installation and Configuration:

  • You’ve already installed the on-premises data gateway on your server, which is great.
  • Make sure the gateway is properly configured and online. You can check this in the Power BI Service under Manage Gateways.
  • In the gateway configuration, ensure that the connection to the SQL Server database is added.
    • Go to Manage Gateways → Add Data Source and provide the necessary SQL Server details, like the server name, database name, authentication method (Windows/SQL Server), and credentials.

• Create the Power BI Report:

  • In Power BI Desktop on your PC, connect to the SQL Server database using the SQL Server connector.
  • Create your report as usual, pulling data from the database hosted on your server (ensure the correct server connection string is used).
  • Save your report.

• Publish the Report to Power BI Service:

  • When you publish the report to the Power BI Service, Power BI will initially store the connection details you used when building the report on your local machine (the connection string points to your local server setup).

• Configure the Dataset in Power BI Service:

  • After publishing the report, go to the Power BI Service.
  • Navigate to the workspace where you published the report, find the Dataset for your report, and select Settings.
  • In the dataset settings, under Gateway Connection, select your on-premises Data Gateway.
  • You should see the option to map the dataset to the SQL Server connection you previously set up in the gateway. Select this data source.

• Map Dataset to Gateway Data Source:

  • Under the Data source credentials section, ensure that the credentials for accessing the SQL Server (on the server) are provided.
  • Once mapped, the dataset will now use the SQL Server connection via the on-premises gateway you installed on the server, instead of the local connection you used when creating the report.

[charlesbatista2]

Again, what I just said above. What I did was take a backup from the server, restore it in MSSM, and connect it to my powerbi desktop. I cannot connect directly the database from the server because I had to open ports to do that and that's not a good solution for me. 

[lbendlin · Accepted solution]

Your setup is not supported. Both your Power BI Desktop and the gateway must have direct line of sight to the on-premise data source.

[amjidali]

Be it using SQL Server, Mysql, Excel Sheet or Oracle Database, Process is simle. Here is my quick guide on How to Install and configure PowerBI Gateway