Azure Powershell - Not able to add Subnet Settings to Database

Question

I am trying to add a subnet to SQL Server using Azure Az Module. The command I am using is

New-AzSqlServerVirtualNetworkRule -VirtualNetworkRuleName "newvnetrule1" -ServerName $sqlServer.ServerName -ResourceGroupName $sqlServer.ResourceGroupName -VirtualNetworkSubnetId $newsubnetId -ErrorAction Stop

I get an exception saying:

The client with object id does not have permission to perform this action

The object id belong to a SPN of name Azure SQL Virtual Network to Network Resource Provider.

I get the exact same issue while provisioning cosmos db account with ARM template only this time the erroneous SPN is Azure Cosmos DB Virtual Network to Network Resource Provider

Can anyone throw some light on this? The same code used to work fine. All the services are registered for the subnet too

Answer 1

The Owner role is enough, I test it on my side, it works fine.

$virtualNetworkSubnetId = "/subscriptions/xxxxxxx/resourceGroups/joynet/providers/Microsoft.Network/virtualNetworks/joysqlnet/subnets/default"

New-AzSqlServerVirtualNetworkRule -ResourceGroupName joynet -ServerName joyser -VirtualNetworkRuleName vnetrule1 -VirtualNetworkSubnetId $virtualNetworkSubnetId

---

To fix the issue, try to use Clear-AzContext to clear all the local account information, then use the script below to login again.

$azureAplicationId ="<Application ID>"
$azureTenantId= "<Tenant ID>"
$azurePassword = ConvertTo-SecureString "<Client secret>" -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential($azureAplicationId , $azurePassword)
Connect-AzAccount -Credential $psCred -TenantId $azureTenantId -ServicePrincipal

Then run (Get-AzContext).Account, make sure the Id is the same as the Application ID of the service principal you are using, also the Tenant ID of the service principal should be the same as the GUID in Tenants.