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

[eerhardt]

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:

aspire/src/Aspire.Hosting.Azure.Sql/AzureSqlExtensions.cs

Lines 213 to 229 in 73459e9

	// Creating a new SqlServer instance requires an administrator,
	// so we need to create one here using the empty PrincipalId/PrincipalName
	var principalIdParameter = new ProvisioningParameter(AzureBicepResource.KnownParameters.PrincipalId, typeof(string));
	infrastructure.Add(principalIdParameter);
	var principalNameParameter = new ProvisioningParameter(AzureBicepResource.KnownParameters.PrincipalName, typeof(string));
	infrastructure.Add(principalNameParameter);
	return new SqlServer(infrastructure.AspireResource.GetBicepIdentifier())
	{
	Administrators = new ServerExternalAdministrator()
	{
	AdministratorType = SqlAdministratorType.ActiveDirectory,
	IsAzureADOnlyAuthenticationEnabled = true,
	Sid = principalIdParameter,
	Login = principalNameParameter,
	TenantId = BicepFunction.GetSubscription().TenantId
	},

aspire/src/Aspire.Hosting.Azure.Sql/AzureSqlExtensions.cs

Lines 284 to 294 in 73459e9

	internal static SqlServerAzureADAdministrator AddActiveDirectoryAdministrator(AzureResourceInfrastructure infra, SqlServer sqlServer, BicepValue<Guid> principalId, BicepValue<string> principalName)
	{
	var admin = new SqlServerAzureADAdministratorWorkaround($"{sqlServer.BicepIdentifier}_admin")
	{
	ParentOverride = sqlServer,
	LoginOverride = principalName,
	SidOverride = principalId
	};
	infra.Add(admin);
	return admin;
	}

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

[eerhardt]

Here's an example I got from @karolz-ms that might do the trick that we need:

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

resource sqlDeploymentScript 'Microsoft.Resources/deploymentScripts@2020-10-01' = {
  name: '${name}-deployment-script'
  location: location
  kind: 'AzureCLI'
  properties: {
    azCliVersion: '2.37.0'
    retentionInterval: 'PT1H' // Retain the script resource for 1 hour after it ends running
    timeout: 'PT5M' // Five minutes
    cleanupPreference: 'OnSuccess'
    environmentVariables: [
      {
        name: 'APPUSERNAME'
        value: appUser
      }
      {
        name: 'APPUSERPASSWORD'
        secureValue: appUserPassword
      }
      {
        name: 'DBNAME'
        value: databaseName
      }
      {
        name: 'DBSERVER'
        value: sqlServer.properties.fullyQualifiedDomainName
      }
      {
        name: 'SQLCMDPASSWORD'
        secureValue: sqlAdminPassword
      }
      {
        name: 'SQLADMIN'
        value: sqlAdmin
      }
    ]


    scriptContent: '''
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
    '''
  }
}

[mitchdenny]

We should try this to see if it works, but man its ugly :)

[davidfowl]

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 database 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  name: '${sqlServer.name}/${databaseName}'
  location: location
  sku: {
    name: 'S0'
    tier: 'Standard'
  }
}

// Allow Azure services firewall rule
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'
  }
}

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

// User-assigned Managed Identity for Deployment Script
resource scriptIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: 'sqlDeploymentScriptIdentity'
  location: location
}

// Give the managed identity access as a user in the database by assigning SQL Contributor role
resource scriptRoleAssignment 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(scriptIdentity.id, 'sql-contributor-role')
  scope: sqlServer
  properties: {
    roleDefinitionId: subscriptionResourceId('Microsoft.Authorization/roleDefinitions', '9b7fa17d-e63e-47b0-bb0a-15c516ac86ec') // SQL Contributor
    principalId: scriptIdentity.properties.principalId
    principalType: 'ServicePrincipal'
  }
}

// Deployment Script to create SQL user without AAD passwords
resource createSqlUserScript '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'
    scriptContent: '''
      echo "Obtaining access token for SQL..."
      token=$(az account get-access-token --resource=https://database.windows.net --query accessToken -o tsv)

      echo "Creating Azure AD user in SQL database..."
      az sql db query \
        --server ${sqlServer.name} \
        --database ${database.name} \
        --access-token "$token" \
        --query "
          CREATE USER [${aadUserLogin}] FROM EXTERNAL PROVIDER;
          ALTER ROLE db_datareader ADD MEMBER [${aadUserLogin}];
          ALTER ROLE db_datawriter ADD MEMBER [${aadUserLogin}];
        "
    '''
  }
}

output sqlServerFQDN string = sqlServer.properties.fullyQualifiedDomainName

[eerhardt]

FYI - this can break existing apps that use ACA infrastructure and connect to Azure SQL Server from more than 1 Container App.