Forum Discussion
On premise Data Gateway to access SQL data on a separate server
Hi Anonymous
No, you don't need custom connectors.
You install the Gateway on Server2 then from Power BI service go to "Settings" then "Manage Gateways". There you can select your Gateway and add a datasource to it. The datasource type will be SQL Server. The datasource entry in the Gateway on Server2 will hold the Server1 name, Database and authentication (login) details. That provides a path through the Gateway on Server2 to the actual Database on Server1.
The issue is that if you use Power BI Desktop (which you need to to build a Report based on the SQL database). You can't connect to a Gateway in Power BI Desktop, you can only connect to datasources you have access to on Desktop and you can't access Server1, so you can't get at the data.
The way around is to use Dataflows (In the Power BI Cloud) which DO allow access through a Gateway. So you create Dataflows in the Power BI cloud from data on Server1. That will effectively copy data from the Database on Server1 into the Cloud. Then Power BI Desktop can access the Dataflows created, and build Report visualisations based on them.
See:
On-premises data gateway - Power BI | Microsoft Docs
Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs
Hope this helps
Stuart
Hi Burningsuit,
Thanks for the reply. We currently have the infrastructure (2 servers) under our remit. The way I envisioned this, was that the gateway installed on Server 2 (which has access to public internet and ports open to SQL server on secured server 1) is used to connect the SQL server from server 1 to the app.powerbi service.
The problem I am having is how do I 'connect' the data gateway from server 2 to server 1? Is this using a custom connector? Have you had any experience with using a custom connector?
Hi Anonymous
No, you don't need custom connectors.
You install the Gateway on Server2 then from Power BI service go to "Settings" then "Manage Gateways". There you can select your Gateway and add a datasource to it. The datasource type will be SQL Server. The datasource entry in the Gateway on Server2 will hold the Server1 name, Database and authentication (login) details. That provides a path through the Gateway on Server2 to the actual Database on Server1.
The issue is that if you use Power BI Desktop (which you need to to build a Report based on the SQL database). You can't connect to a Gateway in Power BI Desktop, you can only connect to datasources you have access to on Desktop and you can't access Server1, so you can't get at the data.
The way around is to use Dataflows (In the Power BI Cloud) which DO allow access through a Gateway. So you create Dataflows in the Power BI cloud from data on Server1. That will effectively copy data from the Database on Server1 into the Cloud. Then Power BI Desktop can access the Dataflows created, and build Report visualisations based on them.
See:
On-premises data gateway - Power BI | Microsoft Docs
Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs
Hope this helps
Stuart
I've tested this and gives me what I need. Many thanks for your support, saves me hours of digging around on the internet! 🙂