Passwordless Azure SQL: Entra Authentication and Managed Identity

Most Azure SQL deployments still use a connection string that looks something like this:

Server=myserver.database.windows.net;Database=mydb;User Id=appuser;Password=S3cr3tP@ssw0rd;

That password has to live somewhere — an app setting, a Key Vault secret, a .env file that shouldn’t be committed but sometimes is. It has to be rotated, and the rotation has to be coordinated across every service that uses it. When a developer leaves or a credential leaks, you’re scrambling.

Microsoft Entra authentication with managed identity removes the password entirely. The connection string shrinks to:

Server=myserver.database.windows.net;Database=mydb;Authentication=Active Directory Default;

No credentials. The application authenticates as itself using a token issued by Entra ID. This post walks through the full setup.

SQL Authentication vs. Managed Identity

How It Works

Azure resources like App Services, Container Apps, Azure Functions, and VMs can be assigned a managed identity — an Entra service principal that Azure manages automatically. You never see or handle the credentials; Azure rotates them behind the scenes.

When your app opens a SQL connection, the Azure SDK acquires a short-lived Entra token for the managed identity and presents it to Azure SQL instead of a username and password. Azure SQL validates the token against Entra and grants access based on the database roles assigned to that identity.

┌─────────────────────────────┐
│         App Service         │
│   (System Managed Identity) │
└─────────────┬───────────────┘
              │  1. Request token
              ▼
┌─────────────────────────────┐
│      Microsoft Entra ID     │
└─────────────┬───────────────┘
              │  2. Return short-lived access token
              ▼
┌─────────────────────────────┐
│      Azure SQL Database     │
│                             │
│  3. Validate token → map to │
│     database user & roles   │
└─────────────────────────────┘

Step 1: Enable Entra Authentication on Azure SQL

Azure SQL supports a single Entra admin at the server level — that admin is always an Entra principal, regardless of whether SQL authentication is also enabled. The admin can be either a single user or an Entra group. A group is strongly recommended: you manage membership in Entra without ever touching the SQL server configuration, and you avoid a single person’s account becoming a hard dependency. Assigning a single user works but means if that account is disabled or deleted, you lose your admin path.

At this point, both Entra and traditional SQL authentication are active — your existing SQL logins continue to work. That’s a perfectly valid long-term configuration if you have tools or services that aren’t ready for Entra auth yet.

If you want to go further and disable SQL authentication entirely, enable Entra-only mode using one of the methods below.

Note: Once Entra-only is enabled, the SQL admin password no longer works. Make sure your Entra admin account is accessible before flipping this switch, or you can lock yourself out.

Portal

Navigate to your Azure SQL Server (not the database, the server). Under Settings, select Microsoft Entra ID and set an Entra admin (a user or group from your tenant). Save. To disable SQL auth, enable Microsoft Entra authentication only and save again.

Azure CLI

# Set the Entra admin
az sql server ad-admin create \
  --resource-group <resource-group> \
  --server <server-name> \
  --display-name <admin-user-or-group-name> \
  --object-id <entra-object-id>

# Optionally disable SQL authentication entirely
az sql server ad-only-auth enable \
  --resource-group <resource-group> \
  --name <server-name>

Bicep

resource sqlServer 'Microsoft.Sql/servers@2025-01-01' = {
  name: sqlServerName
  location: location
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      login: entraAdminName
      sid: entraAdminObjectId
      tenantId: tenant().tenantId
      azureADOnlyAuthentication: true  // set false to allow both Entra and SQL authentication.
    }
  }
}

Step 2: Enable a Managed Identity on Your App

If you’re using a user-assigned identity (useful when multiple services share the same database permissions), create the identity as a standalone resource and assign it to the app. Otherwise a system-assigned identity works fine and requires no extra resources.

Portal

Navigate to your App Service or Azure Function. Under Settings → Identity, toggle System assigned to On and save.

Azure CLI

# System-assigned identity
az webapp identity assign \
  --resource-group <resource-group> \
  --name <app-name>

# User-assigned identity (create first, then assign)
az identity create \
  --resource-group <resource-group> \
  --name <identity-name>

az webapp identity assign \
  --resource-group <resource-group> \
  --name <app-name> \
  --identities <identity-resource-id>

For Azure Functions, replace az webapp with az functionapp.

Bicep

// System-assigned
resource appService 'Microsoft.Web/sites@2025-03-01' = {
  name: appName
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  // ...
}

// User-assigned
resource userIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2024-11-30' = {
  name: identityName
  location: location
}

resource appService 'Microsoft.Web/sites@2025-03-01' = {
  name: appName
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${userIdentity.id}': {}
    }
  }
  // ...
}

Step 3: Create the Database User

To run the SQL below you need to connect to the database as the Entra admin you set in Step 1. SSMS offers several Entra options in the authentication dropdown — here’s what each one does and when to use it.

Connecting via SSMS

Microsoft Entra MFA — Prompts for your Entra credentials and MFA. This is the right choice for most people doing day-to-day admin work. Use your full UPN (you@domain.com) as the username.

Microsoft Entra Integrated — Signs in silently using your current Windows session if your machine is Entra-joined or hybrid-joined. No credential prompt. Convenient on managed corporate devices.

Microsoft Entra Password — Entra username and password with no MFA step. Avoid this unless you have a specific reason — it bypasses MFA and isn’t suitable for accounts that have Conditional Access policies enforcing it.

Microsoft Entra Managed Identity — For when SSMS itself is running on an Azure VM with a managed identity assigned. Rarely needed for typical admin work.

