I failing to set the Microsoft Entra admin for my Azure SQL server "Failed to set the Microsoft Entra admin for serverName: xxxxxx"

Question

I failing to set the Microsoft Entra admin for my Azure SQL server "Failed to set the Microsoft Entra admin for serverName: xxxxxx"

jimzim 11

Using the Azure Portal, I checked in IAM and my logged in user is "Owner" for the Azure SQL server, but I am still unable to set the Microsoft Entra admin to my user, it keeps failing with the error "Failed to set the Microsoft Entra admin for serverName: xxxxxxxx" (where xxxx is the name of my server)

Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-17T11:12:35.9866667+00:00

Hi jimzim,
Greetings!
To set the Microsoft Entra admin for your logical server in the Azure portal, follow these steps:In the Azure portal Directories + subscriptions pane, choose the directory that contains your Azure SQL resource as the Current directory.

Search for SQL servers and then select the logical server for your database resource to open the SQL server pane.

On the SQL server pane for your logical server, select Microsoft Entra ID under Settings to open the Microsoft Entra ID pane.

On the Microsoft Entra ID pane, select Set admin to open the Microsoft Entra ID pane.

The Microsoft Entra ID pane shows all users, groups, and applications in your current directory and allows you to search by name, alias, or ID. Find your desired identity for your Microsoft Entra admin and select it, then click Select to close the pane.

At the top of the Microsoft Entra ID page for your logical server, select Save.

The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.

The process of changing the administrator might take several minutes. Then the new administrator appears in the Microsoft Entra admin field.

To remove the admin, at the top of the Microsoft Entra ID page, select Remove admin, then select Save. Removing the Microsoft Entra admin disables Microsoft Entra authentication for your logical server.

I would request you to refer the below mentioned links for more information
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-portal

I hope this information helps. Please do let us know if you have any further queries. 
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-18T08:45:10.2733333+00:00

Hi James Railton,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
jimzim 11 Reputation points

2025-03-19T06:29:50.9333333+00:00

Hi Adithya, apologies for my delayed response, no notification of your post was received.

Thanks for the info. What you described is precisely what I had tried to do and unfortunately the problem persists per below screenshot
Saraswathi Devadula 1,705 Reputation points Microsoft External Staff

2025-03-19T10:48:35.98+00:00

Hello **James R
**
Following our discussion over the remote session, we noticed that the user is having global administrator role already. But still, you are unable to set the Entra Admin for the Azure SQL server.

Please be informed that the Microsoft Entra admin is stored in the server's master database as a user (database principal). Since database principal names must be unique, the display name of the admin can't be the same as the name of any user in the server's master database. If a user with the name already exists, the Microsoft Entra admin setup fails and rolls back, indicating that the name is already in use.

Kindly please try to change the username and retry the operation to set the admin for the SQL server.

Please refer the below document for better understanding,

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-portal#azure-sql-database-and-azure-synapse-analytics

Please do let me know if you have any further concerns. I am happy to address your queries.

Thank you.
jimzim 11 Reputation points

2025-03-19T13:26:26.5766667+00:00

Hi Saraswathi.

Unfortunately Im no closer to winning, despite having identified the problem.

As Razza guessed, there is an existing SQL server authentication LOGIN with my same email address which already exists. It happens to be the administrator user which Im guessing I setup right in the beginning before I knew (anything) about Microsoft Entra ID

The problem now is that I cannot rename, delete etc. that user because I need to login as that user in order to get access to the Azure SQL server instance, and it wont allow me to make changes to the user that i'm logged in as.

Do you have any suggestions?
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-20T08:46:33.48+00:00

Hi jimzim,
I understand this might be an additional task, but I would greatly appreciate it if you could either create a new user with a different name in Entra Users or update the current username.

2 answers

Your answer

Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-17T11:12:35.9866667+00:00

Hi jimzim,
Greetings!
To set the Microsoft Entra admin for your logical server in the Azure portal, follow these steps:In the Azure portal Directories + subscriptions pane, choose the directory that contains your Azure SQL resource as the Current directory.

Search for SQL servers and then select the logical server for your database resource to open the SQL server pane.

On the SQL server pane for your logical server, select Microsoft Entra ID under Settings to open the Microsoft Entra ID pane.

On the Microsoft Entra ID pane, select Set admin to open the Microsoft Entra ID pane.

The Microsoft Entra ID pane shows all users, groups, and applications in your current directory and allows you to search by name, alias, or ID. Find your desired identity for your Microsoft Entra admin and select it, then click Select to close the pane.

At the top of the Microsoft Entra ID page for your logical server, select Save.

The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.

The process of changing the administrator might take several minutes. Then the new administrator appears in the Microsoft Entra admin field.

To remove the admin, at the top of the Microsoft Entra ID page, select Remove admin, then select Save. Removing the Microsoft Entra admin disables Microsoft Entra authentication for your logical server.

I would request you to refer the below mentioned links for more information
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-portal

I hope this information helps. Please do let us know if you have any further queries. 
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-18T08:45:10.2733333+00:00

Hi James Railton,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
jimzim 11 Reputation points

2025-03-19T06:29:50.9333333+00:00

