Study guide
5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting
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.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting
Key Takeaways Explain This Topic AI
SQL Server on Azure VMs supports SQL authentication and Windows authentication; Entra authentication requires Azure Arc registration plus the SQL Server Entra extension, not just the VM itself
Azure SQL Managed Instance supports three authentication modes: SQL only, Entra only, and mixed, configured by provisioning or removing the Entra admin and toggling Entra-only authentication
Managed Instance relies on a certificate-protected server identity for Entra; rotating the certificate used for the cluster and the Entra trust is automated but must succeed for Entra logins to keep working
The most common Entra auth failure is an unset Entra admin; other top failures are network/NSG blocking 1433/3342, expired tokens, guest users without Directory Readers, and service principal identities missing the MI server identity
Diagnostic order for auth failures: verify admin is set, verify network path, verify the client method matches a supported mode, verify the principal exists and has directory-read rights, then check token validity
Last updated: August 2026
Authentication on SQL Server in Azure VMs
SQL Server on Azure Virtual Machines is infrastructure-as-a-service, so authentication works the same way it does on any on-premises SQL Server: you can enable SQL authentication (mixed mode), Windows authentication , or both, by setting the server authentication mode in SSMS or via T-SQL. There is no Azure-platform identity integration by default - the VM may be domain-joined for Windows auth, stand-alone for SQL auth, or workgroup-joined. Two configuration choices the exam checks:
SQL authentication (mixed mode) is in effect when SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') returns 0 ; a return of 1 means the instance is Windows-authentication-only. If a scenario requires SQL logins to be disabled entirely, set the server to Windows-only mode; this is the most secure default for a domain-joined VM.
Windows authentication requires either an Active Directory domain or, for cloud-only deployments, Entra-joined VMs with Kerberos. Without a domain, Windows auth falls back to local machine accounts, which is rarely what production workloads want.
To add Entra authentication to a SQL Server VM, the server must be registered with Azure Arc and the SQL Server Entra ID authentication extension installed. The extension brokers tokens and creates a certificate-based login; the SQL VM does not gain Entra support merely by being an Azure VM - Arc is required. This is a frequent trap: a scenario may say the VM is in Azure but never mention Arc, in which case Entra authentication is unavailable until Arc is configured.
Authentication on Azure SQL Managed Instance
Azure SQL Managed Instance (SQL MI) exposes three authentication modes controlled by whether an Entra admin is provisioned and whether Entra-only authentication is toggled:
- Mode SQL logins Entra logins How configured
- SQL only Allowed Disabled No Entra admin provisioned
- Mixed Allowed Allowed Entra admin provisioned, Entra-only OFF
- Entra only Disabled Allowed Entra admin provisioned, Entra-only ON
SQL MI is built on a cluster that uses certificates to secure its internal Always On availability group and to establish trust with Entra. When you provision the Entra admin, the platform creates a server identity (a managed identity assigned to the instance) and grants it Directory Readers in your tenant so the engine can enumerate group memberships and resolve guest users. Entra logins in MI are real server-level logins created with CREATE LOGIN [group-or-user] FROM EXTERNAL PROVIDER , mirroring on-prem SQL Server more closely than the contained-user model on SQL Database.
Certificate and Key Rotation on Managed Instance
SQL MI maintains an internal certificate hierarchy that protects the cluster's service broker, the database master key, and the Entra trust. The platform rotates these automatically , and the rotation window is near-transparent for SQL logins. Two rotation-related facts appear on the exam:
Service-managed rotation : the platform replaces the cluster certificate on a regular cadence without downtime; you do not and cannot rotate it manually.
Entra trust certificate rotation : the certificate that establishes trust between the MI and Entra also rotates automatically; if the MI's server identity loses Directory Readers during a directory reconfiguration, the engine cannot refresh group memberships, and group-based logins may fail until access is restored.
The take-away is that you should not write runbooks that depend on a specific certificate thumbprint for MI; rely on the platform and monitor the Azure Service Health notifications for rotation events.
Troubleshooting Authentication Failures
The exam loves to present a connectivity symptom and ask for the diagnostic step that resolves it. The structured order, from most common to least:
Entra admin not set - the number-one cause of Entra login failures. Symptom: CREATE USER FROM EXTERNAL PROVIDER fails, or an Entra login attempt returns a generic login error. Fix: provision an Entra admin on the server or instance.
Network/NSG blocking the port - SQL Database uses port 1433; SQL Managed Instance uses a private endpoint on a custom port (typically 3342 for the proxy type, or 1433 for redirect). Symptom: the connection times out rather than returning a login error. Fix: open the NSG rule on the MI subnet and the firewall on the client.
Expired or wrong-scope token - tokens issued for one resource cannot be used for another. The audience must be https://database.windows.net for SQL Database and https://managedinstance.azure.com (or the MI-specific resource) for SQL MI. Symptom: a working application suddenly fails with Failed to authenticate the user ... after a token cache expiry. Fix: ensure the token request uses the correct resource URI and is refreshed before each connection.
Guest user not allowed - B2B guest users cannot authenticate unless the admin (or the server identity) can read them in the directory. Symptom: a guest sees "principal not found" while a member user in the same group logs in fine. Fix: assign Directory Readers to the server identity or make the admin a group that includes the guest.
Service principal identity missing - connecting as a service principal or managed identity requires that the principal be granted access in the database (a contained user created from the principal's object ID or application name). Symptom: a new app deployment fails on first connection. Fix: CREATE USER [app-name] FROM EXTERNAL PROVIDER and grant it the required database roles.
The diagnostic query below lists which Entra logins exist on the server or instance so you can confirm the principal is provisioned: SELECT name, type_desc, sid
- FROM sys.server_principals
- WHERE type_desc IN ('EXTERNAL_LOGIN', 'EXTERNAL_GROUP');
A practical troubleshooting aid for Entra-only authentication: when the server is set to Entra-only, SQL logins silently fail even though their passwords are correct, and the error message does not always say "Entra-only mode". Check the server's Entra-only setting ( sys.database_principals will not show it - use az sql server ad-only-auth show ) before assuming a SQL login is corrupt.
Common Authorization Pitfalls
Authentication answers "who are you?"; authorization answers "what can you do?". Many failures reported as "can't log in" are actually authorization errors. A user with a valid Entra login but no user mapping in the target database receives a login error mentioning the database, not the login. The fix is to create a database user from the login and grant it a role. Similarly, an Entra group login works only if the database has a user mapped to that group (not to each member); the engine expands membership at login time using the server identity's directory read. A common exam pattern: a group login fails intermittently for a subset of users - the cause is that the server identity lost Directory Readers and cannot enumerate nested group membership.