Best way to grant SQL DB (serverless sql pool) access to end user.

Question

What is the best way to grant access to users for SQL DB(serverless sql pool) build on top of synapse azure lake db. How should SQL DB (views or external tables) access the lake database (storage container). Should we use RBAC, Managed Identity, ACLS, etc. Please tell me the best way/used in industry to handle it.

Answer 1

@Abbas Furniturewala

Thanks for reaching out to Microsoft Q&A

To grant access to end users for a serverless SQL pool built on top of Azure Synapse Lake DB, you have several options. I'll outline the most common approaches, their pros and cons, and the best practices

Option 1: Role-Based Access Control (RBAC)

• Pros: Fine-grained access control, easy to manage, and scalable.
• Cons: Requires careful planning and implementation of roles and permissions.

RBAC is a popular choice for access control in Azure Synapse. You can create custom roles with specific permissions and assign them to users or groups. This approach ensures that users only have access to the resources they need.

Option 2: Managed Identity

• Pros: Simplifies authentication and authorization, reduces administrative burden.
• Cons: Limited control over permissions, may not be suitable for complex scenarios.

Managed Identity allows your Azure Synapse instance to authenticate to Azure services without the need for credentials. You can use system-assigned or user-assigned managed identities to access the lake database.

Option 3: Access Control Lists (ACLs)

• Pros: Flexible and granular access control, suitable for complex scenarios.
• Cons: Can be complex to manage, may lead to permission creep.

ACLs allow you to define permissions at the object level (e.g., tables, views, or storage containers). This approach provides fine-grained control but requires careful planning and management.

Option 4: Azure Active Directory (AAD) Integration

• Pros: Leverages existing AAD infrastructure, simplifies authentication and authorization.
• Cons: Requires AAD setup and configuration.

By integrating Azure Synapse with AAD, you can use AAD groups and roles to manage access to the serverless SQL pool.

Best Practice:

The best approach is to use a combination of RBAC and Managed Identity. This provides a balance between fine-grained access control and simplified authentication and authorization.

To access the lake database, you can use external tables or views in your serverless SQL pool please follow the below links for detailed information. https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

https://www.serverlesssql.com/user-permissions-in-serverless-sql-pools-external-tables-vs-views/

This response contains a reference to a third-party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

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.