aspire: Using multiple apps to talk to Azure SQL Server doesn't work with managed identites

Description

When you have 2 projects talking to the same Azure SQL Server, and you use an ACA environment, each app will get a separate managed identity.

This causes problems with Azure SQL Server because the way .NET Aspire grants access to the SQL Server is via setting the SQL Admin:

https://github.com/dotnet/aspire/blob/73459e96c8d05fb00b0fac5df43134ca6446ea7a/src/Aspire.Hosting.Azure.Sql/AzureSqlExtensions.cs#L213-L229

https://github.com/dotnet/aspire/blob/73459e96c8d05fb00b0fac5df43134ca6446ea7a/src/Aspire.Hosting.Azure.Sql/AzureSqlExtensions.cs#L284-L294

Doing it this way means that the last managed identity / app that runs its roles bicep will be the admin, and any previous apps will no longer be able to talk to the SQL Server, since they no longer have access.

Instead of adding managed identities this way, we need to be adding the managed identity as a USER and assigning it a ROLE. See https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql.

One idea on how to do this is to use a deployment script to run a SQL script that adds each app’s managed identity as a USER and adds appropriate roles for that user.

This is also highly related to Azure SQL Server Provisioning Requires Admin (dotnet/aspire#8381). Maybe we try to solve them both together? We could grant the userPrincipalId (i.e. whoever is doing the deployment) as the SQL Server admin, and then run a deployment script for each app that needs to talk to the SQL Server.

Repro

var builder = DistributedApplication.CreateBuilder(args);
builder.AddAzureContainerAppEnvironment("env");

var dbServer = builder.AddAzureSqlServer("sqlserver")
    .RunAsContainer(c => c.WithLifetime(ContainerLifetime.Persistent));

var todosDb = dbServer.AddDatabase("todosdb");

// The ApiDbService project is responsible for managing the database schema and seeding data.
var apiDbService = builder.AddProject<Projects.AspireStarterDb_ApiDbService>("apidbservice")
    .WithReference(todosDb)
    .WaitFor(todosDb)
    .WithHttpsHealthCheck("/health")
    .WithHttpsCommand("/reset-db", "Reset Database", iconName: "DatabaseLightning");

// The ApiService project provides backend HTTP APIs for the web frontend.
var apiService = builder.AddProject<Projects.AspireStarterDb_ApiService>("apiservice")
    .WithReference(todosDb)
    .WaitFor(apiDbService);
$ azd up

cc @sebastienros @davidfowl

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 26 (24 by maintainers)

Most upvoted comments

That’s why this runs as a deployment script as part of the ARM deployment.

Our customers need to write this stuff 😢. Can we make this easier in the service?

You don’t need to generate the token manually, you can just use the “-G” option. I’ll send the correct script later, now I’m AFK

Maybe time to add one 😄

@davidfowl did ChatGPT hallucinate the az sql db query command?

Does the code I linked from the Azure samples work?

https://github.com/Azure-Samples/todo-csharp-sql/blob/dc617f594a6b9d6da6511bde2ec90200fb05f3e5/infra/core/database/sqlserver/sqlserver.bicep#L46-L98

wget https://github.com/microsoft/go-sqlcmd/releases/download/v0.8.1/sqlcmd-v0.8.1-linux-x64.tar.bz2
tar x -f sqlcmd-v0.8.1-linux-x64.tar.bz2 -C .

cat <<SCRIPT_END > ./initDb.sql
drop user if exists ${APPUSERNAME}
go
create user ${APPUSERNAME} with password = '${APPUSERPASSWORD}'
go
alter role db_owner add member ${APPUSERNAME}
go
SCRIPT_END

./sqlcmd -S ${DBSERVER} -d ${DBNAME} -U ${SQLADMIN} -i ./initDb.sql

ROTFL I focused only on the T-SQL part, didn’t even notice that there was a query command that doesn’t even exist 😱🤦‍♂️

@davidfowl did ChatGPT hallucinate the az sql db query command?

Does the code I linked from the Azure samples work?

https://github.com/Azure-Samples/todo-csharp-sql/blob/dc617f594a6b9d6da6511bde2ec90200fb05f3e5/infra/core/database/sqlserver/sqlserver.bicep#L46-L98

wget https://github.com/microsoft/go-sqlcmd/releases/download/v0.8.1/sqlcmd-v0.8.1-linux-x64.tar.bz2
tar x -f sqlcmd-v0.8.1-linux-x64.tar.bz2 -C .

cat <<SCRIPT_END > ./initDb.sql
drop user if exists ${APPUSERNAME}
go
create user ${APPUSERNAME} with password = '${APPUSERPASSWORD}'
go
alter role db_owner add member ${APPUSERNAME}
go
SCRIPT_END

./sqlcmd -S ${DBSERVER} -d ${DBNAME} -U ${SQLADMIN} -i ./initDb.sql

Here a version from ChatGPT that uses managed identity instead of the admin password

param location string = resourceGroup().location
param sqlServerName string
param databaseName string
param sqlAdminUsername string
@secure()
param sqlAdminPassword string
param aadAdminLogin string
param aadAdminObjectId string
param aadUserLogin string

// Azure SQL Server
resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {
  name: sqlServerName
  location: location
  properties: {
    administratorLogin: sqlAdminUsername
    administratorLoginPassword: sqlAdminPassword
    version: '12.0'
  }
}

// Azure SQL Database
resource sqlDatabase 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  name: '${sqlServer.name}/${databaseName}'
  location: location
  sku: {
    name: 'S0'
    tier: 'Standard'
  }
}

