Azure SQL with Managed Identity (user-assigned) failed to use against AAD

Question

I'm trying to utilize AAD identities to access Azure SQL server instead of SQL server users. I was following this article https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure. What I need to do based on Microsoft docs is to create contained users in the database. So I'm executing this from an Azure Function app.

CREATE USER [existing group in AAD] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [existing group in AAD]

The function app is able to log into SQL server because its service principal is set as Azure Active Directory admin for SQL server.

I got the exception:

Principal 'existing group in AAD' could not be resolved. Error message: 'AADSTS700016: Application with 
identifier '168d4e19-6718-4e50-8f96-c280e821ad6c' was not found in the directory 'my directory'. This can
happen if the application has not been installed by the administrator of the tenant or consented to by any
user in the tenant. You may have sent your authentication request to the wrong tenant.

So I assign system assign managed identity to the SQL server. Then I got:

Server identity does not have Azure Active Directory Readers permission. Please follow the steps here : https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal

I assigned Directory Readers permission to that identity as required and it started to work.

My problem is I need to use user assign managed identity, so I did the same but getting the first exception like there is no identity assigned to the SQL server.

Any ideas why system assigned identity works but not the user assigned?

Answer 1

This error might be caused by one of the following factors:

1. Make certain that you're using the Application ID as the Client ID.

2. This issue indicates that you made a mistake when configuring the Client ID OR tenant ID in your code; first, double-check that both are correct according to your Service Principle (tenant ID and Client ID).

The issue indicates that there is a discrepancy between the tenant where your app is registered and the tenant where you have indicated that your app is registered in either the application code or the portal. You must set the tenant ID in your application's code. This is usually found in the web.config or app settings files. Then, using the same tenant ID that you specified in your code, you must register the application on the portal. Also, make sure your application ID and client ID are the same on both sides, and that all of the variables in your app settings/web.config are the same as what you have in the portal.