2

I am not able to add an active directory administrator to a newly created SQL Managed Instance in the Azure portal or with the Cloud Shell.

When I attempt in the Azure portal, the operation fails with the following message in the activity log:

Operation name: Update Administrator of Azure SQL Managed Instance. Create

Time stamp: Sun Nov 17 2019 xxxxx

Event initiated by: Admin A

Error code: ResourceOperationFailure

Message: The resource operation completed with terminal provisioning state 'Failed'.

When running the following command:

Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "myResourceGroupName" -InstanceName "myInstaceName" -DisplayName "Chris Green" -ObjectId "xxx-xxxx-xxx-xxxx"

I'm receiving the following error...

Set-AzSqlInstanceActiveDirectoryAdministrator : Long running operation failed with status 'Failed'. Additional Info:'The operation timed out and automatically rolled back. Please retry theoperation.'
At line:1 char:1
+ Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "NWN ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : CloseError: (:) [Set-AzSqlInstanceAc\u2026ectoryAdministrator], CloudException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.InstanceActiveDirectoryAdministrator.Cmdlet.SetAzureSqlInstanceActiveDirectoryAdministrator

I was able to assign an active directory admin to a previously created SQL managed instance in the same subscription. Some other things I tried:

  • Tried with several different users with higher level permissions
  • Tried using the same user who is currently the admin of the other managed instance
  • Restarted the instance by scaling up and then back down

How can I further troubleshoot this problem? Could there potentially be more specific information somewhere in the logs? Are there any SQL/Powershell commands I could to further diagnose the failure?

Here is a link to the article I was using as a reference: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure#provision-an-azure-active-directory-administrator-for-your-managed-instance

Cza102282
  • 33
  • 6
  • Is that you want to add more that one AD user or service principal to Azure SQL managed Instance as Azure AD admin? – Jim Xu Nov 18 '19 at 01:59
  • No. I cannot get past the step to associate an Active Directory Admin. Is there anything that can be reset from the SQL server side of things? – Cza102282 Nov 19 '19 at 15:24
  • As far as I know, when we set AD admin for managed instance, we need to grant managed instance permissions to read Azure AD. Do you do that before you set AD admin? For more details, please refer to https://learn.microsoft.com/bs-cyrl-ba/azure/sql-database/sql-database-aad-authentication-configure#provision-an-azure-active-directory-administrator-for-your-managed-instance – Jim Xu Nov 20 '19 at 02:05
  • I do recall performing that step but it seems that the change did not go through properly. However, I ran the powershell script suggested in your link to verify and it returns the message "Service principal 'myInstaceName' is already member of 'Directory Readers' role'' – Cza102282 Nov 20 '19 at 16:57
  • Can you do that on Azure Portal? – Jim Xu Nov 21 '19 at 03:04
  • I'm not sure I understand your question. The orange message that says, "Managed instance needs permissions to access Azure Active Directory. Click here to grant "Read" permissions to your Managed Instance" is not available. It flashes briefly and then goes away. If you are asking if I can perform steps 3 & 4 from the first section in your link and the answer would be no. – Cza102282 Nov 21 '19 at 14:46
  • I just want to know if you can use Portal to set Azure AD admin, – Jim Xu Nov 22 '19 at 01:27
  • No. The second paragraph shows the error message when I attempt to do so – Cza102282 Nov 22 '19 at 15:43
  • Could you please tell me your error message? – Jim Xu Nov 25 '19 at 01:19
  • There are two error messages in my first response. What else are you looking for? – Cza102282 Nov 26 '19 at 14:50
  • Is that you add the guest user as Azure SQL MI admin? – Jim Xu Nov 27 '19 at 01:06
  • Did you ever figure this out? – joe_coolish Jun 02 '20 at 15:48
  • Please see this post:https://social.technet.microsoft.com/Forums/en-US/e93fb883-b552-4c73-9530-33cf5f0d4b5e/setting-active-directory-admin-for-azure-sql-managed-instance-fails?forum=ssdsgetstarted – Cza102282 Jun 03 '20 at 17:55

0 Answers0