Managed Identity User doesnt Authenticate

[MarkTallentire]

Hello.

I'm having an issue with a managed identity not authenticating when added to azuresql using this library.

 var detectAppService = new Web.WebApp(GetResourceName("WebApp"), new Web.WebAppArgs
        {
            ResourceGroupName = resourceGroup.Name,
            ServerFarmId = appServicePlan.Id,
            Kind = "Linux",
            Identity = new Web.Inputs.ManagedServiceIdentityArgs
            {
                Type = Web.ManagedServiceIdentityType.SystemAssigned
            },
         ---REDACTED---
        }
  var appServiceManagedIdentity = new Pulumiverse.Mssql.AzureadServicePrincipal(DetectStack.GetResourceName("SP"), new()
        {
            Name = detectAppService.Name,
            DatabaseId = sqlDb.Apply(getDatabaseResult => getDatabaseResult.Id),
            ClientId = detectAppService.Identity.Apply(x => x.PrincipalId)
        }, new CustomResourceOptions { Provider = providerMssql });

        var dataReaderRole = Pulumiverse.Mssql.GetDatabaseRole.Invoke(new()
        {
            Name = "db_datareader",
            DatabaseId = sqlDb.Apply(getDatabaseResult => getDatabaseResult.Id),
        }, new InvokeOptions { Provider = providerMssql });

        var databaseRoleAssignment = new Pulumiverse.Mssql.DatabaseRoleMember(DetectStack.GetResourceName("RA"), new()
        {
            RoleId = dataReaderRole.Apply(x => x.Id),
            MemberId = appServiceManagedIdentity.Id,
        }, new CustomResourceOptions { Provider = providerMssql });

This succesfully creates the user and I can see them inside SSMS but my APP Service throws an error Login failed for user '<token-identified principal>'.

However, if I delete this user and add a new one using ADD USER [APPSERVICENAME] FROM EXTERNAL PROVIDER and ALTER ROLE db_datareader ADD MEMBER [APPSERVICENAME]

Then everything works as expected.

Any ideas on what the issue could be?

[MarkTallentire]

I've resolved this by using the AzureAD library to fetch the service principal first. The line ClientId = detectAppService.Identity.Apply(x => x.PrincipalId) attempts to use the applications ObjectID where as we need the ApplicationID so we can convert between the two using the below code.

Note: The example for this resource shows using the Service Prinicipal Name to retrieve the service prinicpal from AzureAD but this doesnt work for resources created in the same stack due to invoke executing on known values before creation.

 var appServicePrincipal = Pulumi.AzureAD.GetServicePrincipal.Invoke(new()
        {
            ObjectId = detectAppService.Identity.Apply(x => x.PrincipalId)
        });

        var appServiceManagedIdentity = new Pulumiverse.Mssql.AzureadServicePrincipal(DetectStack.GetResourceName("SP"), new()
        {
            Name = detectAppService.Name,
            DatabaseId = sqlDb.Apply(getDatabaseResult => getDatabaseResult.Id),
            ClientId = appServicePrincipal.Apply(x => x.ApplicationId)
        }, new CustomResourceOptions { Provider = providerMssql });