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

Question

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

Answer 1

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];

Test output:

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.