Azure SQL Database login fails with error 916 for least-privilege SQL user and managed identity, but admin login works

Question

I am trying to connect an Azure App Service application to Azure SQL Database using a least-privilege account.

The admin SQL login can connect successfully, but non-admin access fails.

Environment:

• Azure App Service connecting to Azure SQL Database
• Azure SQL Database, not SQL Managed Instance
• App Service and SQL Database are in the same Azure tenant/subscription
• Application works when using the SQL admin login
• Application fails when using least-privilege SQL users or managed identity

Errors seen:

• Error 18456: Login failed for user
• Error 916: The server principal is not able to access the database under the current security context
• Error 40532 when trying one contained-user login format

What I tried:

1. SQL login + database user

• • Created a SQL login in the logical SQL server/master context.
• • Created a matching database user in the target database.
• • Verified the SID in the server login and database user matched.
• • Granted CONNECT.
• • Added the user to a least-privilege database role.
• • Confirmed the role membership exists.
• • Result: login still fails with error 916 when connecting directly to the target database.

2. Contained database user

• Created a database-contained SQL user with password.
• Confirmed the user exists in the target database.
• Granted CONNECT.
• Added it to the runtime role.
• Checked that contained database authentication appears unavailable/disabled in this Azure SQL environment.
• Result: login failed with 18456 / 40532 depending on connection string format.

3. Managed identity

• Enabled managed identity on the App Service.
• Also tested user-assigned managed identity.
• Used a connection string with Active Directory Managed Identity authentication.
• Created the external user in the target database.
• Also tested creating the external user in master.
• Granted CONNECT.
• Added it to the runtime role.
• Verified that the token identity was the expected managed identity client/app/object identity.
• Result: Azure SQL still returns error 916 saying the server principal is not able to access the database under the current security context.

4. Other checks

• Stopped and restarted the App Service.
• Republished the application.
• Cleared auth cache where possible.
• Confirmed the application can open the database using the admin SQL login.
• Confirmed the same database name/server name are used.
• Confirmed firewall/network connectivity is not the issue because the admin login works.

Diagnostic result from the application:

• Connection string is configured.
• Server/database values are correct.
• Admin login canOpen = true.
• Least-privilege user / managed identity canOpen = false.
• Failure is 916 or 18456 depending on authentication type.

Question:

What is the correct Azure SQL setup for a least-privilege App Service connection?

Specifically:

1. For Azure SQL Database, should the least-privilege SQL user be a server login mapped to a database user, or a contained database user?
2. Why would error 916 occur when the database user exists, CONNECT is granted, and the user is a member of the required database role?
3. For App Service managed identity, does the external user need to exist in both master and the target database, or only the target database?
4. Are there Azure SQL settings or tenant/Entra configuration requirements that would cause admin login to work but least-privilege users/managed identity to fail?
5. Is there a known issue where SSMS can show the database but fails with 916 when expanding tables for a least-privilege user?

I am trying to avoid using the SQL admin login in production, so I need the correct least-privilege pattern for Azure App Service to Azure SQL Database.

Answer 1

Remove all those DENY. There are situations where DENY makes sense, but since DENY takes precedence over GRANT, you can easily shoot yourself in the foot. Since users by default have very few permissions beyond CONNECT permission, there is rarely a reason to deny permissions like the ones you have listed. And particularly not CONTROL, since DENY CONTROL means DENY of all permissions implied by CONTROL, and that are all permissions in the database. Including CONNECT.

Here is a quick repro:

CREATE USER testuser WITH PASSWORD = 'Don''t try this at home!'
go
EXECUTE AS USER = 'testuser'
PRINT USER   -- Prints
REVERT
go
DENY CONTROL TO testuser 
go
EXECUTE AS USER = 'testuser'    -- Error.
PRINT USER
REVERT
go
DROP USER testuser

Answer 2

Hello Mark S,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that your Azure SQL Database login fails with error 916 for least-privilege SQL user and managed identity, but admin login works.

Error 916 occurs when the identity authenticated by Azure SQL cannot access the named database under its current security context. Existing CONNECT and role grants do not solve the problem if they were granted to a different database principal or if the application selected a different managed identity. - https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-916-database-engine-error?view=sql-server-ver17, https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql. The failure is at the managed-identity selection and target-database user-mapping boundary.

Best method to resolve this is to:

• Keep the user-assigned managed identity assigned to the correct App Service or deployment slot.
• Verify the deployed Microsoft.Data.SqlClient version.
• For SqlClient 3.0 or later, place the UAMI client ID in User Id; SqlClient 2.1 instead expects the UAMI object ID.
• Create or validate the UAMI as a contained external user in the target database only.
• Specify the target database explicitly in the application connection string.
• Remove TrustServerCertificate=True.
• Restart the App Service to clear configuration and connection-pool state.
• Validate the resolved database identity with USER_NAME() after connection.
• If the corrected configuration still fails, enable Azure SQL auditing and review FAILED_DATABASE_AUTHENTICATION_GROUP before escalating. - https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup?view=azuresql, https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-analyze-audit-logs?view=azuresql

Use the following production connection string with Microsoft.Data.SqlClient 3.0 or later:

Server=tcp:sql-intranet-prod.database.windows.net,1433;
Database=sqldb-riga-balticvip-dev-import;
Authentication=Active Directory Managed Identity;
User Id=<UAMI-CLIENT-ID>;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

The managed identity should be created in the target database as follows:

CREATE USER [mi-supplierportal-sql]
FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [mi-supplierportal-sql];
ALTER ROLE [<application-runtime-role>]
ADD MEMBER [mi-supplierportal-sql];

Azure SQL Database supports contained Microsoft Entra users directly in the target database, and App Service supports passwordless Azure SQL access through system-assigned or user-assigned managed identities. A separate login in virtual master is not required for this application connection. - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver17, https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-azure-database, https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database

If the name mi-supplierportal-sql is not unique in Microsoft Entra ID, create an unambiguous SQL alias using the UAMI object ID:

CREATE USER [mi-supplierportal-sql-uami]
FROM EXTERNAL PROVIDER
WITH OBJECT_ID = '<UAMI-OBJECT-ID>';
GRANT CONNECT TO [mi-supplierportal-sql-uami];
ALTER ROLE [<application-runtime-role>]
ADD MEMBER [mi-supplierportal-sql-uami];

WITH OBJECT_ID is specifically intended to resolve nonunique Microsoft Entra principal names and should not replace the standard syntax when the display name is unique. - https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/authentication-microsoft-entra-create-users-with-nonunique-names?view=sql-server-ver17, https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/authentication-microsoft-entra-create-users-with-nonunique-names?view=sql-server-ver17

After applying the corrected connection string, restart the App Service and run:

SELECT
DB_NAME() AS database_name,
ORIGINAL_LOGIN() AS original_login,
SUSER_SNAME() AS server_principal,
USER_NAME() AS database_user,
CURRENT_USER AS current_database_user;

The expected result is:

database_name = sqldb-riga-balticvip-dev-import
database_user = mi-supplierportal-sql
current_database_user = mi-supplierportal-sql

Do not grant access to guest, enable TRUSTWORTHY, grant db_owner, or create an unnecessary login in master. Those actions weaken security and do not correct managed-identity selection or contained-user mapping. Use associated resource links for more reading and steps.

I hope this is helpful. Please! Do not hesitate to let me know if you have any other questions, steps or clarifications.

---

Please do not close the thread by upvoting and accepting the answer if any part of it is helpful.