部署在Azure App Service的.NET 6 Web API连接Azure SQL时访问被拒

环境信息 #

• 部署架构：.NET 6 Web API 部署在Azure App Service，搭配Azure SQL数据库（定价层：通用目的 - 无服务器：Gen5，2个vCore）
• 资源归属：同一订阅、同一资源组、同一区域（南非北部）

问题现象 #

• 本地调试：Visual Studio中可通过EF Core正常连接Azure SQL执行CRUD操作，SQL Server Management Studio也能直接连接并操作数据库
• 部署后异常：Web API发布到Azure App Service后，EF Core尝试写入数据库时抛出连接异常，但NLog通过存储过程写入同一数据库完全正常

异常日志 #

An error occurred using the connection to database 'eballcoz_mssql' on server '156.38.224.15'.

An exception occurred in the database while saving changes for context type 'eBall.Web.API.Data.EBallContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 ---> System.ComponentModel.Win32Exception (5): Access is denied.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:5,State:0,Class:20

相关代码配置 #

DbContext的OnConfiguring方法 #

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {             
        optionsBuilder.UseSqlServer("Server=tcp:xxxxx.database.windows.net,1433;Initial Catalog=xxxxx-sqlserver;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
    }
}

NLog数据库目标配置 #

<target name="db"
            xsi:type="Database"
            connectionString="Server=tcp:eball.database.windows.net,1433;Initial Catalog=xxxxx-sqlserver;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
            commandType="StoredProcedure"
            commandText="[InsertLog]"
        >
      <parameter name="machineName"    layout="${machinename}" />
      <parameter name="logged"         layout="${date}" />
      <parameter name="logLevel"       layout="${level}" />
      <parameter name="message"        layout="${message}" />
      <parameter name="logger"         layout="${logger}" />
      <parameter name="properties"     layout="${all-event-properties:separator=|}" />
      <parameter name="callsite"       layout="${callsite}" />
      <parameter name="exception"      layout="${exception:tostring}" />
      <parameter name="callsiteLineNumber"      layout="${callsite-linenumber}" />
      <parameter name="stackTrace"      layout="${stacktrace}" />
    </target>
  </targets>

已尝试的操作 #

• 勾选Azure SQL服务器网络设置中的「允许Azure服务和资源访问此服务器」
• 将App Service的IP地址添加到SQL防火墙规则
• 本地验证连接字符串有效性，排除字符串本身错误

解决思路 #

1. 确认EF Core实际使用的连接字符串 #

• 检查App Service「配置」-「连接字符串」中是否存在同名配置项，会覆盖代码中的硬编码值
• 在代码中添加日志输出实际使用的连接字符串（脱敏后），对比NLog的连接字符串是否完全一致
• 建议改用配置文件（appsettings.json）或App Service连接字符串配置，通过依赖注入获取，避免硬编码

2. 排查连接字符串参数与数据库名称差异 #

• 异常中显示的数据库为eballcoz_mssql，但连接字符串的Initial Catalog是xxxxx-sqlserver，确认是否为同一数据库
• 尝试在EF Core连接字符串中添加Network Library=DBMSSOCN，强制使用TCP/IP（Azure SQL不支持命名管道，异常中提到的Named Pipes Provider可能是问题根源）
• 临时测试将TrustServerCertificate=True加入连接字符串，排除证书验证问题（生产环境不推荐）

3. 针对Azure SQL无服务器模式的调整 #

• 无服务器模式下数据库会自动暂停，首次连接需要唤醒，延长连接超时时间至60秒
• 检查Azure SQL的活动日志，确认是否有暂停/唤醒记录或权限相关报错

4. 检查EF Core上下文的配置优先级 #

• 确认OnConfiguring是否被正确触发，是否存在Program.cs中依赖注入配置覆盖了该方法的设置
• 改用显式依赖注入配置DbContext，避免硬编码：

  builder.Services.AddDbContext<EBallContext>(options =>
      options.UseSqlServer(builder.Configuration.GetConnectionString("YourConnectionString")));
  

5. 网络与权限深层排查 #

• 检查App Service的出站网络规则，确认1433端口未被阻止
• 验证SQL账号是否具备EF Core所需的数据库权限（NLog仅调用存储过程，可能权限范围不同）

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