Access data lake gen2 through Synapse Serverless external tables by technical user

[Nick34]

Hi all,

I want to grant access on Azure Synapse Analytics Serverless SQL Pool External Tables to some AAD users. The source of an external table is an Azure data lake gen2 parquet file. I’m not the owner of the data lake gen2  but I got the necessary role memberships to test accessing the parquet files through external tables. But the owner of the data lake gen2 don’t want to manage many user accounts to get access to the data lake. They will accept only a technical user per business domain  to grant access to. Is there any way to enable AAD user accounts accessing external tables without granting themselves access on the data lake? Only a technical user would get access permission to the data lake directly.

Thanks in advance for help.

Nick

[#2]

@Nick34, you should be able to accomplish this by creating database-scoped permissions for each AAD user in your serverless SQL pool database. The "Configure Database-scoped permissions" steps here show how to create an AAD login in the master database and assign a database role within a specific database. The db_reader role could be used to grant read access to all external tables within a serverless SQL pool, or you could grant the SELECT permission on a table-by-table basis.

Access to the underlying Data Lake Storage files would be provided for all users by a single External Data Source definition and credential (SAS token or workspace Managed Identity), as shown here.