how to add user assigned managed identify in azure sql database using powershell?

Question

how to add user assigned managed identify in azure sql database using powershell?

I tried two methods as follow and they didn't work.

1.add parameter userassignedidentityid when create az sql database .

# Create Azure SQL Database  
$databaseName = "sql-db-ase-ermes-dev-02"  
$edition = "GeneralPurpose"  
$computeModel = "Serverless"  
$collation = "SQL_Latin1_General_CP1_CI_AS"  
$vcore =  2 
$ComputeGeneration = 'Gen5'
$MinimumCapacity = 2
$BackupStorageRedundancy ='Local'

$UserAssignedIdentityId = (Get-AzUserAssignedIdentity).Id

$databaseParams = @{  
    ResourceGroupName = $resourceGroupName
    ServerName = $serverName  
    DatabaseName = $databaseName  
    Edition = $edition  
    ComputeModel = $computeModel  
    Collation = $collation 
    VCore = $vcore
    ComputeGeneration = $ComputeGeneration
    MinimumCapacity = $MinimumCapacity
    BackupStorageRedundancy = $BackupStorageRedundancy 
    UserAssignedIdentityId = $UserAssignedIdentityId
}  
  
New-AzSqlDatabase @databaseParams  

2.add parameter userassignedidentityid after creating az sql database.

$UserAssignedIdentityId = (Get-AzUserAssignedIdentity).Id
Set-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName   -UserAssignedIdentityId  $UserAssignedIdentityId

Answer 1

Hi Brian D H ZHANG •,

Welcome to Microsoft Q&A forum.

As I understand, you want to provision Azure SQL Database logical server using user assigned managed identity.

Thanks for sharing details and screenshots.

Could you please try to use commands for Logical Server and not on Database as shown below:

$server = @{

ResourceGroupName = "<ResourceGroupName>"

Location = "<Location>"

ServerName = "<ServerName>"

ServerVersion = "12.0"

AssignIdentity = $true

IdentityType = "UserAssigned"

UserAssignedIdentityId = "/subscriptions/<subscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<managedIdentity>"

PrimaryUserAssignedIdentityId = "/subscriptions/<subscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<primaryIdentity>"

ExternalAdminName = "<AzureADAccount>"

EnableActiveDirectoryOnlyAuthentication = $true }

New-AzSqlServer @server

Note

The above example provisions a server with only a user-assigned managed identity. You could set the -IdentityType to be "UserAssigned,SystemAssigned" if you wanted both types of managed identities to be created with the server.

To check the server status after creation, see the following command:

Get-AzSqlServer -ResourceGroupName "<ResourceGroupName>" -ServerName "<ServerName>" -ExpandActiveDirectoryAdministrator

Prerequisites

• To provision a SQL Database server with a user-assigned managed identity, the SQL Server Contributor role (or a role with greater permissions), along with an Azure RBAC role containing the following action is required:
  • Microsoft.ManagedIdentity/userAssignedIdentities/*/assign/action - For example, the Managed Identity Operator has this action.
• Create a user-assigned managed identity and assign it the necessary permission to be a server or managed instance identity. For more information, see Manage user-assigned managed identities and user-assigned managed identity permissions for Azure SQL.
• Az.Sql module 3.4 or higher is required when using PowerShell for user-assigned managed identities.
• The Azure CLI 2.26.0 or higher is required to use the Azure CLI with user-assigned managed identities.
• For a list of limitations and known issues with using user-assigned managed identity, see User-assigned managed identity in Microsoft Entra for Azure SQL

Refer https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity-create-server?view=azuresql&tabs=azure-powershell more details.

Hope this helps.

Let us know if you have further queries.

Thanks