Issue:
Trying to set my AAD account as an Admin in Azure SQL Managed Instance from Azure Portal, although no admin is set as shown in the following image but keep getting the below error message:
"statusMessage": "{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceOperationFailure\",\"message\":\"The resource operation completed with terminal provisioning state 'Failed'.\",\"details\":[{\"code\":\"ServerPrincipalAlreadyExists\",\"message\":\"The requested server principal already exists on Managed Instance. Consider dropping the server principal and retrying operation.\"}]}}"
Reason:
This means that this AAD account may be still exists in the list of Managed Instance (MI) logins and may be not linked to any user (orphaned login) as shown in the below image:
Mitigation Steps:
- From your machine, Open SQL Server Management Studio (SSMS).
- Connect to your MI via SQL Authentication using Server Admin user.
- From your Object Explorer tree, click on Security.
- Click on Logins.
- Locate the problematic AAD login.
- Right-click on the AAD login and choose Delete as shown in the below image:
For more information about users and logins, please refer to the below article:
Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics
Microsoft
