Azure SQL Firewall

[AJAJ]

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

[v-sdhruv · Accepted solution]

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-firewa...

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-

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

Hope this helps!

[v-sdhruv · Accepted solution]

 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!

[AJAJ]

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

[v-sdhruv · Accepted solution]

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-firewa...

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-

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

Hope this helps!

[AJAJ]

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.

[v-sdhruv]

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

[AJAJ]

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.