Access to synapse serverless views without having access to underlying ADLS (data lake)

Question

Hi Xperts,

I have a scenario where we have multiple files sitting on ADLS gen2 and I have created views on serverless SQL pool referencing the files. Is there a way to grant access to the users to be able to query the serverless views without granting access on ADLS gen2 where the files exist.

I can't enable anonymous access on the ADLS gen2 account.

Thanks,

Eswar

Answer 1

@Eswar Sahukari - Thanks for the question and using MS Q&A platform.

A serverless SQL pool query reads files directly from Azure Storage. Permissions to access the files on Azure storage are controlled at two levels:

• Storage level - User should have permission to access underlying storage files. Your storage administrator should allow Azure AD principal to read/write files, or generate SAS key that will be used to access storage.
• SQL service level - User should have granted permission to read data using external table or to execute the OPENROWSET function. Read more about the required permissions in this section.

This article describes the types of credentials you can use and how credential lookup is enacted for SQL and Azure AD users.

OR

You can access ADLS Gen2 using a serverless pool in Synapse Analytics using a private endpoint.

To achieve this, you need to create a private endpoint for the ADLS Gen2 storage account in your virtual network and associate it with a subnet. Once you have created the private endpoint, you can use it to access the ADLS Gen2 storage account from the serverless pool in Synapse Analytics.

Here are the high-level steps to achieve this:

1. Create a virtual network and a subnet for Synapse Analytics.
2. Create a private endpoint for the ADLS Gen2 storage account in the virtual network and associate it with the subnet.
3. Configure the private endpoint to use Azure AD authentication.
4. Grant the serverless pool in Synapse Analytics access to the private endpoint by adding the private endpoint to the storage account's virtual network service endpoint policies.

For more detailed information on how to set up a private endpoint for ADLS Gen2 storage account and use it with Synapse Analytics, please refer to the following documentation: How To Setup Azure Synapse Analytics With Private Endpoint.

Hope this helps. Do let us know if you any further queries.

---

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.