topic How to access Azure SQL Database without allowing public network access in Pipelines

How to access Azure SQL Database without allowing public network access

Marusyk — Wed, 15 May 2024 13:50:23 GMT

Of course, my production database is accessible only from specified vNets.

How can I access it from Data Pipeline in Microsoft Fabric?

I found the following options:

Managed Private Endpoints or Managed virtual networks - works only for Spark Job from notebooks. Is not an option because we need data pipeline
Data gateway - is too expensive and also is available only for Dataflows Gen2, why Data Pipelines are not supported is totally unclear!
IP allowlists - works but unsupportable because we need to know all IPs of Fabric.
Checkbox "Allow Azure services and resources to access this server" - is insecure because the data can be accessed from any other Azure Service

A private link for the SQL database is also useless because it requires a vNet of Fabric which is unavailable (because of SaaS?).

Managed Private Endpoints - looks very promising but only for Spark (why? are the any plans to support not only Spark and notebooks?)

So, could anyone help me how to access the data? Allow public network access is not an option here!

Re: How to access Azure SQL Database without allowing public network access

NandanHegde — Wed, 15 May 2024 14:17:00 GMT

Hey Marusyk ,

Based on my understanding Data gateways are now supported for data pipelines as well.

Also, you can set up a VM with small cost within the VNET and install the Power BI gateay in it which you can leverge for your connection ( with more security, there is bound to be some additional cost :))

In case if you want to avoid Gateway and additional costs, there would be some additional maintenance activity like updating the firewall on a yearly basis with IP changes.

Note : You need not whitelist all the fabric IP but only the IP range of the data pipeline in which your fabric capacity is hosted .

The IP range for fabric data pipeline is similar to the IP range of Azure Data Factory as they leverge the same engine/framework

Re: How to access Azure SQL Database without allowing public network access

Marusyk — Wed, 15 May 2024 22:59:49 GMT

Unfortunately no, Data Pipeline doesn't support Data Gateway - this is the reason why I'm raising this question here.

It is clear from here https://learn.microsoft.com/en-us/data-integration/vnet/overview

and I've just tried on a real Fabric instance

Could you suggest how to get that IP range? because maintaining VM is not suitable for us.

Re: How to access Azure SQL Database without allowing public network access

NandanHegde — Thu, 16 May 2024 04:27:20 GMT

Hey,

The gateway which I meant was Power BI On premises data gateway and not the VNET Data gateway 🙂

The On Premises data gateway is supported for data pipelines and hence suggested to create a VM within the VNET and set up the gateway.

But if that is not possible, you can whitelist the IP range :

https://learn.microsoft.com/en-us/azure/data-factory/azure-integration-runtime-ip-addresses

Re: How to access Azure SQL Database without allowing public network access

v-cboorla-msft — Fri, 17 May 2024 16:07:22 GMT

Hi Marusyk

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

Re: How to access Azure SQL Database without allowing public network access

Marusyk — Mon, 20 May 2024 07:12:14 GMT

The question is still open!

The answer from Nandan is definitely doesn't help

Re: How to access Azure SQL Database without allowing public network access

NandanHegde — Wed, 22 May 2024 01:56:49 GMT

Marusyk sorry for the delayed response. Can you please confirm what is that you are expecting other than either of the below 2 methods for network access:

1) whitelist the ip range of the data pipeline region in the firewall

2) set up a power bi gateway on a server hosted within the vnet via which you can access the database

Re: How to access Azure SQL Database without allowing public network access

Marusyk — Wed, 22 May 2024 07:07:30 GMT

I'm expecting that one of "Managed Private Endpoints or Managed virtual networks" or "vNet Data gateway" should work for Data Pipelines - because they are easiest way to securely access the data

topic How to access Azure SQL Database without allowing public network access in Pipelines

How to access Azure SQL Database without allowing public network access

Managed Private Endpoints or Managed virtual networks - works only for Spark Job from notebooks. Is not an option because we need data pipeline

Data gateway - is too expensive and also is available only for Dataflows Gen2, why Data Pipelines are not supported is totally unclear!

IP allowlists - works but unsupportable because we need to know all IPs of Fabric.

Checkbox "Allow Azure services and resources to access this server" - is insecure because the data can be accessed from any other Azure Service

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access

Re: How to access Azure SQL Database without allowing public network access