Coding Azure 5: Securing an Azure SQL Database to an App Service

There’s nothing more important than securing access to your organization’s data. Here’s how to set up your Azure SQL database so that only authorized applications can get to it.

In Coding Azure 4: Securing a Web Service in an App Service to Access an Azure SQL Database, I created and assigned a Managed Identity to an App Service holding a Web Service that accesses an Azure SQL database. In this post, I’m going to take advantage of that identity to allow only that App Service (or, rather, the Web Service running inside the App Service) to access my database. After doing that, I’m going to show how to further secure your database using something less “cloud-based”: IP addresses.

As I discussed in my previous post, a Managed Identity and a user are both examples of service principals—i.e., things you can assign permissions to (users also have account information assigned to them). One of the places you can use a service principal is within an Azure SQL database, provided you’ve set up your Azure SQL database resource to use Entra Id as your authentication provider, as I did in Coding Azure 2: Configuring an Azure SQL Database.

Assigning Permissions

In a database, “giving permissions” begins with setting up your Managed Identity as a user for your database. To do that, start SQL Server Management Studio and connect to your Azure SQL database server (in my case, that’s warehousedbserver.database.windows.net).

Adding the Managed Identity

To add your Managed Identity as a user:

1. In the Object Explorer pane on the left, expand the Databases node to display your database (my database is called WarehouseDB).
2. Drill down through your database’s Security node to get to the Users node (you may have to refresh your view once or twice before the Security node will display).
3. Right-click on the on the Users node and, from the pop-up menu, select “New User” to display the Database User – New dialog.
4. At the top of the dialog, in the User Type dialog, select External User (in this case, your Azure Entra ID Managed Identity).
5. In the User name textbox, enter the name of your Managed Identity (in my case, that’s WarehouseManagementDBAccess).
6. Click the OK button to add your Managed Identity as a user in your database.

Your user will now show up in SQL Server Management Studio, under your database’s Users node. To confirm that your user has Connect permissions:

1. Right-click on your new user and select Properties to open the Database Properties dialog.
2. In the menu on the left of that dialog, select Permissions to display your user’s permissions.
3. In the list labelled Explicit, scroll down to the Connect entry.
4. Make sure that the checkbox in the Grant column is checked.

You could add additional settings/permissions for your Azure SQL resource to your Managed Identity (e.g., give your identity a database role or a default schema). However, the less functionality the identity has in this resource, the more secure your database is. The minimum that you need to provide is access to individual tables in the database, and that doesn’t require anything more than the Connect permissions already granted.

Adding Table Permissions

The next step is to give your Managed Identity user access to the table it needs. Initially, all I want my identity to be able to do is return all the rows from my WarehouseDB’s SalesLT.Products table. To do that:

1. Still in SQL Server Management Studio’s Object Explorer pane, expand the Databases node.
2. Under the Database node, expand the entry for your Azure SQL database (in my case, that’s WarehouseDB).
3. Inside that database node, expand the Tables node and right-click on the table you want to give your Managed Identity access to (in my case, SalesLT.Products).
4. From the pop-up menu, select Properties to display the Table Properties dialog
5. In the menu down the left side of the dialog, select Permissions to open a new panel on the right.
6. In that panel, click the Search button to open the Select Users or Roles dialog.
7. In that dialog, click the Browse button to open the Browse for Objects dialog which will include, for example, your Managed Identity.
8. In the list on that dialog box, check off your Managed Identity (in my case, that’s WarehouseMgmtDBAccess).
9. Click the OK button to close the dialog and return to the Select Users or Roles dialog.
10. Click that dialog’s OK button to get back to Table Properties dialog.
11. With your Managed Identity selected, find the permissions you want to grant and put a check mark in the Grant column (I just checked the Grant column for Select permission).
12. Click the OK button to return to SQL Server Management Studio and repeat for any other permissions that your App service will need.

Configuring Addresses

