How to connect to the external tables/serverless views I created in Synapse from another Synapse instance?

Deba Nayak 41 Reputation points
2023-03-01T20:03:17.34+00:00

I tried using the Synapse connector in the pipeline, and it has a mandatory field to select the database. Only the dedicated SQL pool databases are visible from the drop-down. I have business logic added to the views and can not allow the consumers to access the files from the lake

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.
2,834 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,642 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KranthiPakala-MSFT 35,101 Reputation points Microsoft Employee
    2023-03-03T20:34:01.7033333+00:00

    Hi @Deba Nayak ,

    Welcome to Microsoft Q&A forum and thanks for reaching out here.

    As per my understanding, you would like to know how to establish connection to Synapse Serverless SQL DB from another Synapse instance. Please correct me if I misunderstood the ask.

    In order to establish connection to Synapse Serverless SQL, you can utilize Azure SQL DB linked service or Azure Synapse Analytics linked service. I tried with both and was successful with Azure SQL database linked service but not Azure Synapse analytics linked as I noticed a bug with it hence escalated to respective product team.

    Coming back to Azure SQL DB linked service, you will have to first create dummy Azure SQL DB linked service and then update linked service - FQDN to 'Serverless SQL endpoint (krpakalasynapse-ondemand.sql.azuresynapse.net)' and DB name to Servereless DB name which you would like to connect to and then test the connection.

    Note: Please make sure that you create a login and user for the Synapse instance from which you are trying to connect as explained in this article: Read data from Azure Synapse Serverless SQL Pools with Azure Data Factory

    Below is sample:

    CREATE LOGIN [Managed identity name of Synapse from which you want to connect]
    FROM EXTERNAL PROVIDER;

CREATE USER [Managed identity name of Synapse from which you want to connect]
   FROM LOGIN [Managed identity name of Synapse from which you want to connect];

ALTER ROLE db_datareader ADD MEMBER [Managed identity name of Synapse from which you want to connect];

    User's image

    Test output:
    User's image

    Hope this info helps.

    Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.