Azure Synapse Workspace authentication mode should be Microsoft Entra ID Only

What does the recommendation "Azure Synapse Workspace authentication mode should be Microsoft Entra ID Only" check?

Flags any Synapse workspace where the 'azureADOnlyAuthentication' property is not set to true, meaning the workspace still accepts SQL authentication (the local SQL admin login and any SQL-authenticated users) alongside Microsoft Entra ID. It looks only at the authentication mode of the workspace itself. It does not inspect which Entra users or groups are assigned, whether a Microsoft Entra admin has been set, how the dedicated and serverless SQL pools beneath the workspace are permissioned, or whether the workspace also restricts public network access. A workspace can therefore pass this control and still be over-permissioned in other ways.

Why does "Azure Synapse Workspace authentication mode should be Microsoft Entra ID Only" matter?

While SQL authentication is allowed, the workspace carries a standing local credential that lives outside Entra ID, so it sits outside conditional access, MFA, sign-in risk evaluation and central deprovisioning. A leaked or shared SQL admin password authenticates straight into your analytics data without any of those controls firing, and the resulting access leaves a thinner audit trail than an Entra sign-in. Because that login is not tied to a joiner-mover-leaver process, it tends to outlive the person who created it and quietly become a shared secret. Forcing Microsoft Entra ID only authentication retires the local credential entirely, so every connection is governed by one identity plane and revoking or disabling a person centrally genuinely cuts off their access to the warehouse rather than leaving a side door open.

How do I fix "Azure Synapse Workspace authentication mode should be Microsoft Entra ID Only"?

1. First set a Microsoft Entra admin on the workspace (Synapse workspace, Microsoft Entra ID blade, set the admin), because Entra-only authentication cannot be enabled until an Entra admin exists and would otherwise lock everyone out.
2. Enable the mode with 'az synapse ad-only-auth enable --workspace-name <workspace> --resource-group <rg>', or in infrastructure as code create a Microsoft.Synapse/workspaces/azureADOnlyAuthentications resource with azureADOnlyAuthentication set to true in your Bicep or ARM template.
3. To enforce it across the estate, assign the built-in policy by display name rather than a hardcoded GUID: pid=$(az policy definition list --query "[?displayName=='Synapse Workspaces should have Microsoft Entra-only authentication enabled'].name" -o tsv); az policy assignment create --name synapse-entra-only --policy "$pid" --scope <scope>.

# 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"