Azure SQL Database connection without whitelist

Question

Hi, I am currently using the free trial of the Azure SQL Database to host a database for a school project. It works perfectly fine for me, but the problem is whitelisting users in the firewall. I have whitelisted myself and can access the database, but when an examiner uses my app, they can't connect to the database as they aren't whitelisted. Is there any solution to this problem? I have no method of knowing the examiner's IP address beforehand or for them to contact me at any point.

Answer 1

There are a number of ways to restrict access, but as you are trying to keep costs low, they may not be suitable for you.

One way that you could approach this is to create an additional user in your Azure subscription for the examiner and provide that account with permissions to update your firewall and add their own IP Address at the time of access.

To do this, you should first of all ensure that your SQL Server resource is the only resource within your Resource Group. This lets you be granular with your controls as you need to assign some high level permissions. Only you have this configured then you can add your examiners Azure AD identity to the SQL Server Contributor, SQL Server Security Manager IAM Roles scoped to the Resource Group only. Then add the examiners Azure AD identity to the Owner IAM Role scoped to the Resource Group only.

Be aware that this does provide the examiner with highly privileged access within your Azure subscription, but only scoped to the Resource Group with your SQL Server. They will then be prompted when they connect to login to your Azure Subscription and add their IP Address to the firewall rules.