Login creation failed while using Service Principal

Question

We are trying to create login from AAD using a Service Principal.
We did try to use both those two entries:

CREATE LOGIN [login] FROM EXTERNAL PROVIDER;
CREATE USER [login] FROM EXTERNAL PROVIDER;

but each time we face the following error:

[ERROR]: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]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 (37353) (SQLExecDirectW)')

The Service Principal we are using has been granted access to Graph (as explained here: [https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql):

The same sql entries will work if executed from the portal using a standard user account.

Any suggestion on how to solve this issue?

Thanks

Answer 1

Hi @Francesco AMENTA

Your server identity has not been granted the Azure AD Directory Reader role.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi, thank you for your question at Q&A.

As this documentation indicates you will need some more permissions on the Graph API for a managed identity assigned to the Azure SQL Database instance to be able to lookup users, groups and applications.

The managed Identity needs at least User.Read.All, GroupMember.Read.All and Application.Read.All API permissions.