Lakehouse query output to on-prem sql database

[wenchi]

I need to export my query outcome from lakehouse to on-prem sql database and can't get it to work. I use the copy data pipeline. The data source is pointed to sql endpoint of lakehuose. The denstination step causes the error. I am pretty sure i enter the correct information for the sql server, database and credential. The only thing i don't know is for the gateway. We already have gateway in place to move data between on-prem sql and azure sql. For fabric, do i need to download a different gateway? Is the pipeline a right tool to use for this?

Thanks,

Wenchi Wang

[v-prasare]

Hi @wenchi,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,

Prashanth

[v-prasare]

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@burakkaragoz & @suparnababu8 , thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support

[burakkaragoz]

Hi @wenchi ,

You're on the right track with using the copy data pipeline - that's definitely the correct tool for this scenario.

Regarding the gateway question: Yes, you'll need to configure your existing gateway for Fabric, but you don't need to download a separate gateway. Your current on-premises data gateway can work with Fabric, but it needs to be registered and authorized specifically for your Fabric environment.

Here's what you need to do:

1. Update your gateway to the latest version if it's not already current
2. Register the gateway with Fabric - this is different from your Azure SQL registration
3. In the Fabric admin portal, go to Manage connections and gateways
4. Add your on-premises SQL Server connection using the registered gateway
5. Test the connection before using it in your pipeline

Pipeline setup tips:

• Source: SQL endpoint of your lakehouse (sounds like you have this right)
• Destination: On-premises SQL Server connection via gateway
• Make sure your gateway machine can reach both your SQL server and has internet connectivity to Fabric

Common gotcha: Even if your gateway works fine for Azure SQL, the Fabric service needs its own authorization to use that same gateway.

Try updating and re-registering your gateway with Fabric first. If you're still getting destination errors after that, the issue might be in your connection string or credentials format.

Let me know what specific error message you're seeing on the destination step - that would help narrow down the exact issue.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[suparnababu8]

Hi @wenchi 

Even though you are using gateway for Azure SQL. Fabirc required another gateway to register and work in the Fabric environment. I highly recommend install lateast gateway, register & authorize gateway with your Fabric  env. Then use that in your pipeline. Check this thread Using the On-premises data gateway with Microsoft Fabric It might helps you.  Just give a try this way.

Please let me know if it works.

Thank you!!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!