topic Re: Azure SQL Firewall in Data Warehouse

Azure SQL Firewall

AJAJ — Thu, 07 Aug 2025 13:11:17 GMT

Hi there,

I have 2 environments - Dev and Prod (Both have same issue). I'm more of admin on Azure environment (recently made as subscription admin)

after which I'm having difficulty to remove IP from Azure SQL Firewall. (Those IPs are my own IPs added via SSMS before my i became Azure Subscription admin) (Earlier i was able to delete IPs) today my manager granted me subscription admin and as SQL Security Manager and it still not able to remove grayed out IPs.

Prod Resource group has lock. Looks like permission are messed up. What permission do I need to have control over RGroups?

Thanks

Re: Azure SQL Firewall

v-sdhruv — Fri, 08 Aug 2025 13:28:29 GMT

Hi AJAJ ,

Grayed-out IPs in Azure SQL Firewall typically indicate server-level firewall rules that were created by the server-level principal login. According to Microsoft documentation, only the server-level principal login—the account created during the initial provisioning of the SQL server—can delete these rules using sp_delete_firewall_rule
Refer-

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-firewall-rule-azure-sql-database?view=azuresqldb-current

Even with Subscription Admin and SQL Security Manager roles, you won’t have access to delete these rules unless:

You are logged in as the server-level principal login, or
You are assigned as a Microsoft Entra admin for the SQL server

Therefore you can follow these steps-

Confirm whether you are the server-level principal login or request access from whoever is.
If you’re not, ask your Azure admin to assign you as a Microsoft Entra admin for the SQL server.
Remove the resource group lock if you have the necessary permissions or request it from someone who does.

Hope this helps!

Re: Azure SQL Firewall

AJAJ — Fri, 08 Aug 2025 20:50:59 GMT

Thanks. Those grey IPs are my own IPs created when I logged into SSMS. I was able to delete it myself for many months earlier. Once I got subscription level admin, later on i got to know my ips are greyed out and now want to clean up. Does it mean once i become S-Admin i lost server-level principal login. im still able to login and continue db development activities with no issues. Anyway what should be done to so i become server level principal login again?

I remember i was sql contributor. I never had much of any admin access till last 2 weeks (except PBI admin) and many months i was still able to add/remove SQL IPs under firewall. Im not sure seeking S-Admin, AAD admin is all necessary to delete the SQL firewall. I dont want unnecessary admin access when i was able to delete IPs without admin access earlier. All i need is full control of Azure SQL. Not SAdmin, not AAD admin,etc.. Can you please guide me to get full of Azure SQL but dont want my ID to be as admin unnecessarily to avoid any unknown breaches.

Re: Azure SQL Firewall

AJAJ — Fri, 08 Aug 2025 20:52:50 GMT

What do I need to do for full control of resource group. Azure SQL DB is within that RG.

Re: Azure SQL Firewall

v-sdhruv — Mon, 11 Aug 2025 09:30:20 GMT

Hi AJAJ ,

( To get full of Azure SQL but dont want my ID to be as admin unnecessarily to avoid any unknown breaches )
You will need either the Owner or Contributor role at server-level.

To regain the ability to manage those grayed-out IPs:

Try connecting to the master database in SSMS and run:

EXEC sp_delete_firewall_rule @name = N'YourRuleName';

If you get an error like “User must be in the master database,” you're not the principal login.
Your current role change may have revoked or overridden that access.
To answer-

What do I need to do for full control of resource group. Azure SQL DB is within that RG?

If the RG is locked, only users with delete or write permissions can remove or modify the lock.

You can assign yourself the necessary roles using Azure RBAC:

Go to the RG in Azure Portal

Navigate to Access Control (IAM)

Click Add Role Assignment

Select either Owner or Contributor

Refer - https://learn.microsoft.com/en-us/azure/role-based-access-control/

Hope this helps!

Re: Azure SQL Firewall

AJAJ — Tue, 12 Aug 2025 20:20:20 GMT

Thanks.

For someone referring this in future.

I got myself as Owner of resource group, navigated to Azure SQL DB. Of course as recommended above, need to be a contributor (i guess may not matter since your id is admin). On the other hand, you could ask whoever is the admin to do the below to remove / clean up greyed out IPs on Azure SQL.

Re: Azure SQL Firewall

v-sdhruv — Wed, 13 Aug 2025 05:02:04 GMT

Thank you AJAJ for sharing your inputs.
This would definitely help the community members who are facing such issue.

Re: Azure SQL Firewall

maruthi_P — Thu, 11 Sep 2025 12:28:47 GMT

Please use azure cli command

az sql server firewall-rule delete --resource-group [yourResourceGroupName] --server [yourServerNamewithout.database.windows.net] --name "ClientIPAddress_2025-08-28_08:02:45"

Note: As client IP address is string case senstive, please add string format ""

Cli Command>az sql server firewall-rule list --resource-group resourceGroupName --server serverName --output table
Cli Command>az sql server firewall-rule delete --resource-group resourceGroupName --server serverName --name "ClientIPAddress_2025-08-28_08:02:45"
Cli Command>az sql server firewall-rule list --resource-group resourceGroupName --server serverName --output table

Re: Azure SQL Firewall

maruthi_P — Thu, 11 Sep 2025 13:26:33 GMT

You can use Azure CLI command because 'sp_delete_firewall_rule' procedure may not have available in all the Azure SQL Servers

First step:

CliCommand>az sql server firewall-rule list --resource-group [resourceGroupName] --server [serverNamewithout.database.window.net] --output table

You can see all the Network Ip's
CliCommand>az sql server firewall-rule delete --resource-group resourceGroupName --server serverName --name "ClientIPAddress_2025-08-28_08:02:45"

CliCommand>az sql server firewall-rule list --resource-group [resourceGroupName] --server [serverNamewithout.database.window.net] --output table