--- name: entra-id-auth description: >- Takes an application identity to a passwordless connection to Azure SQL Database, and diagnoses it when that fails: sets the Microsoft Entra administrator, creates the database user for a managed identity or service principal, and grants roles. Use for "set up Microsoft Entra authentication for Azure SQL", "connect with a managed identity", "stop putting the database password in configuration", or "turn on Microsoft Entra-only authentication", and for the container once its MSSQL_AAD_ variables are set. Owns failures after a credential was evaluated: Msg 33134 "Principal could not be resolved", Msg 33131 "duplicate display name", 18456 "Login failed for user", 4060 "Cannot open database". What fails before that is diagnose-connection-errors; the connection code itself is connect-from-dotnet, connect-from-python or connect-from-typescript-and-node. --- # Microsoft Entra ID authentication for an application identity Passwordless connection is an ordered procedure whose failures do not arrive in order: steps succeed while achieving nothing, and the errors name the wrong component. Every flag below was read from the tool on 2026-09-03: Azure CLI 2.90.0, `sqlcmd` 1.10.0, `azd` 1.32.0, and the container behaviour was measured the same day. Cloud engine behaviour and message text are from Microsoft Learn, cited in `references/`. ## What this skill owns, and what it does not **Owns** the server-side identity configuration, the database user, the grant, the authentication keyword, and every failure about **who the caller is**. Route the rest. | Question | Skill | |---|---| | Retry, encryption, pooling, and whether the container has Entra ID configured at all | `connect-to-azure-sql` | | Drivers and per-language syntax | `connect-from-dotnet`, `connect-from-python`, `connect-from-typescript-and-node` | | The server, the database, the firewall rule | `provision-azure-sql-db` | | Transport, pre-login, certificates, timeouts, the `40xxx` gateway family | `diagnose-connection-errors` | | Which roles the application should hold | Define a custom role containing only the permissions the application uses | | Server-level Entra logins and fixed server roles | Outside this database-user workflow; use the current Microsoft Learn guidance | | Identity through a hosting service, and the deployment | `deploy-app-to-azure` | **The split, stated once.** `diagnose-connection-errors` owns failures before a credential is evaluated; this skill owns those after it: `18456`, `4060`, `33134`, `33131`, `37545`. A failure carrying no SQL error number is neither. ## 18456 and 4060 arrive after the credential Both read as "login failed", neither is a network problem, and no edit to the server name, encryption or timeout fixes either one. - **`18456`** means the credential was evaluated and refused. Naming `` it means the opposite of what it looks like: the token was **accepted**, and no matching principal exists in the database. - **`4060`** means the login is valid and has no user in **that** database. After a deployment that is almost always a missing database user: a login lives on the server, a user in the database, and deployment tooling creates neither. Both are answered by step 5, run on the user database. `40532` reads the same and is the gateway refusing before a database was reached, so it is `diagnose-connection-errors`. ## The order that works Steps 2, 5 and 7 are the ones that fail quietly. 1. **Set the Microsoft Entra administrator on the logical server.** Nothing works until it exists, and the CLI takes an object id rather than a name, so look it up first. ```bash az ad user list --display-name "" --query "[].{id:id, upn:userPrincipalName}" -o table az sql server ad-admin create -g -s \ --display-name "" --object-id ``` 2. **Decide whether the logical server needs its own identity.** It does **only** if a service principal, not a signed-in person, runs `CREATE USER`, the normal case for a pipeline. See [Msg 33134](#msg-33134-does-not-mean-what-it-says). 3. **Give the application an identity, and take its client id from the deployment, not the portal.** `azd` prints the client id among the environment values only if the infrastructure declared it as an output, so add that output rather than copying a GUID. ```bash azd env get-values -e az ad sp show --id --query "{appId:appId, objectId:id}" ``` `appId` is the client id step 5 wants. `objectId` is the enterprise application object id, which only `WITH OBJECT_ID` wants. They are not interchangeable. 4. **Connect to the user database with a Microsoft Entra authenticated connection.** Not `master`, and not a SQL login. ```bash sqlcmd -S .database.windows.net -d -U -G -l 30 ``` 5. **Create the database user.** `CREATE USER` adds the user to the **current** database, so the database in step 4 is the one that gets it. Run against `master` it is valid, succeeds, and reports nothing; the application then fails against the user database with correct code. ```sql CREATE USER [] FROM EXTERNAL PROVIDER; ``` 6. **Grant roles, and name them.** Add `db_ddladmin` only if the application applies schema. A grant made after the application first connected does not take until the host's token cache turns over, so the grant is correct and the application keeps failing. ```sql ALTER ROLE db_datareader ADD MEMBER []; ALTER ROLE db_datawriter ADD MEMBER []; ``` 7. **Write the connection string**, from [the per-driver table](#the-authentication-keyword-is-not-the-same-string-twice). 8. **Check it worked from the database**, below. ## Msg 33134 does not mean what it says ```output Msg 33134, Level 16, State 1, Line 1 Principal 'test-user' could not be resolved. Error message: 'Server identity is not configured. ...' ``` The first line sends an agent to check the principal name, which is almost always fine. **The fault is on the logical server.** A **Microsoft Entra user** running `CREATE USER` is impersonated and Graph queried with that user's permissions; an application cannot impersonate another application, so a **service principal** falls back to the **server identity**. With no server identity, or one that cannot read Graph, the lookup fails and the message blames the principal, which is why the same statement works by hand and fails in a pipeline. ```bash az sql server update -g -n -i az sql server show -g -n --query identity ``` **The flag is `-i`, long form `--assign_identity` with an underscore.** Measured 2026-09-03 on Azure CLI 2.90.0: the hyphenated `--assign-identity` exits with `unrecognized arguments`, so a script carrying it assigns nothing and the next `CREATE USER` still raises `33134`. Then, in order of tenant privilege needed: grant that identity Microsoft Graph read, either three application permissions or the broader `Directory Readers` role, both needing a `Privileged Role Administrator`; or put it in a role-assignable group holding `Directory Readers`, the production shape, because a group owner can then add servers; or skip the lookup with the third `CREATE USER` form below, the answer wherever that privilege is absent. Open [references/identity-errors.md](references/identity-errors.md) before attempting any of the three, because it names the exact Graph permissions. ## Three forms of CREATE USER, and when each is forced | Form | Looks the principal up | Use it when | |---|---|---| | `CREATE USER [] FROM EXTERNAL PROVIDER;` | Yes | The default. A person is running it, or the server identity is configured | | `CREATE USER [] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '';` | Yes | The display name is **not unique** in the tenant, so the plain form fails | | `CREATE USER [] WITH SID = , TYPE = E;` | **No** | No Graph permission is available, or Msg 33134 has to be worked around. Needs an engine that has Microsoft Entra configured, so on a bare container it raises `40530` | Agents paraphrase the third form into something that does not run, because the id must be converted to binary. Open [references/identity-errors.md](references/identity-errors.md) and copy the worked block there: `TYPE = E` covers users, applications and managed identities, `TYPE = X` groups. **Nothing validates that id**, so a wrong value creates a user no token will ever match and it reads back as a healthy row. For a **user or group** it is the **object id**; for a **service principal or managed identity** the **application (client) id**. **`WITH OBJECT_ID` is not the general fix and does not answer Msg 33134.** It exists for one problem: Microsoft Entra ID permits duplicate application display names and the engine requires a unique one. That produces a different error. ```output Msg 33131, Level 16, State 1, Line 4 Principal 'myapp' has a duplicate display name. ``` Three traps. **`WITH OBJECT_ID` is a modifier on `FROM EXTERNAL PROVIDER`, not an alternative to it**: on its own the statement is `Msg 37546, Can only specify object_id when creating user from external provider`, measured 2026-09-08. The object id must exist in this tenant or the statement fails with `Msg 37545`. And the **Object ID on an app registration is not the one on its service principal**: this clause wants the enterprise application one, `objectId` in step 3. ## A SQL-authenticated administrator cannot do any of this Learn is explicit: only Microsoft Entra users can create other Entra users in Azure SQL Database, and no SQL-authenticated user can, the server administrator included, which is why "I am the admin and it still fails" is a dead end. The permission needed is `ALTER ANY USER`, carried by `db_owner`, but no grant substitutes for the connection being Entra authenticated. The `WITH SID` form is the exception: it performs no external lookup. ## The name to put in the brackets A wrong name produces **`Msg 33130`**, `Principal '' could not be found or this principal type is not supported`, and reads as a permissions problem. Measured on two different logical servers, on 2026-09-05 and again on 2026-09-08: an unknown address, a bare guid and a non-existent application name all answered 33130. `Msg 33134` is a different path, above; `Msg 33131` is the duplicate display name, confirmed 2026-09-08 by `CREATE USER [Microsoft Graph] FROM EXTERNAL PROVIDER`. The one nobody guesses: a **system-assigned** identity on a deployment slot is `/slots/`, which is why an application works in production and fails in staging. Open [references/identity-errors.md](references/identity-errors.md) before typing a name you inferred, because it tables all five principal kinds. ## The authentication keyword is not the same string twice Every value below is plausible in every driver and each is valid in only one of them. | Stack | Passwordless default | Managed identity, explicit | User-assigned client id goes in | |---|---|---|---| | .NET, `Microsoft.Data.SqlClient` | `Authentication=Active Directory Default` | `Active Directory Managed Identity`, alias `Active Directory MSI` | `User Id=` | | Python, `mssql-python` | `Authentication=ActiveDirectoryDefault` | `Authentication=ActiveDirectoryMSI` | `UID=` | | Python, `pyodbc` on ODBC Driver 18 | **no such value exists** | `Authentication=ActiveDirectoryMsi` | `UID=`, object id on most hosts | | Node.js, `mssql` on `tedious` | `type: 'azure-active-directory-default'` | `azure-active-directory-msi-app-service`, `azure-active-directory-msi-vm` | `authentication.options.clientId` | | Java, the Microsoft JDBC driver | `authentication=ActiveDirectoryDefault` | `ActiveDirectoryManagedIdentity`, alias `ActiveDirectoryMSI` | `msiClientId`, or `user` | **In production, name the managed identity mode, not the default.** The default walks a chain of credential providers, which Microsoft warns costs response time, and can pick up a developer credential on a machine that has one. Two facts a plausible guess gets wrong. **ODBC has no `ActiveDirectoryDefault`** and spells managed identity `ActiveDirectoryMsi`, mixed case, so Python is two stories: `mssql-python` has a default mode and uses `ActiveDirectoryMSI`, `pyodbc` has neither. **In Node.js the client id is nested**, in `authentication.options.clientId`, not the connection `options` bag beside `encrypt`. Open [references/driver-auth-keywords.md](references/driver-auth-keywords.md) before writing one of these values, because each list there is the driver's complete accepted set and a value outside it is not a value. A driver that names its own set, such as `sqlcmd`, is faster to ask than to recall. **`Microsoft.Data.SqlClient` 7.0 needs the `Microsoft.Data.SqlClient.Extensions.Azure` package**, or every `Active Directory *` mode fails at run time; connection strings do not change. `Active Directory Password` is deprecated across the drivers and `[Obsolete]` in 7.0, because it rests on the resource owner password credentials grant. ```bash dotnet add package Microsoft.Data.SqlClient.Extensions.Azure ``` ## System-assigned or user-assigned Two decisions, confused because both use the same words. **The server identity** reads Microsoft Graph and matters only for step 2; a user-assigned one survives the server and holds the Graph permissions once for many. **The application identity** is what the application authenticates as, and changes the connection string **only if user-assigned**, which must name its client id because the host may carry several. ## On the Azure SQL Database container, the shape is login then user The container reads a certificate, the application (client) id and the tenant id from three environment variables set at creation. One command says which case you are in. ```bash docker inspect -f '{{range .Config.Env}}{{println .}}{{end}}' | grep -c '^MSSQL_AAD_' ``` `0` means Entra ID was never configured: an Entra login returns `18456`, `Login failed for user ''`, which is `connect-to-azure-sql`, not this skill, and the error below. `3` means `MSSQL_AAD_CLIENT_ID`, `MSSQL_AAD_PRIMARY_TENANT` and `MSSQL_AAD_CERTIFICATE_FILE_PATH` are set and the database side is yours. Learn documents that trio, and the statements below, for the SQL Server on Linux image and not for this one, so confirm the engine agreed rather than assuming: ```bash docker exec bash -c "grep Entra /var/opt/mssql/log/errorlog" ``` Expected: `Microsoft Entra ID authentication is enabled.` The principal is then a server login and a database user from that login, not a contained user: ```sql CREATE LOGIN [] FROM EXTERNAL PROVIDER; CREATE USER [] FROM LOGIN []; ``` ### Msg 40530 blames the batch, and the batch is fine In the `0` case the no-lookup form is refused too, by a message naming the wrong thing. Measured 2026-09-03 with no `MSSQL_AAD_` variables: ```output Msg 40530, Level 16, State 1, Line 1 The CREATE USER statement must be the only statement in the batch. ``` It **was** the only statement in the batch. `TYPE = X` fails identically, and so does every rewrite tried, `EXEC (@cmd)` and `GO` separation included, so a reader who trusts the text rewrites the batch forever and never succeeds. `CREATE USER [] WITHOUT LOGIN;`, the same batch shape, succeeds, which rules the batch out. **The fix is server configuration, not SQL**: the engine has no Microsoft Entra configuration and refuses the external principal types. Set the three variables and recreate the container, or run against Azure SQL Database. Open [references/identity-errors.md](references/identity-errors.md) before believing the number, because Microsoft Learn documents `40530` only as the batch rule and that disagreement is recorded there. Two things have no local equivalent, so a green local run proves nothing about either: the **server identity** and its Graph permission, which `Msg 33134` is about, and **Entra-only authentication** with its Azure Policy enforcement. ## Microsoft Entra-only authentication Enabling it disables SQL authentication at the **server** level, for every database and the server administrator too. Existing SQL logins are not removed and new ones can still be created; they simply cannot connect. ```bash az sql server ad-only-auth enable -g -n az sql server ad-only-auth get -g -n ``` **The administrator must be set first**, or enabling fails through every interface, and it cannot be removed until the setting is disabled again. Enabling also silently disables elastic jobs, SQL Data Sync, SQL Insights, `EXEC AS` for Entra group members and some change data capture paths. **Enforcing it takes two Azure Policy definitions, not one**: one evaluates a server at creation, the other the setting on an existing server. Open [references/entra-only-and-policy.md](references/entra-only-and-policy.md) before enabling this on a shared server or naming a policy in a plan, because it carries the full disabled-feature list, the role split, and the two definition ids. ## Check it worked Run the first two against the **user database**, never `master`. **1. The principal exists and its stored id is the identity you meant.** ```sql SELECT name, type, type_desc, CAST(CAST(sid AS varbinary(16)) AS uniqueidentifier) AS entra_id, DATALENGTH(sid) AS sid_bytes FROM sys.database_principals WHERE type IN ('E', 'X'); ``` Expected: `type_desc` is `EXTERNAL_USER` for an application or a person, `EXTERNAL_GROUP` for a group. `entra_id` must equal the client id the workload runs as; a row carrying the wrong one looks exactly like success. `sid_bytes` is `16` for a contained user, `18` for one from a server login. On a container with no `MSSQL_AAD_` variables it returns nothing: no `E` or `X` principal exists there. **2. The roles are the ones you named, and nothing wider.** ```sql SELECT r.name AS role_name, m.name AS member_name FROM sys.database_role_members rm JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id JOIN sys.database_principals m ON m.principal_id = rm.member_principal_id WHERE m.type IN ('E', 'X'); ``` Expected: exactly the roles from step 6. A `db_owner` or `CONTROL` grant nobody asked for is the usual leftover of a one-command tool. **3. On the cloud, the server side agrees.** ```bash az sql server ad-admin list -g -s -o table az sql server show -g -n --query identity ``` Expected: the administrator from step 1, and a non-null identity if step 2 said you needed one. Having run the command is not evidence it took. ## References Each of the three files under `references/` is linked from the section that needs it, at the point where the condition for opening it is stated. Tenant-side role assignment changes, so fetch the Learn articles rather than recalling them. ## Do not - Do not answer `Msg 33134` by editing the principal name. Check the server identity, its Graph permission, and that the assign command used `-i`. - Do not reach for `WITH OBJECT_ID` as a general fix. It answers `Msg 33131` and nothing else, and wants the enterprise application object id, not the app registration's. - Do not run `CREATE USER FROM EXTERNAL PROVIDER` on `master` and report the identity as configured. - Do not attempt the lookup forms from a SQL-authenticated connection, server administrator included. No grant makes those work. - Do not transpose an authentication keyword between drivers, and never write `ActiveDirectoryDefault` into an ODBC connection string. - Do not use `Active Directory Password` in any spelling, or leave a .NET 7.0 application on an `Active Directory *` mode without the extension package. - Do not put a client secret in a connection string on Azure, or grant `CONTROL ON DATABASE` because a tool did. Define a custom role containing only the permissions the application uses. - Do not enable Entra-only authentication on a shared server without naming what it turns off, or assign one Azure Policy definition and call the estate enforced. - Do not conclude a grant failed until the identity token cache has turned over. - Do not answer `Msg 40530` by rewriting the batch. On a bare container it means the engine has no Entra configuration. - Do not read a clean container run as evidence the cloud path works. The server identity, `Msg 33134` and Entra-only authentication have no local equivalent.