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:
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
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 26 (24 by maintainers)
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 😄
ROTFL I focused only on the T-SQL part, didn’t even notice that there was a query command that doesn’t even exist 😱🤦♂️
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
Here a version from ChatGPT that uses managed identity instead of the admin password