Hi Adithya, apologies for my delayed response, no notification of your post was received.

Thanks for the info. What you described is precisely what I had tried to do and unfortunately the problem persists per below screenshot
Saraswathi Devadula 1,705 Reputation points Microsoft External Staff

2025-03-19T10:48:35.98+00:00

Hello **James R
**
Following our discussion over the remote session, we noticed that the user is having global administrator role already. But still, you are unable to set the Entra Admin for the Azure SQL server.

Please be informed that the Microsoft Entra admin is stored in the server's master database as a user (database principal). Since database principal names must be unique, the display name of the admin can't be the same as the name of any user in the server's master database. If a user with the name already exists, the Microsoft Entra admin setup fails and rolls back, indicating that the name is already in use.

Kindly please try to change the username and retry the operation to set the admin for the SQL server.

Please refer the below document for better understanding,

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-portal#azure-sql-database-and-azure-synapse-analytics

Please do let me know if you have any further concerns. I am happy to address your queries.

Thank you.
jimzim 11 Reputation points

2025-03-19T13:26:26.5766667+00:00

Hi Saraswathi.

Unfortunately Im no closer to winning, despite having identified the problem.

As Razza guessed, there is an existing SQL server authentication LOGIN with my same email address which already exists. It happens to be the administrator user which Im guessing I setup right in the beginning before I knew (anything) about Microsoft Entra ID

The problem now is that I cannot rename, delete etc. that user because I need to login as that user in order to get access to the Azure SQL server instance, and it wont allow me to make changes to the user that i'm logged in as.

Do you have any suggestions?
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-20T08:46:33.48+00:00

Hi jimzim,
I understand this might be an additional task, but I would greatly appreciate it if you could either create a new user with a different name in Entra Users or update the current username.

Answer 1

PratikLad 720 Microsoft External Staff

Hello James R

As Saraswathi Devadula mentioned in the comment the Microsoft Entra admin is stored in the server's master database as a user (database principal). Since database principal names must be unique, the display name of the admin can't be the same as the name of any user in the server's master database. If a user with the name already exists, the Microsoft Entra admin setup fails. But you have issue with login as that user in order to get access to the Azure SQL server instance.

To work around the issue, you can create a Service principal (App registration) to temporarily login into the database and rename or drop the existing SQL user.

First create an app registration from Microsoft Entra Id >> App Registration >> New Application.

enter image description here

After creating application create client secret and store it to use it as password.

enter image description here

Then Add this Service principal as Microsoft Entra admin.

enter image description here

Login With Service Principal authentication, enter the client ID in the User name field and the secret in the Password field.

enter image description here

After successful login alter the username that causing error while adding Microsoft Entra admin.


ALTER USER [******@domain.com] WITH NAME = [******@domain.com1];

GO

This command will rename the azure SQL user.

Now, you can remove the service principal as admin and set the appropriate user as Entra admin.

enter image description here

jimzim 11 Reputation points

2025-03-20T09:21:24.3633333+00:00

Hi Adithya. To update the current username, I need to do that in SSMS correct? In which case its not possible since you cant change a user who you are logged in as -- I tried that.

To create new user in entra users would be a great shame, since the current one (me) is in fact the global administrator and we'd prefer to keep it as such. Surely Microsoft thought about this and have a way around it?

Many thanks
Erland Sommarskog 120.2K Reputation points MVP

2025-03-20T22:01:06.7566667+00:00

You may want to look at https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-microsoft-entra-create-users-with-nonunique-names?view=azuresql and see if that could help you. I'm not entirely sure that this help you in this particular scenario, but you could try it.

Then again, while a bit of steps to take, I think the actions suggested by Pratik are perfectly adequate, so you might as well try it directly.
jimzim 11 Reputation points

2025-03-21T07:39:54.15+00:00

@PratikLad thank you for your suggestion. Everything went very well until I attempted to rename the old user at which time I received the error:

Cannot alter the user 'xxxxxxx', because it does not exist or you do not have permission.

The user Im trying to rename does exist, I can see it (as well as my service principle which was added for this exercise) listed under master db users, so I have to assume that the service principle which is now the Microsoft Entra admin, somehow does not have permission (I cant conceive how thats possible, but I have to believe the error)
PratikLad 720 Reputation points Microsoft External Staff

2025-03-21T09:53:04.4233333+00:00
Hello James R

You need to Alert the user from master database. make sure you are executing query in master database

or you can execute below query to check principal have alter permission or not

SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;
jimzim 11 Reputation points

2025-03-21T11:05:25.8133333+00:00

@PratikLad confirmed, I am executing the command on the master db.

Also just to confirm, see below image showing the 'temporary' user on master database which im logged in with, which is the service principle currently assigned as Microsoft Entra administrator for the SQL server

Also here is the result of the query showing user privs. N.B. masked is the user im trying to change, 'temporary' is the service principle im logged in with.
PratikLad 720 Reputation points Microsoft External Staff

2025-03-24T03:59:16.7133333+00:00

Hello James R, are you executing on Correct user?
jimzim 11 Reputation points

2025-03-24T07:50:29.54+00:00

