LLI Wiki — My Digital Knowledge Base

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

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

  1. 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.
  2. 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.
  3. 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.
  4. Applications already on managed identity (or a service principal as a last resort), with a connection string using Authentication=Active Directory Managed Identity or Active 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:

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

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).

Mastodon LLIMastodon SocialFosstodon