Moving Azure SQL to Microsoft Entra-only authentication
Prerequisites, T-SQL inventory of remaining SQL accounts, detecting orphaned users and users tied to deleted Entra groups, enabling it (portal, PowerShell, az), rollback and pitfalls.
Moving an Azure SQL server to Microsoft Entra-only authentication removes the most common class of security problem I see: SQL passwords sitting in connection strings, scripts and config files. On paper it is a single switch. In practice the work is everything I check before flipping it, because the switch immediately disables every SQL account, including the server's SQL administrator.
Why do it
- No SQL secret to store, rotate or leak: applications use a managed identity, people use their Entra account (with MFA and conditional access).
- One place to cut access: the Entra account or group.
- Smaller attack surface: brute-forcing a SQL login has no target left.
How it works, per Microsoft's documentation: when the option is on, SQL authentication is disabled at server level for logins, users and the SQL admin. Existing SQL accounts are not deleted; they just can no longer connect.
flowchart TD
A["Inventory: SQL logins and users"] --> B["Move applications to managed identity"]
B --> C["Create contained Entra users (FROM EXTERNAL PROVIDER)"]
C --> D["Check orphans and deleted groups"]
D --> E["Enable Entra-only"]
E --> F{"Does a job break?"}
F -- "yes" --> G["Disable (rollback)"]
F -- "no" --> H["Clean up SQL accounts after a quiet period"]
Prerequisites
- A Microsoft Entra admin set on the server. Without one the option stays inactive and the API refuses to enable it. I prefer a group over a person.
- The right permissions. SQL Security Manager can toggle the option but cannot set the Entra admin; SQL Server Contributor can set the admin but not the option. Contributor or Owner can do both.
- Contained Entra users in each database:
CREATE USER [name] FROM EXTERNAL PROVIDER;plus the database roles needed. Azure SQL access does not go through Azure RBAC: the SQL DB Contributor role grants no data access. - Applications already on managed identity (or a service principal as a last resort), with a connection string using
Authentication=Active Directory Managed IdentityorActive Directory Default.
Inventory of what is still SQL-authenticated
Server level (master database):
SELECT name, create_date, is_disabled
FROM sys.sql_logins;
In each user database, the SQL users (login-mapped or with a password):
SELECT name, type_desc, authentication_type_desc, create_date
FROM sys.database_principals
WHERE type = 'S'
AND authentication_type_desc IN ('INSTANCE', 'DATABASE')
AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys');
For each row I write down who uses it (application, job, person). That is the slow part: the catalog tells you who exists, not who connects. I cross-check connection strings and audit logs before declaring an account dead.
Orphans and deleted Entra groups
Entra principals have type E (user, application or managed identity) and X (group). Their SID is not a random number: it is the Entra identifier, stored as binary. Which identifier exactly depends on the kind of account:
| Entra account | What the SID holds |
|---|---|
| User | its object ID |
| Group | its object ID |
| Application or managed identity (service principal) | its application ID (client ID), not its object ID |
And its length tells you how the user was created:
- 16 bytes: contained user (
CREATE USER ... FROM EXTERNAL PROVIDER); - 18 bytes: user created from a login (
CREATE USER ... FROM LOGIN). The 2 extra bytes are theAADEsuffix Azure appends; the first 16 are the same identifier.
Keeping only the first 16 bytes, one query covers both cases:
SELECT name, type, type_desc,
CAST(CAST(sid AS varbinary(16)) AS uniqueidentifier) AS EntraId,
CASE DATALENGTH(sid) WHEN 16 THEN 'contained' ELSE 'from a login' END AS origin
FROM sys.database_principals
WHERE type IN ('E', 'X');
Then I check on the Entra side that each identifier still exists, with the right command for each type:
# $rows = output of the query above (name, type, type_desc, EntraId)
foreach ($r in $rows) {
$id = "$($r.EntraId)"
if ($r.type -eq 'X') {
$null = az ad group show --group $id --query id -o tsv 2>$null # group: object ID
} else {
$null = az ad user show --id $id --query id -o tsv 2>$null # user: object ID
if ($LASTEXITCODE -ne 0) {
$null = az ad sp show --id $id --query id -o tsv 2>$null # application: application ID
}
}
if ($LASTEXITCODE -ne 0) { "Not found in Entra: $($r.name) ($($r.type_desc))" }
}
Before trusting the result, I run the loop on an account I know is alive. Without read access to the directory, az fails too, and a perfectly alive account would look deleted.
A deleted group raises no alert in SQL: the user stays in the database, and nobody benefits from it any more. I list them and drop them deliberately, never in bulk straight after the script.
On the classic SQL side, orphans are users whose SID has no matching login in master: same idea, compare the two SID lists. Contained users cannot become orphans that way.
Enabling it
Portal: the server's Microsoft Entra ID menu, the Microsoft Entra authentication only option, then save.
Enable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName sql-contoso-prd -ResourceGroupName rg-dba-prd
Get-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName sql-contoso-prd -ResourceGroupName rg-dba-prd
az sql server ad-only-auth enable --resource-group rg-dba-prd --name sql-contoso-prd
az sql server ad-only-auth get --resource-group rg-dba-prd --name sql-contoso-prd
T-SQL check: SELECT SERVERPROPERTY('IsExternalAuthenticationOnly'); returns 1 when the option is on.
Rollback
This is the big advantage: nothing is deleted, so going back is immediate.
az sql server ad-only-auth disable --resource-group rg-dba-prd --name sql-contoso-prd
SQL logins and passwords work again. I prepare this command before enabling, and I make the change outside batch windows.
Pitfalls
- Unsupported features with Entra-only on SQL Database, per the docs: elastic jobs, SQL Data Sync, change data capture in some cases, transactional replication from a managed instance or SQL Server, SQL Insights, and
EXEC ASfor Entra group members. Check the current list before switching. - The
CloudSA...account shown in the portal is generated at provisioning; it is not usable to connect. To reset a SQL admin password you must first disable Entra-only. - You cannot remove the Entra admin while the option is on.
- Managed identity token cache: a change in SQL permissions may only show up after the token you already hold expires.
- Unique Entra display names:
CREATE USER ... FROM EXTERNAL PROVIDERfails if two objects share a name. - Old tooling (scripts with
-U/-P, old drivers) breaks first. I search the repositories for it before the switch.
Lessons learned
The switch itself takes ten seconds; the preparation takes weeks. I do the inventory database by database, start with the test environment, and only delete SQL accounts after one full quiet cycle (a month, to catch rare jobs).