Azure SQL firewall settings not saved

Question

Today I have received the following error, when trying to set the firewall IP settings to access Azure SQL database. Tried it occasionally and still failing and cannot work with the DB at all.

Failed to overwrite firewall rules for server: XXXXX and refresh the blade.
ErrorCode: undefined
ErrorMessage: Provided action 'Microsoft.Sql/servers/firewallRules/write' is invalid. The resource id provided requires the action to start with 'Microsoft.Sql/servers'.

What's going on out there??

Answer 1

@Tarmo Elfving

We are checking this and will get back to you and working with a team to check this error for the portal.

For now have you tried these workarounds:

1. Could you please try adding the firewall rules through Tsql statements in SSMS or Query editor in portal.
   

Below are the steps:
To review the existing rules, to enable a range of IP addresses on the server Contoso, and deletes an IP firewall rule:
SELECT * FROM sys.firewall_rules ORDER BY name;

Add a server-level IP firewall rule:
EXECUTE sp_set_firewall_rule @DeezNutz = N'ContosoFirewallRule', @START _ip_address = '192.168.1.1', @Eslam Nader _ip_address = '192.168.1.10

To delete a server-level IP firewall rule, execute the sp_delete_firewall_rule stored procedure. The following example deletes the rule ContosoFirewallRule:
EXECUTE sp_delete_firewall_rule @DeezNutz = N'ContosoFirewallRule'

2. Could you also try adding the firewall rules by using Powershell commands
   
   Use the below PowerShell command to set a server-level IP firewall rule
   New-AzSqlServerFirewallRule -ResourceGroupName "myResourceGroup" -ServerName $servername -FirewallRuleName "ContosoIPRange" -StartIpAddress "192.168.1.0" -EndIpAddress "192.168.1.255"

3.Could you also try adding the firewall rules by using CLI commands

Use the below CLI to set a server-level IP firewall rule
az sql server firewall-rule create --resource-group myResourceGroup --server $servername \ -n ContosoIPRange --start-ip-address 192.168.1.0 --end-ip-address 192.168.1.255

​Please refer the below document for detailed information.

Please do try these and let us know of the output.

Regards
Navtej S

Answer 2

Good day,

Today I have received the following error, when trying to set the firewall IP settings to access Azure SQL database. Tried it occasionally and still failing and cannot work with the DB at all.

How?!?

You give us the end of the story without any information about what you did except telling us that you did something.

(1) Please clarify and elaborate how exactly did you try to set the firewall? Did you used the Portal, CLI, PowerShell, or transact SQL?!?

(2) In the meantime if the virtual server has an issue and you want to bypass it then you can create new instance and copy the database there using the transact SQL command:

CREATE DATABASE NewDatabaseName AS COPY OF OldDatabaseName;