Dockerize the dotnet sql tuto application to use a managed identity

This article will detail how I modified the dotnet core / SQL Server tutorial application proposed by Microsoft to use an Azure Managed identity and publish it as a Docker image.

Purpose

In another article of this blog, I explored the use of Azure Active Directory pod identity to connect an application running in a Kubernetes pod to an Azure SQL Server database. As I could not find a ready to use image containing the required modifications to the application, I add to build it myself and this post is about how I managed to do it.

Warning

I want to clearly state that I’m far from being a dotnet core developer and if you’re one, maybe you’ll read some stuff here that will make you scream. I beg your pardon :)

Prepare sample dotnet application using SQL database

We will test the scenario by deploying a sample dotnet core application that connects to a SQL Server database. The sample application is available in this Github repository.

Note: you must have dotnet 5.0.x SDK installed on your workstation to execute the following tasks.

First clone the repository.

git clone https://github.com/Azure-Samples/dotnetcore-sqldb-tutorial.git

Open the folder of the cloned repo in your favorite code editor.

Modify the file DotNetCoreSqlDb.csproj to target the correct dotnet framework (the application looks for dotnet core 3.1 framework).

 <TargetFramework>netcoreapp5</TargetFramework>

Install dotnet-ef tool and modify your shell path to include the folder with the tool, then run the migrations that will create the local database.

# Install dotnet-ef tool
dotnet tool install -g dotnet-ef

# Modify your PATH
export PATH=~/.dotnet/tools:$PATH

# Run database migrations
dotnet ef database update

# Run the application locally
dotnet run

To test the application, open a web browser to http://localhost:5000 and create some todos.

fig1

Now that we have tested the application with a local database, we will configure it to connect to Azure SQL Server database. We use the classic userid/password method as we still run the application on our development workstation.

Note: make sure that the Azure SQL firewall is configured to allow your development workstation ip address to connect to the database.

Get the ADO.NET connection strings to your database. Use the SQL authentication variant.

Example:

Server=tcp:sqlsrv.database.windows.net,1433;Initial Catalog=mydb;Persist Security Info=False;User ID=mydbuser;Password=mypassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Configure this connection string in your file Startup.cs. Find the code :

services.AddDbContext<MyDatabaseContext>(options =>
        options.UseSqlite("Data Source=localdatabase.db"));

Replace it by :

services.AddDbContext<MyDatabaseContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("MyDbConnection")));

Now cleanup Migrations and rerun them with the database connection using our Azure SQL database.

# Delete old migrations
rm -r Migrations

# Set the new connection string
export ConnectionStrings__MyDbConnection=<unsafe-connection-string-with-user-pwd>

# Recreate migrations
dotnet ef migrations add InitialCreate

# Run the migrations againt Azure SQL Server database
dotnet ef database update

# Test the application connected to the Azure SQL database
dotnet run

If the test is ok (it was for me), publish the dotnet code in ./app folder.

dotnet publish -c release -o ./app

Build first Docker image

As it is the first time I work with dotnet, I want to first try to package that working application into a Docker image. I will use a Docker image from Microsoft based on dotnet 5.0 runtime and alpine linux.

Create the file Dockerfile with the following content.

FROM mcr.microsoft.com/dotnet/aspnet:5.0-alpine
# Next 2 lines needed to avoid error when trying to connect to SQL Server
# See https://github.com/dotnet/SqlClient/issues/220
RUN apk add icu-libs
ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=false
WORKDIR /app
COPY ./app ./
ENTRYPOINT ["dotnet", "DotNetCoreSqlDb.dll"]

Build the image and test it still locally (very important because we have a username/password in clear there).

# Build the Docker image
docker build -t dotnetcoresqldb .

# Test the image on your workstation
docker run -d -p 8080:80 --name appsql dotnetcoresqldb

If test is ok, it’s time to configure the application to connect to Azure SQL database with a managed identity.

Modify application to connect with Manages Identity

Add the package for Azure AD authentication with Managed Identity to the application.

dotnet add package Microsoft.Azure.Services.AppAuthentication --version 1.6.0

Modify the file appsettings.json to use the safest database connection string.

"ConnectionStrings": {
    "MyDbConnection": "Server=tcp:sqlsrv.database.windows.net,1433;Initial Catalog=mydb;"
  }

Add the following code in Data/MyDatabaseContext.cs file to use a Managed Identity during the Azure SQL connection creation.

public MyDatabaseContext (DbContextOptions<MyDatabaseContext> options)
            : base(options)
        {
            // Added following 2 lines to support Azure SQL DB access through Managed Identity
            var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
            conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider())
                     .GetAccessTokenAsync("https://database.windows.net/")
                     .Result;

        }

Publish the new version of the code and rebuild the Docker image.

# Publish new version of the code
dotnet publish -c release -o ./app

# Build the Docker image
docker build -t dotnetcoresqldb .

Publish the Docker image to Azure Container Registry

# Login to the Azure Container Registry
az acr login --name myregistry
Login Succeeded

# Tag the Docker image
docker tag dotnetcoresqldb myregistry.azurecr.io/samples/dotnetcoresqldb

# Push the image to ACR
docker push myregistry.azurecr.io/samples/dotnetcoresqldb

Conclusion

Now you’re ready to test the application in an AKS cluster with AAD pod identity feature deployed and configured. To see if it was successful, go read my other article if not already done ;)