@PratikLad morning. Yes I believe so, logging in as the service principle created (called 'temporary') which is currently the Microsoft Entra admin for the SQL server -- is that correct?
PratikLad 720 Reputation points Microsoft External Staff

2025-03-24T08:03:20.86+00:00

yes, it is correct but the user you are trying to alter is correct make sure there should not be any extra spaces while querying
jimzim 11 Reputation points

2025-03-24T08:56:19.04+00:00

I can confirm the user in the ALTER USER [xxxx] WITH NAME = [yyyy] statement is precisely as it appears in the list of users in SSMS -- there are no leading or trailing spaces/characters.

For the avoidance of doubt, running CREATE USER [xxxx] (for the same user as we are trying to rename) gives the error 'User, group, or role 'xxxx' already exists in the current database.' so it definitely does already exist.
PratikLad 720 Reputation points Microsoft External Staff

2025-03-25T04:24:37.8133333+00:00

In Azure SQL, a login is different from a user. A login is used to connect to the server, while a user is a database principal. You need to map a login to a database user.
jimzim 11 Reputation points

2025-03-25T08:14:14.4966667+00:00

@PratikLad thanks for the info, although Im a little confused now.

We want to rename the existing USER in the Master db whose email (lets call that a @b.com) is the same as mine correct? That is so that I can make my Azure user (a @b.com) the Microsoft Entra admin for the server.

I can see that there is also a LOGIN for a @b.com but am not sure how that affects things.

Further to the above, the current db_owner for all existing databases is a @b.com which im sure has an impact as well.
PratikLad 720 Reputation points Microsoft External Staff

2025-03-25T08:50:12.65+00:00
Drop the login you want to delete and then try to recreate it

DROP LOGIN login_name; ----e.g.
jimzim 11 Reputation points

2025-03-25T09:59:59.9333333+00:00

The DROP statement fails with the following:

Login 'xxxx' owns one or more database(s). Change the owner of the database(s) before dropping the login.

But I cannot change the owner of master db because the following fails:

ALTER AUTHORIZATION ON DATABASE::[master] TO [temporary];

Cannot change the owner of the master, model, tempdb or distribution database.

Reminder: my service principle which is the Microsoft Entra admin for the SQL instance is named 'temporary'

I cannot change the owner of any of the other databases because the same command fails with this error:

Principal 'temporary' could not be resolved. Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity" (https://aka.ms/sqlaadsetup)'
PratikLad 720 Reputation points Microsoft External Staff

2025-03-25T11:01:42.6366667+00:00
change the login name using below command

ALTER LOGIN Mary5 WITH NAME = John2;
jimzim 11 Reputation points

2025-03-25T11:57:36.85+00:00

Cannot alter the login 'xxxx', because it does not exist or you do not have permission.

This is of course whilst I'm logged in with the service principle which is Entra admin.
Erland Sommarskog 120.2K Reputation points MVP

2025-03-25T16:04:30.59+00:00

Pratik, have you actually tested that the steps you are suggesting to Jimzin actually works?

Early on in the thread, I had ideas along the same line as yours, and created an extra admin login and then tried to rename my original admin login, but I could not get it work, and hence, I did not post anything.

On Azure SQL Database you don't have full permissions in master, even if you are logging in with your admin login. (As in difference to on-prem SQL Server of Azure SQL Managed Instance.) So it maybe that Jimzim cannot sort out this unfortunate situation on his own, but you may have to help him from the backend.

Or Jim may just have to accept the situation, and use another mail account as the Entra admin. I like to point out that SQL Logins normally do not take the form of a email address.

In any case, Pratik, try to confirm that the steps you have suggested are working, because right now this thread seems to be going around in circles.
PratikLad 720 Reputation points Microsoft External Staff

2025-03-26T03:33:59.5266667+00:00

Hi Erland Sommarskog,

I have tested all the steps in my environment and then posted that as an answer.
Erland Sommarskog 120.2K Reputation points MVP

2025-03-26T05:16:19.3033333+00:00

Good! Let's hope that Jim can get this working.
Oury Ba-MSFT 20,716 Reputation points Microsoft Employee

2025-04-03T19:02:31.1533333+00:00

jimzim Please share the information asked over PM.

Regards,

Oury
jimzim 11 Reputation points

2025-04-04T06:20:59.3733333+00:00

@Oury Ba-MSFT I have responded. Thanks
jimzim 11 Reputation points

2025-04-10T07:03:21.14+00:00

Okay, thanks @Oury Ba-MSFT I appreciate your efforts.

Answer 2

jimzim, James R,

User's image

Authorize server and database access using logins and user accounts - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn The name of the server administrator can't be changed after the server has been created.

You can create a new server as a geo replica for this server, replicate all the databases and then failover to that server? Once the failover is completed, you can break the link, drop the server, and start using the new one as your server. See Failover groups overview & best practices - Azure SQL Database | Microsoft Learn; on the secondary server you can configure the right Entra administrator and use a different account for SQL administrator.

Share via

I failing to set the Microsoft Entra admin for my Azure SQL server "Failed to set the Microsoft Entra admin for serverName: xxxxxx"

2 answers

Your answer