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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
marcoG
Resolver I
Resolver I

Dataflow Gen 2 from on premise(Sql server) to lakehouse error

I have yet update gateway to last release 3000.174.13 and i had set proxy in the gateway to permit cloud connection.

 

Is there some functionalities to monitor and admin dataflow gen 2 connection?

 

Thanks!

 

Error type:

WriteToDatabaseTableFrom_TransformForOutputToDatabaseTableFrom_StatoSinistro

 

Error description:

null Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Details: DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server);ErrorCode = -2146232060;Number = 53;Class = 20 GatewayObjectId: 305f0d41-e624-4c13-b947-0f2761403cb7. .

2 ACCEPTED SOLUTIONS

Yes, that fixed it.

We've added rules to allow *.datawarehouse.pbidedicated.windows.net to communicate on 1433.

 

Not sure if it was needed but we've also added rules for *.uksouth.cloudapp.azure.com as there seemed to be some redirects going on.

 

Ben



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

marcoG
Resolver I
Resolver I

I found this article:

https://learn.microsoft.com/en-us/fabric/data-factory/gateway-considerations-output-destinations

 

marcoG_0-1689752706476.png

 

 

 

And this fabric blog article:

https://blog.fabric.microsoft.com/it-it/blog/data-factory-spotlight-dataflows-gen2?ft=All:

 

marcoG_1-1689752706482.png

 

 

So i will attend for next release to disable staging area so I don’t need to use 1433 port.

I think this will work for me.

View solution in original post

11 REPLIES 11
marcoG
Resolver I
Resolver I

I found this article:

https://learn.microsoft.com/en-us/fabric/data-factory/gateway-considerations-output-destinations

 

marcoG_0-1689752706476.png

 

 

 

And this fabric blog article:

https://blog.fabric.microsoft.com/it-it/blog/data-factory-spotlight-dataflows-gen2?ft=All:

 

marcoG_1-1689752706482.png

 

 

So i will attend for next release to disable staging area so I don’t need to use 1433 port.

I think this will work for me.

bcdobbs
Super User
Super User

I think I'm starting to understand the problem. A workspace has a single SQL endpoint that covers all of the lakehouss/warehouses etc. I can connect to it outside of workplace with SSMS but inside SSMS gives the same connection issue. 
I've asked IT to ensure *.datawarehouse.pbidedicated.windows.net isn't being blocked and can access port 1433 (Connectivity to data warehousing - Microsoft Fabric | Microsoft Learn).

 

Will update tomorrow.

 

Ben



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Yes, that fixed it.

We've added rules to allow *.datawarehouse.pbidedicated.windows.net to communicate on 1433.

 

Not sure if it was needed but we've also added rules for *.uksouth.cloudapp.azure.com as there seemed to be some redirects going on.

 

Ben



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hello, I am facing the exact same issue. Can you please explain the process of this step "We've added rules to allow *.datawarehouse.pbidedicated.windows.net to communicate on 1433."? So far I have taken the following steps:
1) I went into my "Windows firewall with advanced security" in the machine that is hosting the Gateway and added Inbound & Outbound rules to allow Port 1433.
2) Restarted the Gateway and tried to run Dataflow Gen 2 to Lakehouse again and still getting the same error. 
Not sure where I am supposed to add the Endpoint Step "datawarehouse.pbidedicated.windows.net to communicate on 1433". Please help

@bcdobbs Hi thanks for the info.
I'd like to tell my infra guy where exactly to add that rule in Azure, do you have more details?
(sorry I know it's basic question, but I am not Azure admin and the admin asks what he needs to do exactly)
Thanks!

I was talking about on prem firewall not azure (unless all your servers are VMs in azure). Afraid I can't help.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

That's ok thanks anyway, we were able to figure it out. But you are right, it was on prem firewall.

Have a good day

Thanks for the solution found, but this for me is not the best solution but a workaround.

The solution must use the corporate proxy and must not include firewall openings

miguel
Community Admin
Community Admin

This might be a situation where your firewall might be preventing a connection to write data to the staging location. You could monitor your firewall to see if this is the case and if there some connection attempts being blocked by it

I'm getting the same. What needs to be open on the firewall to allow connectivity. Never had a firewall issue with dataflows gen1 so assume it's something required to be open to allow acces to one lake storage?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

With Azure Synapse integration runtime don't have firewall problem...
Can Microsoft Fabric, from on premise sql to lakehouse, also achieve the same result through corporate proxies?
In Azure Synapse each source and each target can decide which integration runtime to use.
Therefore, using the gateway for source and destination, a direct connection is used, while using the gateway for the on premise source and without the gateway for the cloud source, network problems would be avoided.
The solution of opening endpoints on the firewall for each lakehouse is impractical.
Why don't use Azure Synapse approach since the capacity is owned by the user?
Thank you

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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