Azure SQL Server Provisioning Requires Admin

[eerhardt]

Description:

When provisioning SQL Server, it currently requires an admin to be specified at provision time. This requirement poses challenges for automation because we need to provide an admin. But the way our role assignment bicep modules are generated, they expect the resource to already be created - causing a chicken and egg problem.

To work around this issue for now, we generate an empty "KnownParameters" parameter for PrincipalId:

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
	},

When using local provisioning/F5 and when not using ACA infrastructure, this works just fine as there is only a single Principal to be considered - either the developer or the single managed identity used for the whole app.

However, when using ACA infrastructure (or any future compute infrastructure that supports targeted role assignments) this poses a problem because this empty "KnownParameters" parameter for PrincipalId needs to be filled in.

For now when using ACA infrastructure the PrincipalId gets filled in with the same managed identity that is used for AcrPulls for the container apps.

aspire/src/Aspire.Hosting.Azure.AppContainers/AzureContainerAppsInfrastructure.cs

Lines 94 to 96 in 73459e9

	// Set the known parameters for the container app environment
	SetKnownParameterValue(r, AzureBicepResource.KnownParameters.PrincipalId, _ => environment.ContainerRegistryManagedIdentityId);
	SetKnownParameterValue(r, AzureBicepResource.KnownParameters.PrincipalType, _ => "ServicePrincipal");

This works, but isn't ideal because we are abusing this managed identity, which is only supposed to be used for the Azure Container Registry.

Potential Solution

One idea to fix this is to scan all the resources, finding one compute app (Project or Container) that references the Azure SqlServer, and use its managed identity as the initial SQL Server admin.

Another idea is to get SQL Server to relax their restrictions that an Admin needs to be specified at provision time. @JerryNixon - would this be possible?

cc @davidfowl