Passwordless access to Azure MySQL and Postgres end-to-end sample with .Net

5 min read1 day ago

So you want to connect your .Net application to Azure Database for MySQL or Postgres using Azure AD authentication, but you don’t know how. In this example, you will find how to deploy an ASP.net core application in an Azure Container that, using a Managed Identity, is able to connect to an Azure Database for MySQL or Azure Database for Postgres. To make it easy, I will use Batec extensions to simplify the implementation in the dotnet client side. You can read my previous story to learn more about Batec extensions.

The application for the demo is based on the todo-csharp-sql sample, but:

Uses Azure Database for MySQL and Azure Database for Postgres instead of SQL Server.
Uses Container Apps.
There are no passwords; it doesn’t require an Azure Key Vault to store secrets. Instead it uses a User Assigned Managed Identity.

The first thing I did was use Nubesgen to generate a base Terraform template to generate the infrastructure. This simple tool can generate a Terraform configuration for Azure following best practices and naming conventions. For instance, I used this link to generate my initial Terraform configuration:

curl "https://nubesgen.com/batec-ossrdbms-demo.zip?iactool=TERRAFORM&region=northeurope&application=CONTAINER_APPS.basic&runtime=DOCKER&database=POSTGRESQL.basic" | tar -xzvf -

You can customize that base configuration for your specific scenario. I adapted the configuration with the following actions:

Configure database authentication to use AAD authentication only. Hence removing database passwords.

Create a user-assigned managed identity to be used by the applications to access the database.

Configure the user-assigned managed identity with permissions to pull images from an Azure Container Registry.

Move container app creation out of the Terraform configuration. Apps will be created from the Azure cli, as I’ll explain later.

Now, it is possible to create the users in the databases linked to the Managed Identities.

In Azure Database for Postgres, you can create a user by executing the following SQL commands:

select * from pgaadauth_create_principal_with_oid('${APPLICATION_LOGIN_NAME}', '${APPLICATION_IDENTITY_APPID}', 'service', false, false);
GRANT ALL PRIVILEGES ON DATABASE "${DATABASE_NAME}" TO "${APPLICATION_LOGIN_NAME}";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "${APPLICATION_LOGIN_NAME}";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "${APPLICATION_LOGIN_NAME}";

The first command creates a user who authenticates with Azure AD, while the last three commands grant database permissions.

In Azure MySQL, you can create the user by executing the following commands:

CREATE AADUSER '${APPLICATION_LOGIN_NAME}' IDENTIFIED BY '${APPLICATION_IDENTITY_APPID}';
GRANT ALL PRIVILEGES ON ${DATABASE_NAME}.* TO '${APPLICATION_LOGIN_NAME}'@'%';

To execute these SQL commands, you can use your favorite database client tool, or you can use Azure CLI extension rdbms-connect.

For instance, for MySQL you can execute:

az mysql flexible-server execute --name ${MYSQL_SERVER} --file-path mysqluser.sql --admin-password "${ADMIN_PASSWORD}" --admin-user "${ADMIN_USER}" --verbose

Now that the databases are created, you can connect your application to the databases. First, add the package dependencies:

Batec.Azure.Data.Extensions.Pomelo.EntityFrameworkCore - If you want to use MySQL.
Batec.Azure.Data.Extensions.Npgsql.EntityFrameworkCore - If you want to use Postgresql.
Azure.Identity - To use Azure Active Directory authentication.

Configure the database connection during application startup using a TokenCredential. The most convenient TokenCredential implementation for a demo application like this is DefaultAzureCredential. This implementation will try to authenticate using different methods, like Managed Identity, Azure CLI, Visual Studio, and so on. With this approach it is possible to test from our local development computer. Note that Terraform script uses the logged-in user in Azure cli as the Database AAD admin. This means that you will be able to use your Azure cli user to connect to the database. The code will look like this:

DefaultAzureCredential azureCredential;
if (string.IsNullOrEmpty(builder.Configuration["UserAssignedManagedClientId"]))
{
    azureCredential = new DefaultAzureCredential();
}
else
{
    azureCredential = new DefaultAzureCredential(new DefaultAzureCredentialOptions
    {
        ManagedIdentityClientId = builder.Configuration["UserAssignedManagedClientId"]
    });
}
// use this code for Postgres
builder.Services.AddDbContext<TodoDb>(options =>
{
    string npgConnString = builder.Configuration["PgSqlConnection"] ?? throw new InvalidOperationException("PgSqlConnection must be set in the configuration");
    options
            .UseNpgsql(npgConnString, npgsqlOptions => npgsqlOptions.UseAzureADAuthentication(azureCredential).EnableRetryOnFailure());
});

// use this code for MySQL
builder.Services.AddDbContext<TodoDb>(options =>
{
    string mysqlConnString = builder.Configuration["MySqlConnection"] ?? throw new InvalidOperationException("MySqlConnection must be set in the configuration");
    var serverVersion = ServerVersion.Parse("5.7", ServerType.MySql);
    options
        .UseMySql(mysqlConnString, serverVersion, options => options.EnableRetryOnFailure())
        .UseAzureADAuthentication(azureCredential);
});

