Azure SQL Entra Authentication: Entra-Only Mode and Managed Identity

To replace SQL logins with Microsoft Entra ID on Azure SQL, you set a Microsoft Entra admin on the logical server or managed instance, create database users for Entra groups and for your applications' managed identities with CREATE USER ... FROM EXTERNAL PROVIDER, move every connection string to an Entra authentication mode, and then enable Microsoft Entra-only authentication. Once that setting is on, no SQL authentication account can connect, including the server admin, but the accounts aren't deleted, so you can roll back by turning the setting off.

Who this is for and what you will have at the end

This guide is for database administrators and platform engineers who want to stop managing SQL passwords for people and applications on Azure SQL Database and Azure SQL Managed Instance.

At the end you will have:

• A Microsoft Entra group configured as the server or instance admin.
• Database access granted to Entra groups instead of individual SQL logins.
• Applications connecting with managed identities and no secret in the connection string.
• Microsoft Entra-only authentication enabled and enforced with Azure Policy for new servers.

Entra ID authentication also brings Conditional Access and MFA to database sign-ins, which fits the identity-centric model described in the zero trust enterprise remote access architecture.

How Microsoft Entra authentication works in Azure SQL

There are two kinds of principals you'll create:

Microsoft Entra server principals are generally available for Azure SQL Database and Managed Instance. Contained users are the simplest choice for application access and also make disaster recovery easier, because the identity travels with the database instead of depending on logins in master on another server; see Azure SQL failover groups for why that matters.

The name you use in CREATE USER depends on the identity type:

Azure SQL requires principal names to be unique, but Entra ID allows duplicate display names. Microsoft has a WITH OBJECT_ID option, currently in preview, for the cases where display names collide.

Prerequisites

• A Microsoft Entra tenant that contains the users and groups, associated with the subscription that holds the server or instance.
• An Entra group for database administrators, for example sql-admins-contoso.
• Azure CLI 2.14.2 or later, or Az.Sql 2.10.0 or later, for the Entra-only commands.
• For the Entra-only setting: Owner, Contributor or SQL Security Manager on the server or instance.
• For Managed Instance: a Privileged Role Administrator to grant the instance identity directory read permissions.
• Applications on Microsoft.Data.SqlClient rather than the deprecated System.Data.SqlClient, or another driver that supports Entra authentication.

Step 1: Set the Microsoft Entra admin

Use a group, not an individual, so administrator access follows group membership.

Azure SQL Database

In the portal: open the logical server, select Microsoft Entra ID under Settings, select Set admin, pick the group, then Save. The change can take several minutes.

With the Azure CLI, pass the group's display name and object ID. You can read the object ID from the group's properties in the Microsoft Entra section of the portal.

az sql server ad-admin create \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --display-name "sql-admins-contoso" \
  --object-id "<group-object-id>"

