Create and Assign Managed Identity using Powershell to SQL MI

Problem Statement:

Provide functionality during creation of Managed Identity to assign the UAMI to SQL Managed Instance. We are executing all tasks using separate steps.

Resolution:

The following request has been made to fulfill this requirement. Below is the single piece of powershell code would help you perform the below tasks.

1) Connect to Azure Subscription.

2) Create UAMI.

3) Assign role to UAMI.

4) Assign a delete lock to UAMI to prevent accidental deletion.

5) Final Step, Assign UAMI to SQL Managed Instance.

$role1 = "Provide the Role Name here"
$userAssignedManagedIdentity = "Provide the UAMI Name here"
$resourceGroup = "Resource group name for UAMI"
$MIresourceGroup = "Resource group name for SQL MI"
$ManagedInstance = "SQL Managed instance Name"
$SubscriptionID="SubscriptionID"

# Connect to Azure Subscription

Connect-AzAccount -Subscription $SubscriptionID

# Create UAMI

New-AzUserAssignedIdentity -ResourceGroupName $resourceGroup -Name $userAssignedManagedIdentity

# Assign Role to UAMI

$UAMI = (Get-AzUserAssignedIdentity -ResourceGroupName $resourceGroup -Name $userAssignedManagedIdentity).PrincipalId
New-AzRoleAssignment -ObjectId $UAMI -ResourceGroupName $resourceGroup -RoleDefinitionName $role1

# Assign Lock to UAMI

New-AzResourceLock -LockName LockUAMI -LockLevel CanNotDelete -ResourceGroupName $resourceGroup -ResourceName $userAssignedManagedIdentity -ResourceType "Microsoft.ManagedIdentity/userAssignedIdentities"

# Assign UAMI to Managed Instance.

# Note: Ensure to pass -AssignIdentity parameter and the service principal should have AAD reader permission before executing the below command.

Set-AzSqlInstance -ResourceGroupName $MIresourceGroup -Name $ManagedInstance -AssignIdentity -IdentityType "UserAssigned" -UserAssignedIdentityId "/subscriptions/$SubscriptionID/resourceGroups/$resourceGroup/providers/Microsoft.ManagedIdentity/userAssignedIdentities/$userAssignedManagedIdentity" -PrimaryUserAssignedIdentityId "/subscriptions/$SubscriptionID/resourceGroups/$resourceGroup/providers/Microsoft.ManagedIdentity/userAssignedIdentities/$userAssignedManagedIdentity" -Force

Reference Article

User-assigned managed identity in Azure AD for Azure SQL - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs

Cannot find the Azure Active Directory object '' when perform management operations on SQL Managed Instance by using Azure Automation account. (microsoft.com)