Azure SQL Firewall

Question

Azure SQL Firewall

RJ 366

Hi there,

I have 2 environments. I'm more of admin on Azure environment (recently made as subscription admin) after which Dev issue - Azure SQL I'm having difficulty to remove IP from Azure SQL Firewall. (Earlier i was able to) today my manager granted me subscription admin and as SQL Security Manager and it still not able to remove grayed out IPs.

RJ 366 Reputation points

2025-08-08T20:14:11.8233333+00:00

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 still follow the above step?

I remember i was sql contributor. I never had any admin access till last 2 weeks 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.
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-11T12:55:27.77+00:00
Hi @RJ,

Thanks for the detailed information, I understand your concern having Azure AD admin access. can you please check with someone who is having Azure AD admin access are able to delete the ip addresses or not.

if you are still not able to delete ip address, could you please confirm below details

Firewall rules are stored in the master database of the logical SQL server, did you logged into the master database and tried deleting the IPs?

Please go through this Microsoft document for more details:

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

Please let us know if you need further information.

Thanks,

Kalyani
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-12T05:08:09.2066667+00:00

Hello @RJ,

Hope you have great day!

Could you please confirm if you have gone through my response and if that worked for you? Do let me know if you have any further questions on this.

If the provided information helpful, please click "Upvote", it will help others who might be facing similar challenges.

Thanks

Kalyani
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-14T07:02:01.87+00:00

Hi @RJ,

Greetings for the day!

Could you please confirm if you have gone through my previous response and if that worked for you? Any update?

Thanks!

Kalyani

2 answers

Your answer

RJ 366 Reputation points

2025-08-08T20:14:11.8233333+00:00

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 still follow the above step?

I remember i was sql contributor. I never had any admin access till last 2 weeks 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.
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-11T12:55:27.77+00:00

Hi @RJ,

Thanks for the detailed information, I understand your concern having Azure AD admin access. can you please check with someone who is having Azure AD admin access are able to delete the ip addresses or not.

if you are still not able to delete ip address, could you please confirm below details

Firewall rules are stored in the master database of the logical SQL server, did you logged into the master database and tried deleting the IPs?

Please go through this Microsoft document for more details:

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

Please let us know if you need further information.

Thanks,

Kalyani
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-12T05:08:09.2066667+00:00

Hello @RJ,

Hope you have great day!

Could you please confirm if you have gone through my response and if that worked for you? Do let me know if you have any further questions on this.

If the provided information helpful, please click "Upvote", it will help others who might be facing similar challenges.

Thanks

Kalyani
Kalyani Kondavaradala 4,600 Reputation points Microsoft External Staff Moderator

2025-08-14T07:02:01.87+00:00

Hi @RJ,

Greetings for the day!

Could you please confirm if you have gone through my previous response and if that worked for you? Any update?

Thanks!

Kalyani

Answer 1

Alberto Morillo 35,406 MVP Volunteer Moderator

To my knowledge, you need "Contributor" or "Owner" on the logical server resource.

About the error with sp_delete_firewall_rule, it should be executed on master database for server-level rules. The rules you are trying to change have been defined on logical server.

RJ 366 Reputation points

2025-08-08T20:50:39.57+00:00

I have attached screenshot above. Am i missing anything else.
Alberto Morillo 35,406 Reputation points MVP Volunteer Moderator

2025-08-08T23:23:11.5+00:00

If the permission of Security Manager did not work, try the 2 other permissions I mentioned that are specified on the Azure SQL documentation https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql#permissions

Answer 2

Hello @RJ,

Thanks for reaching out on Microsoft Q&A! let me try to help with some steps

Why You Can't Delete Grayed-Out Ips:

Even though you have roles like SQL Security Manager and Subscription Admin, those roles do not give you permission to manage server-level firewall rules stored in the master database.

These rules can only be modified by.

The server-level principal login (the original SQL admin created during server setup)

Or an Azure Active Directory (AD) admin assigned to the SQL server

Become the Azure AD Admin for the SQL Server

You must be set as the Active Directory admin on the SQL logical server-not just the database.

Go to the SQL server (not the database) in the Azure portal.
Under Settings, click Active Directory admin.
If your account isn’t listed, click Set admin, choose your user, then Save.
Sign out and back in to refresh your permissions.

Delete the Grayed-Out IPs via Portal

In Azure Portal:

Go to the SQL server --> Firewalls and virtual networks.
You should now be able to click the trash icon next to the grayed-out IPs and Save.

In T-SQL (SQL Server Management Studio or Azure Data Studio):

Make sure you connect to the master database using your Azure AD admin credentials.

--Connect to master database
USE master;
--Delete the rule
EXEC sp_delete_firewall_rule @name = N'ClientIPAddress_2023-06-28_01:06:27';
--Optional: Refresh cache
DBCC FLUSHAUTHCACHE;

If you get the error “User must be in the master database,” it means you're connected to the wrong database. Switch to master before running the command.

Note: Grayed-out firewall rules in Azure SQL Database can only be deleted by the server-level principal login or an Azure Active Directory (AD) admin.

Please refer this document:

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql&source=recommendations

Let me know if you need more details, happy to assist you further

Thanks,

Kalyani

Share via

Azure SQL Firewall

2 answers

Your answer