Microsoft Entra Service Principal — Authenticates as an app registration using a client ID and secret or certificate. Useful for automated tooling and pipelines that need to run SQL commands without a user present.

Microsoft Entra Default — Uses a credential fallback chain (integrated → environment variables → Azure CLI → Visual Studio) similar to DefaultAzureCredential. Useful if you want SSMS to pick up whatever credential is already active locally.

For most admins connecting interactively, Microsoft Entra MFA is the right pick. Set the server name to the full FQDN (myserver.database.windows.net) and the username to your UPN before connecting.

Finding the identity name

The name used in CREATE USER must match the identity’s display name in Azure exactly. Where to find it depends on the identity type:

System-assigned identity — The name is simply the App Service or Function App resource name. To confirm, go to the app in the portal, navigate to Settings → Identity → System assigned, and the name shown at the top of the blade is what you use. The Object (principal) ID on that same page is the identity’s unique identifier in Entra — useful for auditing or role assignments but not needed for the SQL statement itself.

User-assigned identity — Navigate to the managed identity resource directly (it lives in a resource group as a standalone resource). The Name field on the Overview page is what goes in the SQL statement. The Overview also shows the Object (principal) ID and Client ID — the Client ID is sometimes needed when configuring the SDK explicitly. When only a single managed identity is available, DefaultAzureCredential can authenticate automatically. If multiple user-assigned managed identities are assigned to the resource, you’ll need to specify which identity to use — typically by providing its Client ID.

If you’re not sure which identity your app is using, go to Settings → Identity on the app — the System assigned tab shows whether it’s enabled, and the User assigned tab lists any user-assigned identities attached to it.

Create the user

Every Entra principal that needs database access — managed identities, individual users, or groups — must be explicitly created as a database user. Entra membership alone does not grant SQL access; the CREATE USER statement is always required.

The strongly preferred approach is to create database users for Entra groups rather than individuals. Add people to the group in Entra and they automatically inherit the database permissions — no SQL changes needed. Remove someone from the group and access is revoked immediately. Managing individual users directly in the database means a SQL change every time someone joins or leaves, which is easy to let drift.

Connect to the database as the Entra admin you set in Step 1, then run:

-- For a managed identity or individual user
CREATE USER [your-app-service-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-app-service-name];
ALTER ROLE db_datawriter ADD MEMBER [your-app-service-name];

-- For an Entra group (preferred for human access)
CREATE USER [your-entra-group-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-entra-group-name];

For managed identities, use the resource name of the App Service or user-assigned identity — the name must match exactly. For groups and users, use the display name as it appears in Entra. This is a common source of confusion: if the name doesn’t match, CREATE USER FROM EXTERNAL PROVIDER will fail.

Grant only the roles each principal actually needs. If the app only reads data, skip db_datawriter. If it needs to run stored procedures, add EXECUTE on the relevant schemas.

Step 4: Update the Connection String

Remove the User Id and Password fields. Add Authentication=Active Directory Default:

Server=myserver.database.windows.net;Database=mydb;Authentication=Active Directory Default;

Active Directory Default uses the DefaultAzureCredential chain from the Azure Identity SDK, which automatically picks up the managed identity when running in Azure.

The exact implementation varies by language and SDK, but the pattern is the same across all of them: remove the username and password from the connection string, set the authentication mode to the Entra/Active Directory equivalent your driver supports, and ensure your project references the Azure Identity library alongside the database driver. The SDK handles token acquisition and renewal transparently from that point on.

Note: The connection string parameter uses the legacy Active Directory naming (e.g., Authentication=Active Directory Default) even though the portal now shows “Microsoft Entra.” Both refer to the same identity system — don’t let the naming difference trip you up.

Verifying It Works

After deploying, confirm the app is actually authenticating via managed identity — not falling back to a cached SQL credential or a leftover connection string somewhere.

1. Remove the credentials completely — Strip User Id and Password from the connection string entirely. If the app still connects, there’s no SQL credential involved.

2. Restart the app — Force a clean start so there’s no chance of a cached connection from before the change. In App Service, a full restart (not just a slot swap) flushes the connection pool.

3. Confirm the app connects successfully — Check application logs for successful database operations. An authentication failure at this point means either the connection string is still wrong, the database user wasn’t created, or the identity name didn’t match.

4. Query the active sessions in the database — Connect via SSMS and run:

SELECT host_name, program_name, login_name, status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID();

Managed identity connections appear with a GUID-format login_name — specifically the service principal’s object ID and tenant ID joined with @ (e.g., 11533d53-2eed-40fa-8b87-398b016388a6@0ee7c410-2eb6-4ff2-b28d-ecbed4b032e1). This is the managed identity’s Entra representation, not the display name of the App Service. If you see a plain SQL username in login_name, the app is still using SQL authentication.

5. Confirm by removing access — Temporarily remove the database user or drop the role membership, then restart the app and attempt a database operation. If it fails with an authorization error, the app is genuinely relying on the managed identity. Restore the role membership when done.

Closing Thoughts

Managed identity with Entra authentication eliminates an entire category of credential management work. No password rotation, no secrets in app settings, no risk of a connection string ending up in a log file or a git commit. The app authenticates as itself, and access is controlled through database roles like any other principal.

The setup is a bit more involved than creating a SQL login, but it only has to be done once. After that, the only ongoing maintenance is updating database role memberships when access requirements change — which is the kind of access control you should be doing anyway.

For greenfield Azure SQL deployments, there’s no reason to use SQL authentication at all.