But we can do a little more to make sure that only our Web Service can access our Azure SQL database by leveraging your App Service’s TCP addresses and Azure SQL’s built-in firewall.

App Service Addresses

Your App Service has a set of outbound TCP addresses that are used when your Web Service sends a request (for example, the requests sent to your Azure SQL database). You can find those outbound addresses on your App Service’s Overview page or from your App Service’s left-hand menu under Settings | Networking. In either place, look for the label Outbound traffic configuration to find the list of addresses.

Leveraging the Azure SQL Firewall

Now that you have that list, you can limit your database resource to only accept resources from those addresses.

To do that, in the Azure Portal, surf to your Azure SQL to see the database resources you’ve set up. Your database resource appears in that list twice: a SQL database and SQL server. You can give your App Service access at either level.

• Server: You can set these rules in the Azure Portal UI
• Database: These rules can’t be set in the Azure Portal but can be set using SQL commands from, for example, SQL Server Management Studio.

The database-level rules are processed first and, if your App Service is denied at that level, then your request will be rejected regardless of the setting at the server level. Database-level rules can make sense if you allow a large number of users at the server level and then want to selectively deny access to individual databases for users or resources allowed access at the database level. That is a strategy that adds complexity to your security configuration and the resulting database-level rules are much less visible than those set in the Azure Portal.

To set your server-level rules:

1. Select your Azure SQL database (i.e., the entry for your database resource in Azure SQL that’s labeled SQL database and not the one labeled SQL server).
2. From the menu on the left, select Security | Networking to open the firewall’s network settings on the right.
3. Under the Firewall rules heading, click on the + Add a firewall rule menu choice at the right end of the menu to open a dialog box.
4. In the dialog box’s Rule Name textbox, give your rule a meaningful name.
5. In the Start IP textbox, enter the lowest IP address from your App Service’s outbound address.
6. In the End IP textbox, enter the highest IP address.
7. Click the OK button to close the panel.

You’ve now whitelisted your App Service for your database. Which raises the question: Without this rule in place, how has your App Service been able to access your database resource?

Up until now, your App Service has been getting access through a firewall exception that allows all Azure Services and resources to send requests to your database server. You need to turn that off now (otherwise, putting in the IP addresses for your App Service is redundant).

To do that, look below the list of firewall rules that you just added to and find the checkbox labeled Allow Azure services and resources … . Uncheck that checkbox and click the Save button at the bottom of the page to commit your changes.

A Caveat

Having said that, just because you can do something doesn’t mean that you should do something. The more measures you take to secure access to your applications, the more likely it is that, over time and the changes time brings, your application will fail.

If, for example, you destroy and recreate your App Service, it may get a new set of outbound addresses … new addresses which, of course, will be incompatible with the settings in your database’s firewall. Alternatively, if you change your App Service’s service plan from Basic, Standard or Premium to Premium2 or Premium3, then your App Service will get additional outbound addresses which, again, won’t be whitelisted in your database’s firewall but your App Service will use.

And you may do nothing at all and still have your App Service’s outbound IP addresses change—Microsoft does not guarantee that these are static addresses. The Azure teams does, however, try to send notifications of IP address changes out approximately 90 days before the change, giving you time to plan for the change.

You can address this just by “being careful” when creating your App Service, but a better choice would be to take advantage of Azure’s templates for defining services. I’ll be covering that in a later post. Alternatively, if you like this idea of whitelisting your App Service but would prefer to have a guaranteed static IP address, you can consider upgrading to (and paying for) an App Service Environment.

Or you may decide that the default exception that limits access to your database resource to other items in your Azure tenant may be sufficient. I’m not here to judge.

Next Steps

Now that you’ve got a data tier (your Azure SQL Database) and a middle tier (your App Service holding your Web Service), you need a frontend. Before I cover that, though, now that you have deployed code, it makes sense to turn on debugging and logging. I’ll cover that in my next post and, after that, walk through creating both client-side and server-side frontends.