topic Re: How to link SQL server in private network from pipeline in Fabric? in Pipelines

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

Winnie20242024 — Mon, 09 Sep 2024 02:32:01 GMT

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.

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

Anonymous — Tue, 10 Sep 2024 01:40:53 GMT

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:

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.
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, and Amazon RDS for SQL Server connectors - Azure Data Factory & Azure Synapse | Microsoft Learn

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.

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

Winnie20242024 — Tue, 10 Sep 2024 03:38:48 GMT

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

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

Houston-ho — Tue, 10 Sep 2024 06:54:28 GMT

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

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

Winnie20242024 — Tue, 10 Sep 2024 07:14:03 GMT

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

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

Houston-ho — Tue, 10 Sep 2024 14:39:18 GMT

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".

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

frithjof_v — Wed, 11 Sep 2024 05:57:06 GMT

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.

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

Houston-ho — Wed, 11 Sep 2024 08:11:15 GMT

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.

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

Winnie20242024 — Wed, 11 Sep 2024 10:38:39 GMT

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.

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

frithjof_v — Wed, 11 Sep 2024 11:14:36 GMT

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.

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

Houston-ho — Wed, 11 Sep 2024 12:45:39 GMT

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

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

frithjof_v — Wed, 11 Sep 2024 12:56:50 GMT

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