Hotspot Question You have an Azure subscription that contains a logical SQL server. The server hosts two databases named db1 and db2 and an Azure AD service principal named app1. You need to ensure…

Question

Exhibit

Answer Area

• CREATEUSER
  CREDENTIALLOGINUSER
• [app1]FROM EXTERNAL PROVIDER
  FOR LOGIN app1FROM EXTERNAL PROVIDERFROM LOGIN app1WITHOUT LOGIN

Explanation

This question tests knowledge of granting Azure AD service principals access to specific Azure SQL databases using T-SQL with the principle of least privilege.

Approach. The correct T-SQL statement to grant app1 access to db1 while following least privilege is: USE [db1]; CREATE USER [app1] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [app1]; - or simply grant a minimal role. You must first USE [db1] to switch context to the correct database, then CREATE USER [app1] FROM EXTERNAL PROVIDER to create the Azure AD-based user in that specific database, and finally assign a least-privilege role such as db_datareader (read-only) rather than db_owner or other elevated roles. Using 'FROM EXTERNAL PROVIDER' is the required syntax for Azure AD identities including service principals.

Concept tested. Granting Azure AD service principals (external providers) access to a specific Azure SQL database using T-SQL with least privilege - specifically the use of 'CREATE USER [app1] FROM EXTERNAL PROVIDER' within the correct database context and assigning a minimal database role.

Reference. https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure

No community discussion yet for this question.