With PowerShell:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "rg-sql-prod" `
  -ServerName "sql-contoso-prod" -DisplayName "sql-admins-contoso" -ObjectId "<group-object-id>"

For a group, DisplayName must be the display name; ObjectId is required when the display name isn't unique. The admin is stored as a user in master, so the setup fails if a user with the same name already exists there.

Azure SQL Managed Instance

On the instance, select Microsoft Entra admin (shown as Microsoft Entra ID in some portal views) under Settings, then Set admin and Save. The PowerShell equivalent is Set-AzSqlInstanceActiveDirectoryAdministrator, and the CLI equivalent is az sql mi ad-admin create.

Managed Instance also needs permission to read the directory so it can resolve users and group membership. On the instance's Microsoft Entra ID page, a banner offers to grant the Directory Readers role to the instance identity; only a Privileged Role Administrator or higher can complete it. Alternatively, assign the equivalent fine-grained Microsoft Graph permissions.

For Azure SQL Database, Directory Readers isn't needed for basic admin setup. Some scenarios, such as letting a service principal create Entra users, need the server identity to hold Microsoft Graph application permissions such as User.Read.All, GroupMember.Read.All and Application.Read.All.

Step 2: Inventory what uses SQL authentication today

Before you switch anything off, find the SQL logins and users that exist. In master on the server:

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login';

In each user database:

SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER';

For each principal, record the owner, the application or person using it, and the permissions it holds. Each one maps to an Entra replacement: a person becomes a member of an Entra group, an Azure-hosted application becomes a managed identity, and an application outside Azure becomes a service principal.

Step 3: Create Entra users and grant roles

Connect to the user database as a member of the admin group, for example in SSMS with Microsoft Entra MFA, and create users. You need at least ALTER ANY USER in that database.

-- People, through groups
CREATE USER [sql-readers-contoso] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sql-readers-contoso];
 
-- An App Service app with a system-assigned identity (name = app name)
CREATE USER [app-orders-prod] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-orders-prod];
ALTER ROLE db_datawriter ADD MEMBER [app-orders-prod];

Grant permissions to database roles rather than to individual users. Only grant db_ddladmin or similar to identities that run schema migrations.

If CREATE USER ... FROM EXTERNAL PROVIDER fails with error 33134, Azure SQL couldn't query Entra ID on your behalf. Microsoft notes this is usually Conditional Access blocking access to Microsoft Graph (application ID 00000003-0000-0000-c000-000000000000); adjust the policy or run the command as a user rather than a service principal.

Step 4: Move applications to managed identity

For an App Service app, enable a system-assigned managed identity. Its name in Entra ID is the app name:

az webapp identity assign \
  --resource-group rg-app-prod \
  --name app-orders-prod

Then change the connection string. With Microsoft.Data.SqlClient, the relevant Authentication values are:

For production, use the explicit managed identity mode:

Server=tcp:sql-contoso-prod.database.windows.net,1433;Database=ordersdb;Authentication=Active Directory Managed Identity;Encrypt=true;TrustServerCertificate=false;

For a user-assigned identity, add its client ID (not object ID; SqlClient uses the client ID from version 3.0):

Server=tcp:sql-contoso-prod.database.windows.net,1433;Database=ordersdb;Authentication=Active Directory Managed Identity;User ID=<client-id>;Encrypt=true;TrustServerCertificate=false;

Active Directory Default is convenient because the same string works on a developer workstation signed in with az login or Visual Studio and in Azure with the managed identity. Microsoft warns that it can slow the first connection while it tries each credential source, and recommends a specific mode in production.

From Microsoft.Data.SqlClient 7.0, the Entra modes need the Microsoft.Data.SqlClient.Extensions.Azure package installed at the same version as the core driver:

dotnet add package Microsoft.Data.SqlClient --version 7.1.0
dotnet add package Microsoft.Data.SqlClient.Extensions.Azure --version 7.1.0

Don't add Integrated Security=True to an Entra connection string. That keyword means Windows authentication, which is not the same as the Active Directory Integrated mode.

For applications outside Azure, use a service principal and, where the driver supports it, a certificate rather than a client secret. Don't use an Entra user account as a service account.

One operational detail: managed identity tokens are cached until they expire, so permission changes you make in the database after the app first connects might not take effect until the cached token expires.

Step 5: Enable Microsoft Entra-only authentication

Once no connections depend on SQL authentication, turn it off at the server or instance level. The Entra admin must already be set, or the command fails.

In the portal for SQL Database: open the logical server, select Microsoft Entra ID under Settings, check Support only Microsoft Entra authentication for this server, confirm, and Save. For Managed Instance, the checkbox is Support only Microsoft Entra authentication for this managed instance.

With the Azure CLI:

# Azure SQL Database
az sql server ad-only-auth enable --resource-group rg-sql-prod --name sql-contoso-prod
az sql server ad-only-auth get --resource-group rg-sql-prod --name sql-contoso-prod
 
# Azure SQL Managed Instance
az sql mi ad-only-auth enable --resource-group rg-sqlmi-prod --name sqlmi-contoso-prod

With PowerShell:

Enable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName "sql-contoso-prod" -ResourceGroupName "rg-sql-prod"
Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName "sqlmi-contoso-prod" -ResourceGroupName "rg-sqlmi-prod"

What changes:

• SQL authentication logins and users can no longer connect, including the server admin. They aren't removed.
• Entra principals with the right permissions can still create SQL logins and users, but those accounts can't connect either.
• You can't remove the Entra admin while Entra-only is enabled.
• Servers created with Entra-only enabled still get a generated admin name starting with CloudSA. It can't connect and isn't a break-glass account.

Check the features you use before you switch. With Entra-only enabled, Azure SQL Database doesn't support elastic jobs, SQL Data Sync, transactional replication pushing into the database, SQL Insights, or EXEC AS for Entra group member accounts, and change data capture has ownership caveats. Managed Instance doesn't support transactional replication, SQL Insights or EXEC AS for Entra group member accounts in this mode, and an Entra user who has access only through group membership can't own SQL Agent jobs.

Step 6: Enforce it for new servers

Assign the built-in Azure Policy definitions, which still use the old Azure AD name:

• Azure SQL Database should have Azure Active Directory Only Authentication enabled
• Azure SQL Managed Instance should have Azure Active Directory Only Authentication enabled

The default effect is Audit. With Deny, creating a server or instance without Entra-only fails, and the failure is recorded in the resource group's activity log. The policy is evaluated at creation; if someone with SQL Security Manager later disables the setting, the resource shows as non-compliant rather than being blocked.

Keep the right to disable Entra-only separate from day-to-day administration. SQL Server Contributor can set the Entra admin but can't change the Entra-only setting, while SQL Security Manager can change the setting but can't set the admin.

Verification

• SELECT SERVERPROPERTY('IsExternalAuthenticationOnly'); returns 1 when Entra-only is enabled.
• az sql server ad-only-auth get shows "azureAdOnlyAuthentication": true.
• A test connection with a SQL login in SSMS fails with the expected message.
• Each application connects successfully after deployment with no password in its configuration.
• The Activity log of the server shows who enabled or disabled the setting.

Troubleshooting

"Login failed for user 'username'. Reason: Azure Active Directory only authentication is enabled. Please contact your system administrator. (Microsoft SQL Server, Error: 18456)" A client is still using SQL authentication. Move it to an Entra mode, or temporarily disable Entra-only while you fix it.

"does not have authorization to perform action 'Microsoft.Sql/servers/azureADOnlyAuthentications/write'" The account is SQL Server Contributor or similar. Use an account with SQL Security Manager, Contributor or Owner.

Enabling Entra-only fails immediately. The Entra admin isn't set. Set it first; both changes can be saved together in the portal.

Error 33134 on CREATE USER ... FROM EXTERNAL PROVIDER. The message includes the Entra ID error. Access denied or an MFA enrollment requirement usually means Conditional Access is blocking access to Microsoft Graph; allow application 00000003-0000-0000-c000-000000000000 in the policy. If it says access between first-party applications must be handled via preauthorization, you ran the command as a service principal; run it as a user instead.

Managed identity connections fail to log in. Check that the database user name matches the identity's display name, that the user exists in the database named in the connection string, and that a user-assigned identity is referenced by its client ID.

Users can't sign in to Managed Instance through group membership. The instance identity lacks Directory Readers or equivalent Graph permissions.

Duplicate display names. Azure SQL needs unique principal names; create the user with the preview WITH OBJECT_ID option.

Checklist

• Entra group set as admin on every server and instance.
• Managed Instance identity granted Directory Readers.
• SQL logins and users inventoried and mapped to Entra replacements.
• Groups and managed identities created as database users with role-based permissions.
• Applications on Microsoft.Data.SqlClient with an explicit Entra authentication mode.
• Entra-only enabled, verified with SERVERPROPERTY, and enforced with Azure Policy.
• Feature limitations reviewed for replication, Data Sync, elastic jobs and Agent jobs.

References

• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure
• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication
• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-tutorial
• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-policy
• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-microsoft-entra-connect-to-azure-sql
• https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-security-configure
• https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication
• https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database
• https://learn.microsoft.com/en-us/cli/azure/sql/server/ad-admin