Coding Azure 4: Securing a Web Service in an App Service to Access an Azure SQL Database

Managed Identities are an essential tool in Azure to control who can get to your organization’s data. Here’s how to take advantage them in a cloud-native Web Service.

In an earlier post, Coding Azure 2: Configuring an Azure SQL Database, I created an Azure SQL Database and, in my last post, Coding Azure 3: Deploying a Web Service to an App Service, a skeleton Web Service that I deployed to an App Service. The next step is to provide some code in that Web Service that will access the database and also ensure that only that code, running in that App Service, can access the database.

In this post, I’m going to provide some background on the tool I’ll be using to control access (Managed Identities). I’ll then cover how to set up a Managed Identity and then configure the App Service holding my Web Service (and the Web Service itself) to use that Managed Identity. In my next post, I’ll cover what needs to be done in the database to work with the Managed Identity (and also suggest how to control network access to the database).

Understanding Identities

First, some Entra ID terminology. When you log into Azure, the current terminology is that you are presenting a set of credentials (name, password, multi-factor authentication) that entitle you to claim a user identity. A user identity has account information (e.g., name, address, email address, job position, etc.) and permissions to perform activities in Azure (e.g., read your own information from Microsoft Graph, Azure’s one-stop shopping for user information).

Your identity is “something that can be granted permissions” and can also be classified as a “security principal.” Specifically, your user identity can also be called a “user principal.” Another kind of security principal is a Managed Identity, which can be assigned to an Azure resource (e.g., an App Service). A Managed Identity is a kind of “service principal”: a principal without account information that is never assigned to a user. Because a Managed Identity is a security principal, it can—like a user identity—be assigned permissions.

If you’re interested, the term “Managed Identity” refers to how the identity is assigned. Think of it this way: A user identity is assigned as a user logs in, and that identity remains active until the user logs out. A Managed Identity, however, is assigned when the resource starts and is active until the resource shuts down—it’s managed by Azure and not by a user’s activities.

There are two kinds of Managed Identities: system-assigned and user-assigned. A system-assigned identity is generated for a resource (e.g., an App Service) by Azure and has a lifetime that matches the resource. If the App Service is created, a system-assigned Managed Identity is assigned to it. And then if that App Service is deleted, then any permissions assigned to that system-assigned identity will have to be recreated.

A user-assigned identity, on the other hand, is one that is created independently of any Azure resource and can be assigned to any resource (or set of resources). A user-assigned Managed Identity can even be created before any resource that the identity will be used with is created. A user-assigned identity will also survive the deletion of any resource it’s assigned and can, for example, be reapplied (with its permissions) to that resource when the resource is recreated.

There are other benefits to a user-assigned Managed Identity. If, for example, you have multiple resources that need the same permissions, a Managed Identity with those permissions could be shared with all those resources that need identical permissions. As those permissions change for (assuming those permissions need to be changed for all of the resources), you only need to update that shared Managed Identity.

So, if you create an App Service and then assign a user-assigned Managed Identity, you can give that Managed Identity whatever permissions the Web Service running inside that App Service needs. If you later delete and recreate that App Service, you’d just need to reassign the Managed Identity to the new App Service to restore access to the database.

And, while Managed Identities are a great thing, they are only a complete solution for those resources whose permissions are entirely handled by Entra ID (Azure Storage Accounts, for example). For most resources, however, the only permissions that are assigned by Entra ID to an identity are those permissions required to manage the resource (create/delete the resource, change the resource’s settings, review the resource’s audit logs). Those resources will have an internal system for assigning permissions to an identity.

And that’s the way that Azure SQL works, for example: Any identity can access Azure SQL. But, as you’ll see, it’s Azure SQL’s internal security that controls what tables that identity can access and what the identity is allowed to do with the tables it can access (read rows, add new rows, etc.).

Creating a Managed Identity

Based on all that, the first step in securing the App Service with its Web Service to the Azure SQL database is creating a Managed Identity. You may not have permissions to do that and would need to get an administrator to do it for you. Once the identity is created, however, the creator can use the Managed Identity’s Access Control (IAM) page to give you access to the Managed Identity. The two relevant roles are:

• Managed Identity Operator: Given this role, you can assign the identity to Azure resources (e.g., your App Service) but can’t change the permissions assigned to the identity. This is all you need for securing the Web Service to the Azure SQL database because it’s Azure SQL’s internal security system that will control what the Web Service will be able to do.

