Connect Azure SQL Server via System Assigned Managed Identity under ASP.NET

Microsoft

Apr 09, 2025

Why we use it

This tutorial will introduce how to integrate Microsoft Entra with Azure SQL Server to avoid using fixed usernames and passwords. By utilizing System-assigned managed identities as a programmatic bridge, it becomes easier for Azure-related PaaS services (such as Container Apps) to communicate with the database without storing connection information in plain text.

Architecture

I will introduce each service or component and their configurations in subsequent chapters according to the order of A-C:

A: The company's account administrator needs to create or designate a user as the database administrator. This role can only be assigned to one person within the database and is responsible for basic configuration and the creation and maintenance of other database users. It is not intended for development or actual system operations.

B: The company's development department needs to create a Container App (or other service) as the basic unit of the business system. Programmers within this unit will write business logic (e.g., accessing the database) and deploy it here.

C: The company's data department needs to create or maintain a database and designate Microsoft Entra as the only login method, eliminating other fixed username/password combinations.

How to use it

A: As this article does not dive into the detailed configuration of Microsoft Entra, it will only outline the process. The company's account administrator needs to create or designate a user as the database administrator. In this example, we will call this user "cch," and the account, "cch@thexxxxxxxxxxxx" will be used in subsequent steps.

B-1: In this example, we can create a Container App with any SKU/region. Please note that during the initial setup, we will temporarily use the nginx:latest image from docker.io. After creating our own ASP.NET image, we will update it accordingly. For testing convenience, please enable Ingress traffic and allow requests from all regions.

Once the Container App has been created, please enable the System Assigned Managed Identity.

Lastly, please make a note of your App Name (e.g., mine is az-1767-aca) as we will use it in the following steps.

C-1: Create a database/SQL server. During this process, you need to specify the user created in Step A as the database administrator. Please note that to select "Microsoft Entra-only authentication." In this mode, the username/password will no longer be used. Then, click on "Next: Networking."

(Although the image shows that both Microsoft Entra and Username & Password login methods are selected, for security reasons, it is strongly recommended to choose Microsoft Entra Only. The Username & Password option will not be used in this tutorial.)

Since this article does not cover the detailed network configuration of the database, temporarily allow public access during the tutorial. Use the default values for other settings, click on "Review + Create," and then click "Create" to finish the setup.

During this process, you need to specify the system-assigned managed identity created in Step B as the entity that will actually operate the database.

And leave it default from the rest of the parts, and finally create the Database.

C-2: After the database has created, you can log in using the identity "cch@thexxxxxxxxxxxx" you've get from Step A which is the database administrator. Open a PowerShell terminal and using the "cch" account, enter the following command to log in to SQL Server. You will need to change the <text> to follow your company's naming conventions.

sqlcmd -S <YOUR_SERVER_NAME>.database.windows.net -d <YOUR_DB_NAME> -U <YOUR_FULL_USER_EMAIL> -G

You will be prompt for a 2 step verification.

dentities setup from Step B. First, we will introduce the method for the system-assigned managed identity. The purpose of the commands is to grant database-related operational permissions to the newly created user. This is just an example. In actual scenarios, you should follow your company's security policies and make the necessary adjustments accordingly. Please enter the following command.

CREATE USER [<YOUR_APP_NAME>] FROM EXTERNAL PROVIDER;
USE [<YOUR_DB_NAME>];
EXEC sp_addrolemember 'db_owner', '<YOUR_APP_NAME>';

For testing purposes, we will create a test table, and insert some data.

CREATE TABLE TestTable (
    Column1 INT,
    Column2 NVARCHAR(100)
);
INSERT INTO TestTable (Column1, Column2) VALUES (1, 'First Record');
INSERT INTO TestTable (Column1, Column2) VALUES (2, 'Second Record');

B-2: Developers can now start building the Docker image. In my sample development environment, I'm using .NET 8.0. Run the following command in your development environment to create a Hello World project:

dotnet new web -n WebApp --no-https

This command will generate many files used for the project. You will need to modify both Program.cs and WebApp.csproj.

using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/", async context =>
{
    var response = context.Response;

    var connectionString = "Server=az-1767-dbserver.database.windows.net;Database=az-1767-db;Authentication=ActiveDirectoryMsi;TrustServerCertificate=True;";

    await response.WriteAsync("Hello World\n\n");

    try
    {
        using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync();

        var cmd = new SqlCommand("SELECT Column1, Column2 FROM TestTable", conn);
        using var reader = await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            var line = $"{reader.GetInt32(0)} - {reader.GetString(1)}";
            await response.WriteAsync(line + "\n");
        }
    }
    catch (Exception ex)
    {
        await response.WriteAsync($"[Error] {ex.Message}");
    }
});

app.Run("http://0.0.0.0:80");

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.4" />
  </ItemGroup>
</Project>

Please note the connectionString in Program.cs. The string must follow a specific format — you’ll need to replace az-1767-dbserver and az-1767-db with your own server and database names.

After making the modifications, run the following command in the development environment. It will compile the project into a DLL and immediately run it (press Ctrl+C to stop).

dotnet run

Once the build is complete, you can package the entire project into a Docker image. Create a Dockerfile in the root of your project.

FROM mcr.microsoft.com/dotnet/sdk:8.0
# Install ODBC Driver
RUN apt-get update \
    && apt-get install -y unixodbc odbcinst unixodbc-dev curl vim \
    && curl -sSL -O https://packages.microsoft.com/debian/12/prod/pool/main/m/msodbcsql18/msodbcsql18_18.5.1.1-1_amd64.deb \
    && ACCEPT_EULA=Y DEBIAN_FRONTEND=noninteractive dpkg -i msodbcsql18_18.5.1.1-1_amd64.deb \
    && rm msodbcsql18_18.5.1.1-1_amd64.deb
# Setup Project Code
RUN mkdir /WebApp
COPY ./WebApp /WebApp
# OTHER
EXPOSE 80
CMD ["dotnet", "/WebApp/bin/Debug/net8.0/WebApp.dll"]

In this case, we are using mcr.microsoft.com/dotnet/sdk:8.0 as the base image. To allow access to Azure SQL DB, you’ll also need to install the ODBC driver in the image.

Use the following command to build the image and push it to your Docker Hub (docker.io). Please adjust the image tag, for example az-1767-aca:202504091739 can be renamed to your preferred version, and replace theringe with your own Docker Hub username.

docker build -t az-1767-aca:202504091739 . --no-cache
docker tag az-1767-aca:202504091739 theringe/az-1767-aca:202504091739
docker push theringe/az-1767-aca:202504091739

After building and uploading the image, go back to your Container App and update the image configuration.