Study guide

5.1 Microsoft Entra ID Authentication for Azure SQL

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

5.1 Microsoft Entra ID Authentication for Azure SQL

Key Takeaways Explain This Topic AI

Microsoft Entra ID (formerly Azure Active Directory) authentication centralizes identity for Azure SQL Database, SQL Managed Instance, and SQL Server on Azure Arc-enabled servers, replacing per-database SQL logins with centralized, MFA-capable identities

Provisioning an Entra admin for the logical server or managed instance is the prerequisite for all Entra authentication - until an admin is set, CREATE USER FROM EXTERNAL PROVIDER fails and Entra logins are impossible

Authentication methods include password, integrated (Windows), universal with MFA, service principal, managed identity, and access-token-based; the choice is driven by the client, not the server

Entra-only server mode removes SQL authentication entirely, eliminating the SQL auth surface area for customers that require a pure Entra identity model

CREATE USER FROM EXTERNAL PROVIDER creates a contained database user backed by an Entra principal, so the user authenticates through Entra without a server login

Last updated: August 2026

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:

Target Entra support Notes

Azure SQL Database Logical server has an Entra admin Per-database contained Entra users; no server logins required

Azure SQL Managed Instance Entra admin plus server-level logins Supports Entra logins created with FROM EXTERNAL PROVIDER

SQL Server (Arc-enabled) Entra via Azure Arc extension On-prem/VM SQL Server gains Entra auth through the Arc agent

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.