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 Anonymous
Yes you are correct, you will need a Gateway to connect app.powerbi.com to your SQL server.
If you install the Power BI gateway on Server 2 that server will need access to the local Intranet to access the SQL Server, and access to the Internet, so it can act as a gateway between the SQL database and the Power BI service.
We've done this many times, the Client has a private SQL server, which we would need a VPN to connect to from outside. Therfore we have the Client install a Power BI Gateway to access the SQL server, then give access through the Gatway to a Prower BI account which we use. This Power BI account can then connect to the SQL database, through Dataflows (Power Query in Service), and create Dataflow Tables from the extracted and transformed SQL data. These Dataflows can then ber used to build a Datamodel with relationships in Power BI desktop, and the Datamodel Published to Power BI.
The Dataflows as an intermediary save us having to have access directly to the SQL database, we just connect via a Power BI licence to a Dataflow, which connects to the Gateway, which accesses the SQL data.
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! 🙂