Configuring Azure Synapse Analytics Linked Service for an ODBC Linked Server

Question

My client has granted me access to their server using an ODBC connector, which has created a SYSTEM DS on my local machine. My intention is to replicate the data in Az Synapse Data Lake for analytical purposes. However, the Azure Synapse Linked Serio configuration does not provide an option to reference the newly linked server.

Approaches:

• Include connection parameters in Azure Synapse to reference the linked server.
• Configure the integration as an ODBC connector instead of a SQL server.
• As a last resort, consider creating a duplicate image of the data to connect to Azure Synapse.

Recommendations please?

Answer 1

Hi Clarissa
Welcome to the Microsoft Q&A platform and thank you for posting your question here.
As far as I know, we can't connect to linked servers in SQL Server. If we want to copy data from a linked server, we can copy the tables to a database and then copy the data to blob storage.

To do this, I followed these steps:

1. I created an ODBC linked server in SQL Server.
2. I retrieved some data.

I copied the linked server table to database using below code:

SELECT *     
INTO db.dbo.product  
FROM linkedserver.AZURE SYNAPSE.dbo.product	

The table is copied successfully. I created synapse workspace and created selfhosted IR and connected successfully.

• I created linked service for SQL server through selfhosted IR:
• I am having data lake storage linked service. I create new pipeline. Performed copy data activity following below procedure:
• I created sql server dataset using sql server linked service and selected dbo.product table as source:
  

Source data Preview:

• I run the Copy activity. It run successfully. In this way you can copy data from linked server of sql server.

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

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.