Login failed for user '<token-identified principal>' after PublishAsAzureSqlDatabase

[4eversoft]

After deployment via 'azd up' the connection from my container apps to the Azure SQL database failed.

The SQL Server and the database are defined as follows in AppHost:

var travelLoungeMsSql = 
    builder
        .ExecutionContext.IsPublishMode ?
    builder
        .AddSqlServer("travellounge-mssql")
        .PublishAsAzureSqlDatabase() :
    builder
        .AddSqlServer("travellounge-mssql", password: msSqlPassword)
        .WithBindMount("../../.containers/mssql", "/var/opt/mssql/data");

var travelLoungeMsSqlCatalog = travelLoungeMsSql
    .AddDatabase("travellounge-mssql-catalog", "TravelLounge_Catalog");

Access to the database in the container app:

            app.AddSqlServerDbContext<ApplicationDbContext>("travellounge-mssql-catalog", configureDbContextOptions: options =>
            {
                options.UseSqlServer(sqlOptions =>
                {
                    sqlOptions.MigrationsAssembly(typeof(ApplicationDbContext).GetTypeInfo().Assembly.GetName().Name);
                });
                options.EnableSensitiveDataLogging();
            });

The managed identity is assigned as Entra admin to the SQL server but I have no idea what is missing and why the connection fails.

Packages used:

• "Aspire.Hosting.Azure" Version="8.0.1"

• "Aspire.Hosting.Azure.Sql" Version="8.0.1"

• "Aspire.Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.1"

[4eversoft]

I dug a little deeper and was able to solve this problem manually by adjusting the connection string:

1. the connection string was missing the user id for the managed identity ("User Id=[client-id-of-user-assigned-managed-identity]") so the login failed.

2. the database name was not transferred correctly. My database name contains an underscore (see above "TravelLounge_Catalog"), but this was omitted during deployment ("TravelLoungeCatalog") so the database was not found.

Both points still need to be fixed as part of a bug fix.

[mitchdenny]

Hrm. interesting this does indeed seem like a bug.

@vhvb1989 this smells like an azd issue to me. Thoughts?

[vhvb1989]

@mitchdenny , I used the SqlServerEndToEnd.AppHost project from the app host and added a db name with lowercase, like:

var builder = DistributedApplication.CreateBuilder(args);

var db1 = builder.AddSqlServer("sql1").PublishAsAzureSqlDatabase().AddDatabase("db1", "db_1");

I deployed the project to Azure and got this connection string

I can see the Authentication="Active Directory Default";Database=db_1 referring to the db name correctly. So, I wonder if this might be an issue on how the application service (the client) is parsing the connection string?

@4eversoft can you tell where are you looking at the connection string from? (azure portal+containerApp secrets? , debugging the client?, running locally?)

[4eversoft]

I looked at the connection string in the App Secrets and changed it there.

The database name from AppHost was transferred correctly to the connection string but the problem is that the SQL Server database was given a different name during provisioning and therefore no longer matches the connection string.

In my case, the database should be called TravelLounge_Catalog, but after deployment it is called TravelLoungeCatalogand therefore the container app cannot find it.

[vhvb1989]

Thank you @4eversoft , that helps.

@mitchdenny , there is a bug in the Aspire code that generates the bicep module for the database, it is written as:

resource sqlDatabase_7z4od6fRC 'Microsoft.Sql/servers/databases@2020-11-01-preview' = {
  parent: sqlServer_0cdK4Fear
  name: 'TravelLoungeCatalog'
  location: location
  properties: {
  }
}

Would that be an issue for CDK in the implementation for Aspire.Hosting.Azure.Sql package? @JoshLove-msft @tg-msft