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 In...
Cannot find the Azure Active Directory object '' when perform management operations on SQL Managed I...