Note that using Azure AD authentication only requires calling the UseAzureADAuthentication extension method andpassing a TokenCredential.

Now, you can build the Docker images and deploy themto Azure Container Apps. You can use your local Docker tools to create the images and upload them to Azure Container Registry or use Azure Container Registry to build them directly. I’ll use Azure Container Registry to build the images. For that, in the folder containing the code, execute the following command:

az acr build -t $ACR_NAME.azurecr.io/todoapi:latest -t $ACR_NAME.azurecr.io/todoapi:1.0.0 -r $ACR_NAME .

Then you can deploy to Azure Container Apps:

# Deploy MySQL API application
az containerapp create -n mysqlapi -g ${RESOURCE_GROUP} \
    --image ${ACR_NAME}.azurecr.io/todoapi:1.0.0 --environment ${CONTAINER_APP_ENVIRONMENT} \
    --ingress external --target-port 80 \
    --registry-server ${ACR_NAME}.azurecr.io --registry-identity "${MSI_CONTAINER_IDENTITY}" \
    --user-assigned ${MSI_CONTAINER_IDENTITY} \
    --cpu 0.25 --memory 0.5Gi \
    --env-vars TargetDb="MySql" MySqlConnection="${MYSQL_CONNECTION_STRING};UserID=${MSI_LOGIN_NAME};" UserAssignedManagedClientId="${APPLICATION_IDENTITY_APPID}"
# Configure CORS on MySQL API application to allow access from any Web application
az containerapp ingress cors update -n mysqlapi -g ${RESOURCE_GROUP} --allowed-origins "*" --allowed-methods "*"

# Deploy Postgres API application
az containerapp create -n pgsqlapi -g ${RESOURCE_GROUP} \
    --image ${ACR_NAME}.azurecr.io/todoapi:1.0.0 --environment ${CONTAINER_APP_ENVIRONMENT} \
    --ingress external --target-port 80 \
    --registry-server ${ACR_NAME}.azurecr.io --registry-identity "${MSI_CONTAINER_IDENTITY}" \
    --user-assigned ${MSI_CONTAINER_IDENTITY} \
    --cpu 0.25 --memory 0.5Gi \
    --env-vars TargetDb="Postgresql" PgSqlConnection="${PGSQL_CONNECTION_STRING};User Id=${MSI_LOGIN_NAME};" UserAssignedManagedClientId="${APPLICATION_IDENTITY_APPID}"
# Configure CORS on Postgres API application to allow access from any Web application
az containerapp ingress cors update -n pgsqlapi -g ${RESOURCE_GROUP} --allowed-origins "*" --allowed-methods "*"

# Get MySQL API application FQDN
MYSQLAPI_FQDN=$(az containerapp show -n mysqlapi -g ${RESOURCE_GROUP} -o tsv --query "properties.configuration.ingress.fqdn")
# Get Postgres API application FQDN
PGSQLAPI_FQDN=$(az containerapp show -n pgsqlapi -g ${RESOURCE_GROUP} -o tsv --query "properties.configuration.ingress.fqdn")

# Deploy MySQL Web application
az containerapp create -n mysqlweb -g ${RESOURCE_GROUP} \
    --image ${ACR_NAME}.azurecr.io/todoweb:1.0.0 --environment ${CONTAINER_APP_ENVIRONMENT} \
    --ingress external --target-port 80 \
    --registry-server ${ACR_NAME}.azurecr.io --registry-identity "${MSI_CONTAINER_IDENTITY}" \
    --cpu 0.25 --memory 0.5Gi \
    --env-vars REACT_APP_API_BASE_URL="https://${MYSQLAPI_FQDN}" REACT_APP_APPLICATIONINSIGHTS_CONNECTION_STRING="${APPINSIGHTS_CONNECTIONSTRING}"

# Deploy Postgres Web application
az containerapp create -n pgsqlweb -g ${RESOURCE_GROUP} \
    --image ${ACR_NAME}.azurecr.io/todoweb:1.0.0 --environment ${CONTAINER_APP_ENVIRONMENT} \
    --ingress external --target-port 80 \
    --registry-server ${ACR_NAME}.azurecr.io --registry-identity "${MSI_CONTAINER_IDENTITY}" \
    --cpu 0.25 --memory 0.5Gi \
    --env-vars REACT_APP_API_BASE_URL="https://${PGSQLAPI_FQDN}"  REACT_APP_APPLICATIONINSIGHTS_CONNECTION_STRING="${APPINSIGHTS_CONNECTIONSTRING}"

The above commands deployed two API applications, one for Postgres, andone for MySQL. The same goes for Web applications, withone pointing to each API application.

In this example, I intentionally skipped some details to make it easier to follow, but if you want to review the detailed deployment instructions, you can check the repo on felipmiguel/batec-ossrdbms-demo (github.com). It also contains scripts to deploy all components end to end without user interactions.

Using Batec extensions in your project is quite simple, and it follows best practices for accessing the database. However, as explained in this article, Using Azure AD authentication can be difficult if you don’t have these libraries to facilitate the integration with Azure AD.

I hope you enjoy this article.

Passwordless access to Azure MySQL and Postgres end-to-end sample with .Net

Written by Felip Miguel Puig