Azure SQL Firewall

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

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.

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