Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
biswedishdevil
Advocate I
Advocate I

Scheduled refresh breaking with VPN secured on-premise SQL Database table

I have a Power BI Service pointing to an on-premise SQL Server Database table that belongs to a secured VLAN where I need VPN credentials to access. I have built and Enterprise Gateway to play as a bridge to the on-premise server and secure the data trasnfered. If I setup a scheduled refresh of the report in Power BI Service, and I'm in the office connected to the corporate network, everything runs smoothly. As soon as I leave the office, and my surface is not connected to the VPN, the Schedule Refresh crashes...

 

I have installed the On-premise gateway in my local machine, not in the on-premise server, that might be a valid reason to crash, isn't it?

 

Am I missing something?

 

I'm using my windows credentials hard-coded in the data source.

 

Thanks for you time!

Data analytics supporter !
1 ACCEPTED SOLUTION

Hi @biswedishdevil,

 

- When I want to setup the scheduled refresh to use the new gateway I can't select the new one. (although if I go to Manage gateways I've setup the datasource and it's online).

 

Assume the dataset only have one SQL Server data source, and you have created the SQL server data source use the same connection information and credential as in desktop under two data gateways, those two gateways should be available for this dataset.

 

q5.PNG

 

 

- What happens when the dataset i've created have two source tables that come from two different servers? As I far as I know, I can only use 1 gateway for each dataset scheduled refresh.

 

Assume the dataset have two SQL Server data sources, we must create two SQL Server data sources use the same connection information and credential under the same data gateway. If those two data sources are created under different data gateways, the dataset can't be configured to use data gateway.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
GilbertQ
Super User
Super User

hi @biswedishdevil

 

The scenario you described is exactly what I would expect to happen.


I would assume that as soon as you are off your network, which also means you are no longer connected using the VPN, the Gateway can no longer get the required access to the SQL Database. And when that happens the Refresh will fail.

 

I would suggest if possible to install and configure the Gateway to run on a server inside your network. This would then mean the VPN is no longer required. As well as then the refreshing would happen as per the schedule.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I've tried applying the solution, but I find two extra problems:

- When I want to setup the scheduled refresh to use the new gateway I can't select the new one. (although if I go to Manage gateways I've setup the datasource and it's online).

- What happens when the dataset i've created have two source tables that come from two different servers? As I far as I know, I can only use 1 gateway for each dataset scheduled refresh.

Data analytics supporter !

Hi @biswedishdevil,

 

- When I want to setup the scheduled refresh to use the new gateway I can't select the new one. (although if I go to Manage gateways I've setup the datasource and it's online).

 

Assume the dataset only have one SQL Server data source, and you have created the SQL server data source use the same connection information and credential as in desktop under two data gateways, those two gateways should be available for this dataset.

 

q5.PNG

 

 

- What happens when the dataset i've created have two source tables that come from two different servers? As I far as I know, I can only use 1 gateway for each dataset scheduled refresh.

 

Assume the dataset have two SQL Server data sources, we must create two SQL Server data sources use the same connection information and credential under the same data gateway. If those two data sources are created under different data gateways, the dataset can't be configured to use data gateway.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Good morning,

 

Although the first point is solved, the second is not...

 

Let's imagine the following scenario:

 

Server 1 contains SQL Server Database A

 

Server 2 contains SQL Server Database B

 

I have a Power BI Dataset that imports data from both SQL Server Database A and SQL Server Database B, that live in different Servers.

 

Now I want to schedule data refresh, therefore I need to setup a Gateway. As far as I understood, in order to avoid VPN autentitaction, I need to install an on-premise gateway in the server where the database is located.

 

In this case, I assume I need to install a gateway in Server 1 and another gateway in Server 2, so I can reach both servers without the need of the VPN authentication. But as you said, the dataset deployed in the Power BI service cannot use more than one gateway... so, what is the solution then?

Data analytics supporter !

I will try as soon as I get to the office! Thanks in advance!
Data analytics supporter !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors