5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting

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:

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.