SQL servers should have a Microsoft Entra administrator provisioned

What does the recommendation "SQL servers should have a Microsoft Entra administrator provisioned" check?

Flags any logical Azure SQL server (the server that hosts Azure SQL Database) that has no Microsoft Entra administrator set, which means Microsoft Entra authentication is not available and the server falls back to SQL logins alone. The check is scoped to the server's administrator property, so it confirms an Entra admin user or group is named, not that anyone is actively using it. It does not apply to Azure SQL Managed Instance, which is covered by its own recommendation, and it does not require Entra-only authentication: a server can satisfy this control while SQL logins remain enabled alongside Entra, since enforcing Entra-only is a separate, stricter recommendation.

Why does "SQL servers should have a Microsoft Entra administrator provisioned" matter?

Without an Entra admin, the database is governed by SQL authentication, so credentials live in connection strings and scripts, sit outside your identity provider, and skip conditional access, MFA and central deprovisioning. When a member of staff leaves or a contract ends, a SQL login can keep working until someone remembers to drop it, and shared admin passwords spread quietly between tools and people. Provisioning an Entra admin brings the server under the same identity, lifecycle and access reviews as the rest of your estate, so a single revoked Entra account closes off database access everywhere at once.

How do I fix "SQL servers should have a Microsoft Entra administrator provisioned"?

1. In the Azure portal, open the SQL server, go to Settings then Microsoft Entra ID (formerly Azure Active Directory), select 'Set admin', choose a dedicated Entra group rather than an individual, and save. From the CLI: az sql server ad-admin create --resource-group <rg> --server <server-name> --display-name 'SQL Admins' --object-id <entra-group-object-id>.
2. In Bicep or Terraform, declare the administrator on the server resource (the Microsoft.Sql/servers administrators child resource with administratorType 'ActiveDirectory') so every new server is created with an Entra admin and the safe state is the default.
3. Audit the rest of the fleet by assigning the built-in policy by its exact display name rather than a hardcoded GUID: pid=$(az policy definition list --query "[?displayName=='An Azure Active Directory administrator should be provisioned for SQL servers'].name" -o tsv); az policy assignment create --name require-sql-entra-admin --policy "$pid" --scope /subscriptions/<sub-id>. This is an AuditIfNotExists policy, so it reports non-compliant servers but does not provision the admin for you.

# 1. Establish the Entra path FIRST: provision a SQL Entra admin (an Entra group is best).
gid=$(az ad group show --group "sql-admins" --query id -o tsv)
az sql server ad-admin create \
  --resource-group rg-data \
  --server-name sqlprodcust \
  --display-name "sql-admins" \
  --object-id "$gid"

# 2. Switch the Synapse workspace to Microsoft Entra ID Only (local SQL admin disabled).
az synapse workspace update \
  --resource-group rg-data \
  --name synprodanalytics \
  --use-microsoft-entra-only-authentication true

# 3. Disable the Cosmos DB account key ONLY after clients use a managed identity (NoSQL API).
az resource update \
  --resource-group rg-data \
  --name cosmosprodcust \
  --resource-type "Microsoft.DocumentDB/databaseAccounts" \
  --set properties.disableLocalAuth=true

# 4. Ratchet it shut: assign the built-in policy by its EXACT display name (look up its id, never hardcode a GUID).
pid=$(az policy definition list \
  --query "[?displayName=='An Azure Active Directory administrator should be provisioned for SQL servers'].name" -o tsv)
az policy assignment create \
  --name require-sql-entra-admin \
  --policy "$pid" \
  --scope "/subscriptions/00000000-0000-0000-0000-000000000000"