Configuring Azure Synapse Analytics Linked Service for an ODBC Linked Server

ClarissaJacquline 100 Reputation points

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.


  • 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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,798 questions
0 comments No comments
{count} votes

Accepted answer
  1. Smaran Thoomu 2,220 Reputation points Microsoft Vendor

    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.

    User's image

    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.

    User's image

    • I created linked service for SQL server through selfhosted IR: User's image
    • 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:
      User's image

    Source data Preview:
    User's image

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

    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.

0 additional answers

Sort by: Most helpful