Exam DP203 Serverless SQL Pool Authentication

Serverless SQL Pool Authentication

Two types of authentication are supported:

SQL Authentication (username and password). Only within the Serverless SQL Pool.
Microsoft Entra authentication. Global reach within Azure.

Authorization refers to what a user can do within a serverless SQL pool database and is controlled by your user account's database role memberships and object-level permissions.

Access to storage accounts

If a user of either type (above) needs to access files in Azure Storage, or Azure Gen2, they must have one of the following permissions to be set up on the Azure Storage and referenced by the user I think:

Anonymous access
Shared access signature (SAS)
Managed Identity.
User Identity

Anonymous access

To access publicly available files placed on Azure storage accounts that allow anonymous access.

Shared Access Signature (SAS)

With a SAS, you can grant clients access to resources in storage account, without sharing account keys.

Managed Identity

Part of Microsoft Entra ID. Analogous to domain groups.

User Identity

Part of Microsoft Entra ID. Analogous to domain users.

Configuration

In Synapse Studio, go to Manage - Access Control

This lists the users, including NeilM the user I am logged on as.

Click Add, to Add role assignment. In the Role pop-up list select the "Synapse Administrator" role. In the Select User pop-up list, enter the user who you want to make a member of this role.

Access Control Lists (ACLs)

Azure role-based access control (Azure RBAC) and access control lists (ACLs) like Portable Operating System Interface for Unix (POSIX)

Each file and directory in your storage account has an access control list (ACL). There are two kinds of access control lists:

Access ACLs. Controls access to an object. Files and directories both have access ACLs.
Default ACLs. Are templates of ACLs associated with a directory that determine the access ACLs for any child items that are created under that directory. Files do not have default ACLs.

Levels of Permissions

Read
Write
Execute

For directories, Execute permissions are required in addition to read and write, to list the contents of the directory, or to create child items in the directory respectively.

Always use Microsoft Entra security groups as the assigned principal in an ACL entry

Roles necessary for serverless SQL pool users

For users which need read only access you should assign role named Storage Blob Data Reader.

For users which need read/write access you should assign role named Storage Blob Data Contributor

Database level permission

use master
CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER;

use yourdb -- Use your DB name
CREATE USER ualias FROM LOGIN [alias@domain.com];

use yourdb -- Use your DB name
alter role db_datareader 
Add member ualias

Server-level permissions

CREATE LOGIN [alias@domain.com] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE sysadmin ADD MEMBER [alias@domain.com];