Azure SQL firewall settings not saved

Tarmo Elfving 26 Reputation points
2020-11-24T17:23:44.81+00:00

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??

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Navtej Singh Saini 4,216 Reputation points Microsoft Employee
    2020-11-24T20:49:26.327+00:00

    @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.
      42301-download.png

    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'

    1. Could you also try adding the firewall rules by using Powershell commands
      42312-download-1.png
      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
    42313-download-2.png
    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


1 additional answer

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2020-11-24T21:20:19.623+00:00

    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;