On premise Data Gateway to access SQL data on a separate server

[rax99]

Hello all,

Im trying to find a solution to a problem I have around accessing SQL data via a On prem Data gateway. 

I have a client who needs to access data that sits on a secure isolated Server. The solution proposed was to set up an additional server where a data gateway could be installed (and only open a port to access the secure server) and have that access the public internet to store data on the app.powerbi.com service.

This is what I had in mind:

Server 1 currently has the SQL server installed where the data currently sits. The user needs access to this data (via public internet) to plug into his power bi client/app. 

I have 2 questions here to get a quick solution in place;

1. Am I correct in thinking to access this data he would need to go through the app.powerbi service, therefore there would be a need for a data gateway to connect him to the data?

2. If this data gateway was installed on another server (server 2) how do I connect it to the server where the data actually sits (server 1 in this case)?

I understand this can be achieved but it's a little confusing on just how an On-Prem DG connects to SQL server data sitting on a separate server.

Any advice would be great, as I could not find an appropriate explanation in the 'Search'

[makboyun]

Hi,

I have a similar question. Can I install the power bi data gateway to a server with no static IP address? My client has an internal IT infrastructure and their SQL server doesn't have a public IP address. They have an internal network. What is the best approach to get data from their internal sql server database? Thanks.

[Burningsuit]

HI @rax99 

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 

[rax99]

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?

[Burningsuit · Accepted solution]

Hi @rax99 

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  

[rax99]

I've tested this and gives me what I need. Many thanks for your support, saves me hours of digging around on the internet! 🙂