Is there a PowerShell statement that can Update a server with User Managed Identies?

Question

Is there a PowerShell statement that can Update a server with User Managed Identies?

Brian D H ZHANG 60

Hi

like this :

az sql server update -g myResourceGroup -n myServer -i \ --user-assigned-identity-id /subscriptions/xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.ManagedIdentity/userAssignedIdentities/testumi \ --identity-type UserAssigned --pid /subscriptions/xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.ManagedIdentity/userAssignedIdentities/testumi

https://learn.microsoft.com/en-us/cli/azure/sql/server?view=azure-cli-latest#az-sql-server-update,

the az cli cann't work in my env.I need to use powershell .

i use the powershell as following ,but it doesn't work.

Set-AzSqlServer -ResourceGroupName $(resourceGroupNameBCP) -ServerName $(serverNamebcp) -IdentityType UserAssigned -UserAssignedIdentityId '/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx' -PrimaryUserAssignedIdentityId  '/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx'

thanks for your help.

Accepted answer

1 additional answer

Your answer

Answer 1

Hi @Brian D H ZHANG,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue: Is there a PowerShell statement that can Update a server with User Managed Identies? the az cli cann't work in my env. I need to use powershell.

Solution:

As you stated that, you have resolved the issue using azure CLI.

The solution is as follows.

The following command clears the UMI.

   az sql server update -g resourceGroupName -n serverName -i --user-assigned-identity-id /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

2.set sql server UMI

az sql server update -g resourceGroupName -n serverName -i --user-assigned-identity-id /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx 
 --identity-type UserAssigned --pid /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

3.set database UMI

az sql db update -g resourceGroupName -s serverName 
-n databaseName  -i --user-assigned-identity-id 
/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

If I missed anything please let me know and I'd be happy to add it to answer, or feel free to comment below with any additional information.

Please remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution.

Thank you,

Answer 2

Marcin Policht 40,310 MVP

Set-AzSqlServer cmdlet should work

# Set variables
$resourceGroupName = "myResourceGroup"
$serverName = "myServer"
$userAssignedIdentityId = "/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx"

# Update SQL server with User Assigned Identity
Set-AzSqlServer `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AssignIdentity `
    -IdentityType UserAssigned `
    -UserAssignedIdentityId @{Key = $userAssignedIdentityId}

-AssignIdentity: Specifies that an identity should be assigned to the server. -IdentityType: Specifies the type of identity (UserAssigned in this case). -UserAssignedIdentityId: This needs to be passed as a hashtable where the key is the identity ID.

Ensure the PowerShell Az module is up to date by running Update-Module -Name Az.
Verify that the user or service principal executing the command has the necessary permissions (e.g., Contributor role on the SQL server and Managed Identity resources).
Confirm the User Assigned Identity exists and the provided ID is accurate.
Ensure PowerShell is using the appropriate subscription by running Set-AzContext.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Brian D H ZHANG 60 Reputation points

2025-01-17T03:29:00.48+00:00

@Marcin Policht thanks for your reply I executed the script you gave me and got the following error:

Property id 'System.Collections.Hashtable' at path '' is invalid. Expect fully qualified resource Id that start with '/subscriptions/{subscriptionId}' or '/providers/{resourceProviderNamespace}/'.

PowerShell exited with code '1'.

But the userAssignedIdentityId is correct.
Vijayalaxmi Kattimani 1,885 Reputation points Microsoft External Staff

2025-01-17T09:53:39.1066667+00:00
Hi @Brian D H ZHANG,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are trying to update a server with User Managed Identities using PowerShell, but the command you are using is not working.

You can update a SQL Server with a user-assigned managed identity using the Set-AzSqlServer PowerShell cmdlet. However, your PowerShell statement needs some corrections and clarification regarding syntax. Here's a corrected version:

Set-AzSqlServer ` -ResourceGroupName $resourceGroupNameBCP ` -ServerName $serverNamebcp ` -AssignIdentity ` -IdentityType UserAssigned ` -UserAssignedIdentityId @('/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx') ` -PrimaryUserAssignedIdentityId '/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx'

Kindly note that, -AssignIdentity: Ensures that the identity type is updated. It's necessary when specifying managed identities. -UserAssignedIdentityId: Accepts an array of User Managed Identity (UMI) IDs. Enclose the IDs in an array (@()).-PrimaryUserAssignedIdentityId: Points to the primary UMI that the SQL Server should use.

Ensure the Azure PowerShell user account has adequate permissions to update the SQL Server and make sure you are using the latest version of the Az PowerShell module to ensure compatibility.

Replace placeholders like $resourceGroupNameBCP and $serverNamebcp with your actual resource group name and server name. Ensure no trailing/leading whitespace in the variables.

Test with Get-AzSqlServer: Run Get-AzSqlServer before and after executing the script to verify the change.

Please refer to the below mentioned link for more information.

https://learn.microsoft.com/en-gb/powershell/module/az.sql/set-azsqlserver?view=azps-13.1.0

I hope this information helps. Please do let us know if you have any further queries.

Marcin Policht 40,310 MVP

Give the following a try:

# Set variables
$resourceGroupName = "myResourceGroup"
$serverName = "myServer"
$userAssignedIdentityId = "/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx"
# Update SQL server with User Assigned Identity
Set-AzSqlServer `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AssignIdentity `
    -IdentityType UserAssigned `
    -UserAssignedIdentityId $userAssignedIdentityId

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Vijayalaxmi Kattimani 1,885 Reputation points Microsoft External Staff

2025-01-20T08:59:48.99+00:00

Hi @Brian D H ZHANG,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Vijayalaxmi Kattimani 1,885 Reputation points Microsoft External Staff

2025-01-21T09:03:48.3566667+00:00

Hi @Brian D H ZHANG,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Brian D H ZHANG 60

@Vijayalaxmi Kattimani @Marcin Policht Thanks for your helps . The problem is sloved finally using azure cli.

Using PowerShell always reports an error.

The solution is as follows.

The following command clears the UMI.

 az sql server update -g resourceGroupName -n serverName -i --user-assigned-identity-id /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

2.set sql server UMI

 az sql server update -g resourceGroupName -n serverName -i --user-assigned-identity-id /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx 
 --identity-type UserAssigned --pid /subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

3.set database UMI

az sql db update -g resourceGroupName -s serverName 
-n databaseName  -i --user-assigned-identity-id 
/subscriptions/xxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.ManagedIdentity/userAssignedIdentities/xxxxxxxxx

Share via

Is there a PowerShell statement that can Update a server with User Managed Identies?

1 additional answer

Your answer