SQL Server Connection with On Prem gateway

[hasarinfareeth]

Hi - I have developed my report in Power BI Desktop having SQL server as the source. My SQL server in Power BI Desktop is accessible when I connect to my organisation VPN. I am aware that I need to install the onprem gateway software to establish a connectivity to SQL server to my report but I cannot install it in my laptop where I develop the reports as the gateway needs to be up and running 24/7, so I can choose to install the onprem gateway software in the SQL server itself where I have my database hosted. 

Is this a best approach?

Do I need to open up any firewall between the SQL server and the Power BI cloud service? If so, is there any guidance how this can be done?

If the gateway connection is established between SQL Server and the Power BI cloud service, would I be able to continue developing the report in Desktop software installed in my laptop?

Thanks

Fareeth

[lbendlin · Accepted solution]

 I can choose to install the onprem gateway software in the SQL server itself where I have my database hosted. 

That is not a very good idea. They will fight to the death and starve each other of memory and compute.  The gateway VMs (plural!!!) must be dedicated to just the gateway with nothing else running there.

The gateway VMs must have direct line of sight to the SQL server. They cannot manage VPN connections for you.

[Anonymous · Accepted solution]

Hi @hasarinfareeth,

Thanks for reaching out to the Microsoft fabric community forum.

You've already understood a lot of key points, and you're on the right track regarding the need for an on-premises data gateway to connect Power BI Service to your SQL Server. Let me clarify a few important things to help you make the best decision.
As already mentioned by @lbendlin, it's possible to install the gateway on the same machine where SQL Server is hosted, but it's not recommended for production environments. SQL Server and the gateway both require significant compute and memory resources, and running them on the same machine can lead to performance issues or stability problems. For this reason, it's a best practice to install the gateway on a dedicated server or VM that runs 24/7 and has direct network access to the SQL Server. On VPN and Connectivity part the gateway needs constant, direct access to SQL Server, and it does not manage VPN connections. So wherever you install the gateway, that machine must be on the same network (or have a trusted, permanent route) to the SQL Server without relying on someone being connected to a VPN.
Coming to the firewall part, the gateway communicates with the Power BI Service using outbound internet traffic only. You don’t need to open any inbound firewall ports. Just make sure the machine running the gateway can reach Power BI and Azure services over TCP port 443.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Anonymous]

Hi @hasarinfareeth,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Anonymous]

Hi @hasarinfareeth,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[Anonymous]

Hi @hasarinfareeth,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin · Accepted solution]

 I can choose to install the onprem gateway software in the SQL server itself where I have my database hosted. 

That is not a very good idea. They will fight to the death and starve each other of memory and compute.  The gateway VMs (plural!!!) must be dedicated to just the gateway with nothing else running there.

The gateway VMs must have direct line of sight to the SQL server. They cannot manage VPN connections for you.