// Firewall rule allowing Azure services
resource allowAzureServices 'Microsoft.Sql/servers/firewallRules@2023-08-01-preview' = {
  parent: sqlServer
  name: 'AllowAzureServices'
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
}

// Azure AD Admin configuration
resource aadAdmin 'Microsoft.Sql/servers/administrators@2022-02-01-preview' = {
  parent: sqlServer
  name: 'ActiveDirectory'
  properties: {
    administratorType: 'ActiveDirectory'
    login: aadAdminLogin
    sid: aadAdminObjectId
    tenantId: subscription().tenantId
  }
}

// Managed Identity for the deployment script
resource scriptIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: 'sqlDeploymentScriptIdentity'
  location: location
}

// Deployment script to create AAD user via sqlcmd
resource createSqlUser 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'createSqlUserScript'
  location: location
  kind: 'AzureCLI'
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${scriptIdentity.id}': {}
    }
  }
  properties: {
    azCliVersion: '2.59.0'
    retentionInterval: 'PT1H'
    environmentVariables: [
      { name: 'SQL_SERVER_FQDN', value: sqlServer.properties.fullyQualifiedDomainName }
      { name: 'DATABASE_NAME', value: databaseName }
      { name: 'AAD_USER_LOGIN', value: aadUserLogin }
    ]
    scriptContent: '''
    #!/bin/bash

    # Install SQL Server command-line tools (sqlcmd)
    curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
    curl https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/microsoft.gpg > /dev/null
    sudo apt-get update
    sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev
    export PATH=$PATH:/opt/mssql-tools/bin

    # Obtain Azure AD access token for Azure SQL
    TOKEN=$(az account get-access-token --resource=https://database.windows.net --query accessToken -o tsv)

    # Execute SQL commands to create Azure AD user and assign roles
    sqlcmd -S $SQL_SERVER_FQDN -d $DATABASE_NAME -G -U $SQL_SERVER_FQDN -P "$TOKEN" -Q "
      CREATE USER [$AAD_USER_LOGIN] FROM EXTERNAL PROVIDER;
      ALTER ROLE db_datareader ADD MEMBER [$AAD_USER_LOGIN];
      ALTER ROLE db_datawriter ADD MEMBER [$AAD_USER_LOGIN];
    "
    '''
  }
}

output sqlServerFQDN string = sqlServer.properties.fullyQualifiedDomainName