Save onprem SQL Server data into Lakehouse through Dataflow Gen2

[amaaiia]

I have the gateway installed on the machine with the SQL Server running that is not open to the internet. I have created a Dataflow Gen2 to access the data and I can see the data through Dataflow preview, so the connection is right between Fabric and onprem server. But when I try to set the Lakehouse as the destination , I indicate the Data gateway as the same used before for getting the source, but it doesn't let me sign in with the Organizational account. I get an error: An unknown error occurred. If this problem recurs, please contact support with this message. (Session ID: ------- Region: europe)

So, I can reach the data through gateway but I'm not able to store the data in a lakehouse.

Any ideas?

[amaaiia · Accepted solution]

There is not a solution. We've had 5 extended calls with Microsoft support to solve this but they don't know why is this happening to us. Finally, we are working with new internal users created in main tenant (not guest users).

[v-cboorla-msft]

Hi @amaaiia 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience that you are facing here.

This seems to be an intermittent issue. Can you please try running the Dataflow Gen2 after a day or try to create a new Dataflow Gen2 and execute the scenario. If still the issue persists please do let us know.

I tried to repro the scenario and didn't find any difficulty in accessing the data destination as Lakehouse. Please find the screenshots for reference.

I hope this information helps. Please let me know if you have any further questions.

Thanks.

[amaaiia]

Hi.
I have tried again with the same DataFlow Gen2 and creating a new one and I still have the same problem.

When configuring the gateway my user was not defined, but the user added in the configuration has given me the role of Connection Creator. I understand that this is enough.

[v-cboorla-msft]

Hi @amaaiia 

When using Microsoft Fabric Dataflow Gen2 with an on-premises data gateway. The underlying problem occurs when the gateway is unable to connect to the dataflow staging Lakehouse in order to read the data before copying it to the desired data destination. This issue can occur regardless of the type of data destination being used.

Please refer the documentation for more information.

This might also help you in resolving your query, if you are running on the older version of on-prem gateway, try to upgrade the version to the latest. After updating the on-premise gateway to the latest version, you should be able to set the Lakehouse connection.

For additional information please refer this documentation.

Refer to the similar thread for more details: Thread Link

I hope this information helps. Please do let us know if you have any further questions.

Thanks.

[amaaiia]

I have checked both.
1. There is access between the lakehouse and the SQL Server machine:

2. The gateway version is recent, from december 2023. Version 3000.202.16 (December 2023)

I still get the same error.

[pqian_MSFT]

@amaaiia can you share the session ID here (it is PII-free), that way we can at least see what is the error message.

We may also need additional logs from your GW machine to diagnose it further.

[v-cboorla-msft]

Hi @amaaiia 

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.

[v-cboorla-msft]

Hi @amaaiia 

Apologies for the delay in response from my end.

Could you please try connect to data destination by selecting "none" under Data gateway.

I hope this information helps. Please do let us know if you any further questions.

Thanks.

[v-cboorla-msft]

Hi @amaaiia 

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

[amaaiia]

I still have the same issue when trying to write data into Lakehouse. Tenant owner has created an internal user for me just to continue my work, because with tenant user this error is not happening.

At the same time, I created a ticket for this and I'm having calls with Microsoft Support, for the moment we don't have a solution for this. As soon as we solve it I will post the solution.

[v-cboorla-msft]

Hi @amaaiia 

If you have raised support ticket, could you please provide the ticket number here as we can keep an eye on it.

Thanks