The DP-300 exam's authentication and authorization section asks how you design 'who can enter the database' and 'what they can do once inside.' These map to Authentication and Authorization, and in the Azure SQL environment each operates at a completely different layer. Rather than memorization, the exam tests your ability to choose the right option for a given scenario.
SQL Authentication vs Microsoft Entra ID Authentication
Imagine two ways to enter a company building: a PIN issued by the building's management office, or a badge issued by corporate HR. SQL Authentication is the first option. The database engine stores and validates usernames and passwords directly. It matches the familiar on-premises SQL Server model and offers broad client compatibility, but you must separately manage password rotation and account disabling when employees leave.
Microsoft Entra ID authentication is the badge model. Your organization's central identity system handles authentication, so no password is stored in the SQL engine. When an employee's Entra ID account is disabled on departure, SQL access is automatically revoked. MFA, Conditional Access, and group-based permissions are all available natively.
Entra ID sub-modes: (silent sign-on via on-premises AD federation), (direct Entra ID credentials), (interactive login with MFA — appears most often in exam questions), and (non-interactive, for applications and services).
!SQL Authentication versus Microsoft Entra ID Authentication
Login, User, and Contained User
To enter a neighborhood diner, you first walk through the door (entry credential), then get seated at a table (table assignment). SQL Server security follows exactly these two stages.
A is a server-level security principal stored in the database. It grants the right to connect to the SQL Server instance. A is a database-level security principal. You must map a Login to a User inside a specific database before that principal can access any tables. The problem appears when you move a database to a different server: the User still exists inside the database, but the corresponding Login is absent on the new server — an 'orphaned user.'
solve this. Authentication data lives inside the user database itself rather than in master, so the database can move to any server without breaking authentication — the recommended approach for Azure SQL Database. Create an Entra ID user as a Contained User with . Register an entire Entra ID group and all its members gain access at once.
Role-Based Permission Model
In an office of 100 people, handing out individual keys to every room is impractical. Instead, you issue role-based cards — 'Sales Team Card,' 'Dev Team Card' — so that when someone changes teams, you only swap the card.
Fixed Server Roles at the server level: (complete authority), (create, modify, drop databases), (Login management + GRANT/REVOKE/DENY). Critical exam note: can grant permissions it does not itself hold, effectively enabling sysadmin-level privilege escalation — the exam frequently tests this risk.
Fixed Database Roles at the database level: (everything), (SELECT on all tables), (INSERT/UPDATE/DELETE), (role management + GRANT/REVOKE), (add or remove user accounts only). Azure SQL Database does not support because PaaS removes the server-level scope — use User-Defined Database Roles instead.
GRANT, DENY, and REVOKE
A warehouse manager grants employee A access to a storage room. Later, sensitive inventory arrives. How does the manager block A's entry without removing all permissions?
assigns a permission. removes a previously granted or denied permission. explicitly refuses a permission. The decisive rule: DENY always overrides GRANT. Even if a principal inherits GRANT through role membership, a direct DENY on the same object wins. Adding lets the recipient pass the same permission to other users — useful for delegation but prone to creating untraceable permission chains. Schema-level GRANT applies to every object inside that schema; object-level GRANT targets specific tables or views. Least privilege means always starting from the narrowest scope.
Managed Identity — No Passwords in Your Code
A developer hardcodes a username and password into a connection string, then accidentally pushes that file to a public repository. Managed Identity eliminates this risk at the source.
Managed Identity is an Entra ID-based identity automatically assigned to an Azure resource — a virtual machine, App Service, or Azure Functions. The Azure platform issues and rotates tokens automatically, so no password or certificate ever needs to live in code or config files. System-Assigned Managed Identity shares the lifecycle of its resource. User-Assigned Managed Identity can be shared across multiple resources. To use it with Azure SQL, set the Managed Identity as the Entra ID administrator on the SQL server, or register it as a Contained User and assign roles.
Authentication Method Comparison
| Scenario | Recommended Method | |----------|--------------------| | Legacy app, same model as on-premises SQL | SQL Authentication | | Org account login with MFA | Entra ID Universal with MFA | | Auto sign-on with on-premises AD domain credentials | Entra ID Integrated | | Azure app connects to SQL without storing credentials | Managed Identity | | Service-to-service auth, secret management acceptable | Service Principal |
The key difference: SQL Authentication stores credentials in the DB engine; Entra ID stores them in the central identity system. To align database access with the employee lifecycle, Entra ID authentication is the only viable path.
Common Exam Traps
If a librarian holds a master key to every book in the building, that is not least privilege. The DP-300 exam loves building traps around over-permissioned choices.
"Eliminate SQL password management, use company accounts" — pick Entra ID authentication. "All tables readable, no modifications" — pick (not ). "Delegate only grant and revoke, no data access" — pick , not (which only adds or removes user accounts). "Connect Azure app to SQL without credentials in code" — pick Managed Identity (Service Principal requires secret management). fails in Azure SQL Database — PaaS removes server-level scope. can grant permissions beyond its own, creating a path to sysadmin-level escalation.
Exam Key Takeaways
"Eliminate SQL password management, login with company account" -- Microsoft Entra ID authentication "Azure app connects to SQL without credentials in code" -- Managed Identity "Auto sign-on with on-premises AD domain credentials" -- Entra ID Integrated (requires AD federation) "Interactive login with MFA" -- Universal with MFA "Move database to another server, authentication intact" -- Contained Database User "Register Entra ID group in a database" -- CREATE USER [group-name] FROM EXTERNAL PROVIDER "Read-only least privilege" -- db_datareader "Manage permissions only, no data access" -- db_securityadmin "Add or remove user accounts only" -- db_accessadmin "DENY always overrides GRANT" -- DENY priority rule "Server-level permission delegation, dangerous role" -- securityadmin (effectively sysadmin-level) "CREATE SERVER ROLE fails in Azure SQL Database" -- PaaS, use User-Defined Database Role
Microsoft Entra ID = central identity lifecycle, Managed Identity = no secrets in code, Contained User = safe DB migration, DENY = always wins