Aspire Azure SQL provisioning fails

[douglasshelby]

Is there an existing issue for this?

• I have searched the existing issues

Describe the bug

I've not been able to successfully aspire deploy and app I'm working on, the -roles-sql- deployment runs for almost 20 minutes, and eventually fails.

The deployment script activity log shows the information below. The symptom appear to be similar to what was reported in this issue: #9926. I'm pretty new to Azure generally, there may more information I can elsewhere, just not sure where.

      "provisioningState": "ProvisioningResources",
      "retentionInterval": "PT1H",
      "scriptContent": "$sqlServerFqdn = \"$env:DBSERVER\"\r\n$sqlDatabaseName = \"$env:DBNAME\"\r\n$principalName = \"$env:PRINCIPALNAME\"\r\n$id = \"$env:ID\"\r\n\r\n# Install SqlServer module - using specific version to avoid breaking changes in 22.4.5.1 (see https://github.com/dotnet/aspire/issues/9926)\r\nInstall-Module -Name SqlServer -RequiredVersion 22.3.0 -Force -AllowClobber -Scope CurrentUser\r\nImport-Module SqlServer\r\n\r\n$sqlCmd = @\"\r\nDECLARE @name SYSNAME = '$principalName';\r\nDECLARE @id UNIQUEIDENTIFIER = '$id';\r\n\r\n-- Convert the guid to the right type\r\nDECLARE @castId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @id), 1);\r\n\r\n-- Construct command: CREATE USER [@name] WITH SID = @castId, TYPE = E;\r\nDECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @name + '] WITH SID = ' + @castId + ', TYPE = E;'\r\nEXEC (@cmd);\r\n\r\n-- Assign roles to the new user\r\nDECLARE @role1 NVARCHAR(MAX) = N'ALTER ROLE db_owner ADD MEMBER [' + @name + ']';\r\nEXEC (@role1);\r\n\r\n\"@\r\n# Note: the string terminator must not have whitespace before it, therefore it is not indented.\r\n\r\nWrite-Host $sqlCmd\r\n\r\n$connectionString = \"Server=tcp:${sqlServerFqdn},1433;Initial Catalog=${sqlDatabaseName};Authentication=Active Directory Default;\"\r\n\r\nInvoke-Sqlcmd -ConnectionString $connectionString -Query $sqlCmd",
      "status": {
        "error": {
          "code": "DeploymentScriptOperationFailed",
          "message": "Encountered internal server error. Diagnostic information: timestamp '20251222T012737Z', subscription id 'c489932e-61b9-4458-86a6-49ddc4f10970', tracking id '8128b5d2-b7df-41b7-a80f-2a7f9419c6f6', request correlation id '58ea13d4-5b12-4610-9146-04d21df3028f'."
        },
        "startTime": "2025-12-22T01:34:32.579Z"
      },

Expected Behavior

Successful deployment

Steps To Reproduce

I used the Aspire Sample App. I added the AzureContainerAppEnvironment and AzureSqlServer to the AppHost

aspire publish
aspire deploy

Program.cs

var builder = DistributedApplication.CreateBuilder(args);

builder.AddAzureContainerAppEnvironment("env");

var sql = builder.AddAzureSqlServer("sql")
    .RunAsContainer(container =>
    {
        container.WithDataVolume();
        container.WithLifetime(ContainerLifetime.Persistent);
    });

var db = sql.AddDatabase("db");

var apiService = builder.AddProject<Projects.AspireApp1_ApiService>("apiservice")
    .WithHttpHealthCheck("/health");

builder.AddProject<Projects.AspireApp1_Web>("webfrontend")
    .WithExternalHttpEndpoints()
    .WithHttpHealthCheck("/health")
    .WithReference(apiService)
    .WithReference(db)
    .WaitFor(db)
    .WaitFor(apiService);

builder.Build().Run();

Exceptions (if any)

No response

.NET Version info

Aspire SDK
<Project Sdk="Aspire.AppHost.Sdk/13.0.2">
Aspire CLI
13.1.0+8a4db1775c3fbae1c602022b636299cb04971fde

.NET SDK:
Version: 10.0.101
Commit: fad253f51b
Workload version: 10.0.101.1
MSBuild version: 18.0.6+fad253f51

Runtime Environment:
OS Name: Windows
OS Version: 10.0.22631
OS Platform: Windows
RID: win-x64
Base Path: C:\Program Files\dotnet\sdk\10.0.101\

.NET workloads installed:
There are no installed workloads to display.
Configured to use workload sets when installing new manifests.

