What's permissions should be added to Azure SQL server to get active directory user the ability to automatically set your own firewall IP exception?

Question

I have an Azure SQL server where I have the contributor role under the Resource Group that contains it. I have set my own user as Azure Active Directory admin. I guess that's what give me the ability to add my own IP as firewall exception automatically when connecting through Management Studio and my IP was changed. There are other users that will work with the same server and would be great them have the same capability to automatically set the firewall exceptions through Management Studio, but I can't get it working.

Here are the steps I did:

1. Asked subscription admin to add contributor role to user under the server Resource Group
2. I have added the user to master db in this way:

CREATE USER [dude@dude.org] 
    FROM EXTERNAL PROVIDER;

ALTER ROLE dbmanager ADD MEMBER [dude@dude.org];
ALTER ROLE loginmanager ADD MEMBER [dude@dude.org];

With this configuration when user connects to server gets the popup prompting to add it's IP to firewall exceptions but when trying to save is getting 401 error.

An error occurred while creating a new firewall rule (HTTP Status Code 401)) (ConnectionDlg)

An important point is that user is able to set firewall exceptions successfully through Azure Portal

Any ideas on how to solve that?

Answer 1

• In your scenario, you will need to configure the Azure SQL database level firewall rules and allow the IP addresses through which the connection to the databases hosted on that Azure SQL Server will be allowed or permitted. This can be done through various methods, i.e., through the Azure portal, powershell commands or through the transact-SQL query.

To allow a specific IP address range to be allowed to an Azure SQL Server database, the user should have ‘CONTROL’ permissions at the specific database level and this modification can only be done through transact-SQL query. But before you will be allowed to connect to the server to execute the transact-SQL query, a server level firewall IP rule must be created by a user who has Azure level permissions through the portal itself, i.e., an Azure AD administrator or server-level principal login.

• Thus, first you will have to assign an authorized Azure AD user to the concerned database through the transact-SQL query as specified by you. Once done, then grant the Azure AD user control permission on the database through transact-SQL query as shown in below example: -

  ‘ USE AdventureWorks2012;
    GRANT CONTROL ON DATABASE::AdventureWorks2012 TO Sarah;
    GO ’

• Once the above access to the database has been given to the concerned Azure AD users, then execute the below transact-SQL command to allow connection from specific IP address to that database: -

  ‘ exec sp_set_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0'; ‘

In the above command, first part states the starting IP address, and the other part states the ending IP address through which the connection should be allowed. Also, the above commands need to be executed in the master database only.

For more information, please refer the below documentation links for more information: -

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

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver15#examples