SQL Server deployment scripts fail when private endpoints are used (PublicNetworkAccess=Disabled)

[mitchdenny]

Description

When AddPrivateEndpoint is used with an AzureSqlServerResource, the PublicNetworkAccess is set to Disabled. However, the AddRoleAssignments method in AzureSqlServerResource.cs uses an AzurePowerShellScript deployment script that connects to the SQL Server over the public FQDN to create database users:

$connectionString = "Server=tcp:${sqlServerFqdn},1433;Initial Catalog=${sqlDatabaseName};Authentication=Active Directory Default;"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $sqlCmd

The Azure deployment script container runs outside the VNet, so when public network access is disabled, the TCP connection is blocked and the deployment fails with DeploymentFailed on the role assignment step.

Reproduction

var vnet = builder.AddAzureVirtualNetwork("vnet");
var acaSubnet = vnet.AddSubnet("aca-subnet", "10.0.0.0/23");
var peSubnet = vnet.AddSubnet("pe-subnet", "10.0.2.0/24");

builder.AddAzureContainerAppEnvironment("env")
    .WithDelegatedSubnet(acaSubnet);

var sql = builder.AddAzureSqlServer("sql");
var db = sql.AddDatabase("db");
peSubnet.AddPrivateEndpoint(sql);

builder.AddProject<Projects.Web>("web")
    .WithReference(db);

Deploy with aspire deploy — the deployment fails at the provision-webfrontend-roles-sql step.

Root Cause

AzureSqlServerResource.AddRoleAssignments() (line 194-287) creates an AzurePowerShellScript that needs TCP access to the SQL Server. When private endpoints disable public access, the script container (which runs outside the VNet) cannot connect.

Suggested Fix

When private endpoints are present, the AzurePowerShellScript should be configured to run inside the VNet using the SubnetResourceIds property. Azure deployment scripts support VNet integration: https://learn.microsoft.com/azure/azure-resource-manager/bicep/deployment-script-vnet

Environment

• Aspire branch: main (PR Add VNet deployment E2E tests for Storage Blob, Key Vault, and SQL Server #14417)
• Consistently reproduces across multiple CI runs (runs 21847458646 and 21848932910)

[eerhardt]

I did some investigation, and I think we are stuck right now.

Failed to provision server-roles-sql: Deployment failed: DeploymentScriptError:
Microsoft.Data.SqlClient.SqlException (0x80131904): Reason: An instance-specific error occurred while establishing a connection to SQL Server.
Connection was denied because Deny Public Network Access is set to Yes. For more information, see https://go.microsoft.com/fwlink/?linkid=2323206.

As far as I can tell, in order to make deployment scripts work to talk to a Sql Server that is not publicly accessible, you need to get your deployment script to run inside your vnet in a subnet.

Once I did that locally, the next error I got was

✗ Failed to provision server-roles-sql: Deployment failed: DeploymentPreflightValidationFailed: Container
Settings is provided with Subnets, but missing an Exisiting Storage Account Name. Please refer to https://aka.ms/DeploymentScriptsTroubleshoot for
more deployment script information. (3.7s)

which means not only do we need a subnet inside the vnet, but we now also need a Storage account to run the deployment script.

At this point, I think for 13.2 we need to rethink how we are doing private endpoints for Azure SQL Server. Maybe we don't create private endpoints, but instead we add firewall rules so only the vnet can access the Azure SQL Server - until we figure out how to add an Admin user without a deployment script.

[eerhardt]

Fixed by #14664