Host:
Version: 10.0.1
Architecture: x64
Commit: fad253f51b

.NET SDKs installed:
6.0.428 [C:\Program Files\dotnet\sdk]
9.0.112 [C:\Program Files\dotnet\sdk]
9.0.308 [C:\Program Files\dotnet\sdk]
10.0.101 [C:\Program Files\dotnet\sdk]

.NET runtimes installed:
Microsoft.AspNetCore.App 6.0.36 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 8.0.22 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 9.0.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 10.0.1 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.NETCore.App 6.0.36 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 8.0.22 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 9.0.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 10.0.1 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.WindowsDesktop.App 6.0.36 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 8.0.22 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 9.0.11 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 10.0.1 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

Other architectures found:
x86 [C:\Program Files (x86)\dotnet]
registered at [HKLM\SOFTWARE\dotnet\Setup\InstalledVersions\x86\InstallLocation]

Environment variables:
Not set

global.json file:
Not found

Learn more:
https://aka.ms/dotnet/info

Download .NET:
https://aka.ms/dotnet/download

Anything else?

No response

[davidfowl]

@sebastienros

[vipervf1]

I'm seeing the same issue even with the latest packages.

<Project Sdk="Aspire.AppHost.Sdk/13.1.0">
<PackageReference Include="Aspire.Hosting.Azure.Sql" Version="13.1.0" />

[jabbots-contrib]

I'm also having this issue, the role SQL scripts just seem to indefinitely hang when provisioning (all other resources provision OK). This is with a setup with more than 1 container having access to the DB (Migration Service + API).

[rjpowers10]

I'm having the same issue. Any ETA?

[skywithin]

Root cause identified: principalId vs clientId SID mismatch

We hit this on Aspire 13.2.2 with AddAzureContainerAppEnvironment + AddAzureSqlServer and per-service user-assigned managed identities.

The generated *-roles-sql.module.bicep passes principalId (object ID) to the SQL provisioning script:

params: {
  principalId: "{boatswain-public-api-identity.outputs.principalId}"  // object ID
}

But the script uses this as the SID for CREATE USER ... WITH SID = ..., TYPE = E:

DECLARE @id UNIQUEIDENTIFIER = '$env:ID';  -- this is the principalId/objectId
DECLARE @castId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(16), @id), 1);
EXEC(N'CREATE USER [' + @name + '] WITH SID = ' + @castId + ', TYPE = E');

Azure SQL expects the SID to be the client ID (application ID) of the managed identity, not the object ID. The user gets created with the wrong SID, so when the app authenticates with Authentication="Active Directory Default", it presents a token with the correct client ID which doesn't match the stored SID - resulting in Login failed for user '<token-identified principal>'.

Why it hangs for 20 minutes: The deployment script runs inside an Azure Deployment Script container. Its Invoke-Sqlcmd connection attempt can hang when the script container's own managed identity fails to authenticate against SQL (same SID mismatch), blocking until the Deployment Script resource's timeout expires.

Workaround: We use a postprovision hook that runs after azd provision, drops users with wrong SIDs, and recreates them with the correct client ID:

-- @clientId   = managed identity's Application (client) ID, e.g. from: az identity show -n <name> -g <rg> --query clientId -o tsv
-- @identityName = managed identity resource name (used as the SQL principal name)

DECLARE @expectedSid VARBINARY(16) = CONVERT(VARBINARY(16), @clientId);
DECLARE @currentSid VARBINARY(MAX) = (SELECT sid FROM sys.database_principals WHERE name = @identityName);

IF @currentSid IS NOT NULL AND @currentSid != @expectedSid
BEGIN
    EXEC(N'DROP USER [' + @identityName + N']');  -- wrong SID from Aspire Bicep
END

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @identityName)
BEGIN
    DECLARE @castSid NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), @expectedSid, 1);
    EXEC(N'CREATE USER [' + @identityName + N'] WITH SID = ' + @castSid + N', TYPE = E');
    EXEC(N'ALTER ROLE db_owner ADD MEMBER [' + @identityName + N']');
END

The hook handles multiple per-service identities and uses an admin-swap ceremony (temporarily setting the CI/CD service principal as Azure SQL AD admin via az sql server ad-admin create, running the user creation SQL, then restoring the original admin).

The fix in Aspire: The Bicep template generated by Aspire.Hosting.Azure.Sql for the *-roles-sql* deployment script should pass the identity's clientId (application ID) instead of principalId (object ID) when constructing the SID. The provisioning setup starts in AzureSqlExtensions.cs where PrincipalId is assigned as the administrator SID.