How to link SQL server in private network from pipeline in Fabric?

[Winnie20242024]

Hello e , Do you have any idea about linking SQL server in private network from pipeline in Fabric?

We have created:
1)SQL server in private network in Azure
2)managed private endpoint in Fabric to link the private SQL server
3)Approved the private access under the private SQL server

As a test, we tried to connect the private SQL server in notebook, it works.

But when we tried to connect the private SQL server in pipeline in Fabric, it raise the following error: Connection was denied since Deny Public Network Access is set to Yes. 

Do you have any suggestion about this? Very appreciate if you could help on it.

Thanks.

[frithjof_v]

Per my understanding, managed private endpoint is for Spark (Fabric notebook) and Data Gateway is for fabric data factory.

Have you considered the Data Gateway option?

https://learn.microsoft.com/en-us/fabric/security/white-paper-landing-page

I don't have experience with it myself.

[Houston-ho]

They are 2 different features.

Data gateway is for on-premises data source. Example if you have a virtual machine on Azure, and you have SQL server inside, then you need date gateway to connect to that machine and that SQL database, same as Power BI, Data Factory linked service and Fabric.

For the current case, Azure SQL, probably not Data Gateway.

[frithjof_v]

Did you check out the VNET Gateway?

https://learn.microsoft.com/en-us/data-integration/vnet/overview

https://learn.microsoft.com/en-us/data-integration/vnet/use-data-gateways-sources-power-bi

It mentions Azure SQL. However I read someone mention that Data pipeline cannot use VNET Gateway, only On Prem Gateway.

I think the managed private endpoint is only for Spark so I don't think it will work with Data pipeline.

https://learn.microsoft.com/en-us/fabric/security/security-managed-private-endpoints-overview

It's my brief impression.

Edit:

Azure SQL database has a green checkmark for on-premises data gateway on this page: https://learn.microsoft.com/en-us/fabric/data-factory/pipeline-support#a

I'm curious to hear if you find a solution to your case, or if this is not possible to solve with fabric data pipeline.

[Houston-ho]

Please note Virtual network data gateway is not the same as data gateway.

VNET data gateways require a Power BI Premium capacity license (A4 SKU or higher or any P SKU). In addition, this feature is available only for Fabric Dataflow Gen2, Power BI semantic models, Power Platform dataflows, and Power BI paginated reports. Power BI dataflows and datamarts are not supported.

What is a virtual network (VNet) data gateway | Microsoft Learn

Meanwhile, On-premise Data Gateway, is FREE to download at below but i don't think that is needed to use it for Azure SQL, indeed you have no place to install it.

 Download On-premises data gateway from Official Microsoft Download Center

[frithjof_v]

It's annoying if VNET Gateway doesn't work with Data pipeline.

I think an F SKU would be okay, I would be surprised if the F capacity license would be a show stopper for using VNET Gateway. However if VNET Gateway is not supported for Data pipeline then this is a show stopper for Data pipeline in this case.

I'm wondering why this page has a green checkmark for Azure SQL database and On-Prem Data Gateway combination:

https://learn.microsoft.com/en-us/fabric/data-factory/pipeline-support#a

Anyway, I guess maybe Data pipeline is not possible for your case due to this connectivity limitation? Workaround is to use Notebook with Managed Private Endpoint or Dataflow Gen2 with VNET Gateway?

We can vote for this Idea to highlight the need:

Add support for PowerBI VNET Gateway in Fabric Pipelines

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=7e487fec-172a-ef11-8ee8-6045bd7f12d7

[Winnie20242024]

We have created private endpoint. But it works for notebook in Fabric. But it does not work for datapipeline in Fabric.

[Houston-ho]

I believe you are using different authentication method to your SQL server. Are they the same in both notelook and pipeline linked service?

[Winnie20242024]

In Notebook, we are using jdbc with user name and password to connect SQL server

In Pipeline, we are trying to use  the "Basic" auth kind with username and passsword like below:

Any suggestion? Thanks

[Houston-ho]

Have tried using other authentication method like AAD or Service Principal?

And have you enable this setting?

Allow Azure services and resources to access this server" is set to "Yes".

[Winnie20242024]

Currently we are limited to chose

• username/password or
• Service Principal

only one above but not both.

And we choose username/password for SQL server. 

For the networking settings, our public access is also disabled.

[Anonymous]

Hi, @Winnie20242024 

Depending on the information you report as an error, the cause could be Azure SQL Database is set to deny public network access. This requires to use managed virtual network and create private endpoint to access.

Recommendation:

1. If you insist on disabling public network access, use managed virtual network integration runtime and create private endpoint. For more information, see Azure Data Factory managed virtual network.

2. Otherwise, enable public network access by setting Public network access option to Selected networks on Azure SQL Database Networking setting page.

Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance...

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.