Azure中Web App通过托管标识连接SQL的Bicep配置问题排查

我是Azure新手，正在为项目搭建Bicep部署栈，但Web App始终无法连接SQL数据库，不确定Bicep配置遗漏了什么。

Bicep配置代码 #

param location string = resourceGroup().location

var environment = 'myveryownproject'
var sqlDbContributorRole = subscriptionResourceId('Microsoft.Authorization/roleDefinitions', '9b7fa17d-e63e-47b0-bb0a-15c516ac86ec')

func name(abbreviation string, environment string) string => 
  '${abbreviation}-${environment}'

func uname(abbreviation string, environment string, unique string) string => 
  '${abbreviation}-${environment}-${unique}'

resource applicationsSubnet 'Microsoft.Network/virtualNetworks/subnets@2024-01-01' = {
  name: name('snet', environment)
  parent: network
  properties: {
    serviceEndpoints: [
      {
        service: 'Microsoft.Sql'
      }
    ]
    addressPrefix: '10.0.0.0/24'
    delegations: [
      {
        name: name('snetd', environment)
        properties: {
          serviceName: webAppPlan.type
        }
      }
    ]
  }
}

resource network 'Microsoft.Network/virtualNetworks@2024-01-01' = {
  name: name('vnet', environment)
  location: location
  properties: {
    addressSpace: {
      addressPrefixes: [
        '10.0.0.0/16'
      ]
    }
  }
}

resource webAppPlan 'Microsoft.Web/serverfarms@2023-12-01' = {
  name: name('asp', environment)
  location: location
  kind: 'linux'
  properties: {
    reserved: true
  }
  sku: {
    name: 'B1'
  }
}

resource webApp 'Microsoft.Web/sites@2023-12-01' = {
  name: name('app', environment)
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    serverFarmId: webAppPlan.id
    siteConfig: {
      linuxFxVersion: 'DOTNETCORE|8.0'
    }
    httpsOnly: true
    virtualNetworkSubnetId: applicationsSubnet.id
  }
}

resource databaseServer 'Microsoft.Sql/servers@2021-11-01' = {
  name: name('sql', environment)
  location: location
  properties: {
    minimalTlsVersion: '1.2'
    administrators: {
      administratorType: 'ActiveDirectory'
      sid: '<<<MY SID>>>'
      login: '<<<MY LOGIN>>>'
      azureADOnlyAuthentication: true
      principalType: 'User'
    }
  }
  identity: {
    type: 'SystemAssigned'
  }
}

resource subnetRuleForDatabaseServer 'Microsoft.Sql/servers/virtualNetworkRules@2023-08-01-preview' = {
  name: name('sqlnet', environment)
  parent: databaseServer
  properties: {
    virtualNetworkSubnetId: applicationsSubnet.id
  }
}

resource database 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  name: name('sqldb', environment)
  location: location
  properties: {
    zoneRedundant: false
  }
  sku: {
    name: 'GP_S_Gen5'
    tier: 'GeneralPurpose'
    family: 'Gen5'
    capacity: 1
  }
  parent: databaseServer
}

resource webAppDatabaseAccessRole 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(name('role', environment))
  properties: {
    principalId: webApp.identity.principalId
    roleDefinitionId: sqlDbContributorRole
  }
}

ASP.NET Core应用代码 #

var builder = WebApplication.CreateBuilder(args);
// ...
builder.Services.AddDbContext<DataContext>((sp, options) =>
{
    var configuration = sp.GetRequiredService<IConfiguration>();
    var connectionString = configuration.GetConnectionString("MyDbConnectionString");
    options.UseSqlServer(connectionString);
});
// ...
var app = builder.Build();
// ...
var conf = sp.GetRequiredService<IConfiguration>();
var connection = conf.GetConnectionString("MyDbConnectionString");
app.Logger.LogInformation($"The connection is: {connection}");
sp.GetRequiredService<DataContext>().Database.EnsureCreated();

错误信息 #

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''

本地将IP加入SQL防火墙后，运行相同代码可正常连接，但Azure上的Web App始终报错。

问题分析与修复方案 #

你的Bicep配置和应用连接逻辑存在几个关键遗漏点，导致AD身份验证失败：

1. 连接字符串未配置Azure AD托管身份参数 #

当前的连接字符串缺少强制使用Azure AD托管身份的参数，需要添加：

• Authentication=Active Directory Managed Identity：指定使用托管身份验证
• 若Web App有多个身份，可追加User ID=<Web App的客户端ID>

正确的连接字符串示例：

Server=tcp:<sql-server-name>.database.windows.net,1433;Database=<db-name>;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

2. SQL虚拟网络规则需启用ignoreMissingVnetServiceEndpoint #

子网已配置Microsoft.Sql服务端点，但部署时可能存在资源创建顺序问题（服务端点未完全生效就创建SQL规则），导致连接被拒绝。需在虚拟网络规则中添加该参数：

resource subnetRuleForDatabaseServer 'Microsoft.Sql/servers/virtualNetworkRules@2023-08-01-preview' = {
  name: name('sqlnet', environment)
  parent: databaseServer
  properties: {
    virtualNetworkSubnetId: applicationsSubnet.id
    ignoreMissingVnetServiceEndpoint: true // 添加此行
  }
}

3. 角色分配作用域不正确 #

当前角色分配默认作用域为订阅级别，需将SQL DB Contributor角色直接分配到SQL数据库或服务器级别：

resource webAppDatabaseAccessRole 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(name('role', environment))
  scope: database // 指定作用域为SQL数据库，或databaseServer
  properties: {
    principalId: webApp.identity.principalId
    roleDefinitionId: sqlDbContributorRole
    principalType: 'ServicePrincipal' // 显式指定主体类型为服务主体
  }
}

4. 验证Web App托管身份状态 #

确保Web App的系统分配身份已正确创建，可通过Bicep输出验证：

output webAppPrincipalId string = webApp.identity.principalId

5. 检查SQL服务器AD管理员租户一致性 #

确认Web App托管身份所在的Azure AD租户，与SQL服务器的AD管理员租户一致，且无额外AD组限制。

完成以上修改后，重新部署Bicep模板并更新Web App的连接字符串配置，即可解决登录失败问题。

内容的提问来源于stack exchange，提问作者GeorgeF0