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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MikaelDavidsson
Frequent Visitor

OnPremise Gateway, cannot write to lakehouse

Hi,

 

I am setting up an extraction step in my medallion-arcitechure in Fabric where I want to get data from an on-premise SQL Server. I have installed the on premise datagateway (latest release) and I can find the table I want to extract when I set up a new Data flow Gen 2 artifact. I choose my lakehouse (both de destination and data flow gen 2 artifact exsist in the same workspace) and publish the Dataflow.

 

All the staging artifacts are created (DataflowStagingLakehouse, SQL-endpoint etc.) and the dataflow is published corectly. However, when running the dataflow I get an error saying that the activity "WriteToDatabaseTableFrom_TransformForOutputToDatabaseTableFrom_****_*******" fails (** is for my table name). The only error message I get is this:

 

"GatewayCannotAccessSqlError Couldn't refresh the entity because of an internal error"

 

MikaelDavidsson_0-1694002402239.png

 



 

MikaelDavidsson_1-1694002464463.png

*text in swedish

 

The data is stored in the DataFlowStagingLakehouse but is never able to arrive in the designated lakehouse. If i remove the destination from the dataflow, the job works fine without the errors.

MikaelDavidsson_2-1694002580823.png

 

 

What I have done so far:

 

Is thera anyone who has any idea what might be the issue?

1 ACCEPTED SOLUTION
MikaelDavidsson
Frequent Visitor

We have now managed to get the data in to the lakehouse. Greate success!

 

The information that I got from our hosting partner was that traffic going to pbipneu5-northeurope.pbidedicated.windows.net. was blocked. When this blockade was lifted, it started working

 

New rule in firewall:

Protocol: TCP
Endpoint: pbipneu5-northeurope.pbidedicated.windows.net
Port: 1433

 

pbipneu5-northeurope depends on what region you have chosen.

 

Hope this helps.

 

//Mikael

View solution in original post

8 REPLIES 8
MikaelDavidsson
Frequent Visitor

We have now managed to get the data in to the lakehouse. Greate success!

 

The information that I got from our hosting partner was that traffic going to pbipneu5-northeurope.pbidedicated.windows.net. was blocked. When this blockade was lifted, it started working

 

New rule in firewall:

Protocol: TCP
Endpoint: pbipneu5-northeurope.pbidedicated.windows.net
Port: 1433

 

pbipneu5-northeurope depends on what region you have chosen.

 

Hope this helps.

 

//Mikael

Hi Mikael,

Thanks for sharing your approach! So am I right in assuming i would have to do these changes on the machine where the Data Gateway is installed?

No, only in the firewall settings, wherever that happens to be running on your network.

Pragati11
Super User
Super User

Hi @MikaelDavidsson ,

 

I did exactly same thing few weeks back and I can confirm that the approach I followed on getting on-premises SQL Server data to Lakehouse using Dataflow Gen 2 works. I tried it again few minutes back by bringing and writing a new table to Lakehouse.

I am not sure where things are gone wrong for you.

I wrote a blog about the whole process and configurations I followed to set this process up at my end.

Worth checking this one once. May be you can identify some difference:

https://datavibe.co.uk/getting-on-premises-sql-server-data-to-fabric-lakehouse/

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

I read your blog page and you wrote that using Organizatinal for authentication does not work.  However, that's what I used, and the UI picked up the already existing connection to the on-prem DB just fine, populated the DF Power Query UI fine.  It's just when it comes to publishing that sometimes the table is created and the data persisted to the lakehouse and sometimes not.  When it is, the columns are always lost and replaced by generic names such as Column1, Column2, etc...

 

I really don't understand why this is not working and even showing inconsistent behavior in failure.  

MarkBurgess
Frequent Visitor

Hi, did you ever get to the bottom of this. I've checked all the things that you mentioned you'd already done and I'm out of ideas! Thanks

miguel
Community Admin
Community Admin

I'm not entirely sure we can reach a conclusion with the information provided, but definitely using the latest gateway and making sure that its not a network firewall issue are on top of my mind for things to consider given the information.

 

I'd highly recommend reaching out to our support team so they can take a closer look at your scenario and try to troubleshoot.

https://support.fabric.microsoft.com/support 

I get the same message, although I want to write to a WH.

 

The GW connection is working for Power BI

Data is present in the staging lakehouse

I've tried to wrtite data both with and without staging. 

We use Finnish_Swedish_CI_AS as collation and I've tried using both English locale and Swedish locale for the DF.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Oct Fabric Update Carousel

Fabric Monthly Update - October 2024

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

October NL Carousel

Fabric Community Update - October 2024

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