• Managed Identity Contributor: Can create, delete and modify Azure identities. This identity is required if you need to add or remove permissions from the identity

If you can create Managed Identities, the steps are:

1. Surf to the Azure Portal, enter Managed Identities in the search box that appears at the top of the page.
2. When Managed Identities appears in the dropdown list, double-click it to the Managed Identities page.
3. At the left end of menu across the top of the page, click on the + Create choice to open the Create User Assigned Managed Identity page.
4. On this page, as with any Azure resource, you’ll need to assign your Managed Identity to a subscription, a resource group and region. (I used the resource group WarehouseMgmt and region Central Canada for the other resources in this application.)
5. In the Name textbox, give your Managed identity a name that reflects the permissions you will be assigning to it. (I used WarehouseMgmtDBAccess.)
6. Click the Review + create button to start creating your Managed Identity.

Managed Identities take very little time to create, so you should be able to click the Go to resource button almost immediately to surf to the Overview page for your Managed Identity. From this page, copy the Client ID and save a copy of it to use later.

Using the Managed Identity

The next step is to assign the identity to your App Service:

1. In the Azure Portal, surf to your App Service.
2. In the menu on the left, expand the Settings menu and select Identity to display the Identity page on the right.
3. On the Identity page, switch to the User Assigned tab.
4. At the left end of the menu across the page, click the + Add choice to open the Add user assigned managed identity panel on the right.
5. In the panel, from the dropdown box at the top of the panel, pick the subscription you used for your Managed Identity.
6. In the User assigned managed identities textbox, start typing the name of your Managed Identity. When your Managed identity appears in the panel, put a checkmark beside it.
7. Click the panel’s Add button.

Updating the Web Service Code

You can add the code to your Web Service to access your Azure SQL database using the Managed Identity you assigned to the App Service. First, make sure that you have turned on Authorization in your service’s Program.cs file, like this:

app.UseAuthorization();

Any version of the SqlClient from Version 3 on reduces accessing your Azure SQL Database securely to configuring your connection string. Assuming that you’re using ADO.NET, your connection string must include these parameters:

• Server: The URL for your database server prefixed with “tcp:” (in my case, that’s tcp:warehousedbserver.database.windows.net)
• User Id: The Client ID of your managed identity that you copied earlier (i.e., not your managed identity’s name or object id)
• Initial Catalog: The name of your database (in my case, that’s WarehouseDB)
• Authentication: Set this to “Active Directory Managed Identity"

Putting all that together that means, in my case, that my connection string looks like this:

SqlConnection con =
  new SqlConnection(@"Server=tcp:warehousedbserver.database.windows.net;
  User Id=<managed identity Client Id from the Managed Identity’s Overview page>;
  Initial Catalog=WarehouseDB;
  Authentication='Active Directory Managed Identity';");

A minimal API in my .NET 8 Web Service’s Program.cs file that implements a GET method that returns an array of JSON objects from my Azure SQL database from <URL of my App Service>/products would look like this:

app.MapGet("/products", () =>
{
  List<Product> prds = new List<Product>();
  Product prd = null;
    SqlConnection con =
      new SqlConnection(@"Server=tcp:warehousedbserver.database.windows.net;
      User Id=<Client Id of the managed identity>;
      Initial Catalog=WarehouseDB;
      Authentication='Active Directory Managed Identity';");
  con.Open();
  using (SqlCommand command =
      new SqlCommand("Select * from SalesLT.Product", con))
  {
    using (SqlDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
        prd = new Product();
        prd.ProductId =
        reader.GetInt32("ProductId");
        prd.Name = reader.GetString("Name");
        prd.ListPrice = reader.GetDecimal("ListPrice");
        prds.Add(prd);
      }
    }
  }
  return prds;
})
.WithName("GetAllProducts")
.WithOpenApi();

Next Steps

While this is lovely code, it won’t work yet. The next step is to give your Managed Identity permission to access tables in your Azure SQL database—that’s my next post.

I should also point out that I’m not happy about including all this information in my source code where it’s available to anyone who can check out the code. I’ll fix that issue in a later post where I’ll integrate the Azure’s Key Vault and App Configuration. You also shouldn’t take the simplicity of using a managed identity with Azure SQL as typical—there’s more to be done, as you’ll see when I create a frontend for this application.

But the next post will get this code to work.