5.1 Microsoft Entra ID Authentication for Azure SQL

Why Entra Authentication Matters for DP-300

Microsoft Entra ID (formerly Azure Active Directory, or Azure AD) is the cloud identity service that Azure SQL relies on for centralized authentication. Domain 2 of the DP-300 exam weights implementing a secure environment at 20-25%, and Entra authentication is the backbone of that domain. SQL-only authentication has two structural weaknesses the exam repeatedly returns to: identities live in every database separately (no central rotation, no central disablement), and credentials travel in connection strings. Entra authentication moves identity to a central authority, enables multifactor authentication (MFA), supports conditional access policies, and lets administrators revoke access in one place when a user leaves the organization.

Three Azure SQL targets accept Entra authentication, each with a slightly different surface:

Provisioning the Entra Administrator

Before any Entra user can be created, you must provision an Entra admin for the logical server (SQL Database) or the managed instance. Until an admin exists, CREATE USER FROM EXTERNAL PROVIDER fails because the database engine has no Entra tenant context to validate against. The admin can be a single Entra user or, more commonly, an Entra group - making a group the admin is best practice because it lets you rotate membership without touching the server configuration. You can provision the admin in the Azure portal (SQL server -> Microsoft Entra ID), with the Azure CLI (az sql server ad-admin create), PowerShell (Set-AzSqlServerActiveDirectoryAdministrator), or REST.

For SQL Managed Instance, the Entra admin must be a principal that can create a server identity - a managed identity assigned to the instance so it can read the Entra graph. The portal creates this server identity automatically when you set the admin; if you script the deployment, you must grant the MI a system-assigned managed identity and assign it the Directory Readers role in Entra so it can resolve group memberships and guest users.

Authentication Methods

Once an Entra admin is set, the engine accepts Entra tokens, but the client decides which method produces the token. The six methods the exam expects you to distinguish:

• Entra password - the user types an Entra username and password; the driver fetches a token using password grant. No MFA, so it is discouraged for interactive users but fine for legacy clients.
• Entra integrated (Windows) - works only when the workstation is Entra-joined or domain-joined with federation; the driver obtains a token silently using the signed-in Windows identity.
• Universal with MFA - the recommended interactive method. The driver prompts through the Microsoft Authentication Library (MSAL), supports MFA, conditional access, and token caching. SQL Server Management Studio (SSMS) and Azure Data Studio use this by default when you pick "Microsoft Entra ID with MFA".
• Service principal - an Entra application registration authenticates with a client secret or certificate; used by batch jobs and non-interactive services. The connection string must set Authentication=Active Directory Service Principal.
• Managed identity - a system- or user-assigned managed identity on an Azure VM, App Service, or Function fetches a token from the instance metadata service without any secret in code or config. This is the default for Azure-hosted workloads.
• Token-based - the application calls Entra itself (e.g., MSAL) and passes the resulting access token to the driver via the AccessToken connection property. Used when the standard driver modes do not fit, e.g., custom token caching.

A typical exam trap: changing the server-side admin does not change which method a client uses - the method is a client-side decision, and a service principal connecting with Authentication=Active Directory Password will fail even if the server is correctly configured.

Contained vs Server-Contained Entra Users

Entra users in SQL Database come in two flavors. A contained database user is created directly in the user database with CREATE USER [alice@contoso.com] FROM EXTERNAL PROVIDER and has no login in the master database. This is the recommended pattern: it keeps the user database portable (you can move it to another server and the users travel with it) and removes the master-database dependency.

A server-contained Entra user (login-based) is created first as a login in master with CREATE LOGIN [alice@contoso.com] FROM EXTERNAL PROVIDER, then mapped to a user in each database. This pattern is required when the user needs server-level roles (for example the Azure SQL special server roles ##MS_DatabaseManager## or ##MS_LoginManager##, or sysadmin/dbcreator on SQL Server and SQL MI) or when you want one login to control access to many databases uniformly. SQL Managed Instance uses logins extensively because it models a full SQL Server instance.

The decisive exam question: "Should this identity have a login in master?" If the answer is no - the user just needs data access in one database - use a contained user. If the answer is yes - server-scoped role membership, cross-database administration, or MI-wide login management - use a login.

Entra-Only Server and Guest Users

Entra-only server (also called Entra-only authentication) is a setting on the logical server that disables SQL authentication entirely. Once enabled, SQL logins (username/password in master) cannot connect, even if they exist; only Entra principals can authenticate. This is the strongest posture for customers that have migrated all identities to Entra and want to eliminate the SQL auth attack surface. You enable it in the portal (Microsoft Entra ID blade -> Entra-only authentication) or with az sql server ad-only-auth enable. The transition is not instantaneous: existing SQL connections are not killed but new SQL-auth connections are refused.

Guest users - Entra B2B users invited from another tenant - can authenticate to Azure SQL, but only if the Entra admin is a group that includes them, or the admin is the guest user itself. By default, guest users are excluded from the directory reads the server performs; the server identity (or admin group) must have the right to read guest user properties, which is why granting Directory Readers to the MI server identity matters. A common failure mode on the exam: a guest user receives "principal not found" even though they appear in the portal - the resolution is to ensure the server identity can read guest objects.

Arc-Enabled SQL Server Entra Authentication

For SQL Server on Azure VMs or on-premises servers registered with Azure Arc, the SQL Server Entra ID authentication extension enables Entra-based logins without needing a domain controller. The Arc agent brokers Entra tokens; you provision an Entra admin on the Arc-enabled SQL Server resource, and the extension wires up the Entra ID app registration and the certificate that establishes trust between the instance and Entra. The certificate used for Microsoft Entra login must have a unique common name (CN) in the machine certificate store - if two certificates share a CN, SQL Server may select the wrong one and every Entra login fails. Once enabled, you can CREATE LOGIN [alice@contoso.com] FROM EXTERNAL PROVIDER and CREATE USER ... FROM EXTERNAL PROVIDER on the on-prem SQL Server just as on Azure SQL. Key operational facts: the server must have a connection to Azure (Arc requires it), certificate rotation happens automatically on a 30-day cadence, and you must grant the SQL Server's managed identity the Directory Readers role in the tenant for group and guest resolution. An exam scenario describing an on-prem SQL Server that needs cloud identities without Active Directory should resolve to Arc-enabled Entra authentication, not a traditional Windows auth setup.