如何解决IIS应用池账户无法登录SQL Server数据库的连接错误？

“/”应用程序中的服务器错误。
无法打开登录请求的数据库"projectAdelet2"，登录失败。
用户"IIS APPPOOL\DefaultAppPool"登录失败。

说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。

异常详细信息: System.Data.SqlClient.SqlException: 无法打开登录请求的数据库"projectAdelet2"，登录失败。
用户"IIS APPPOOL\DefaultAppPool"登录失败。

源错误:
执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。

堆栈跟踪:
[SqlException (0x80131904): 无法打开登录请求的数据库"projectAdelet2"，登录失败。
用户"IIS APPPOOL\DefaultAppPool"登录失败。]
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) +1524
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +467
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +70
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +940
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +111
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1567
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +118 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +268
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +315 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) +128
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) +265 System.Data.SqlClient.SqlConnection.Open() +133 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) +104 System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) +503 System.Data.Entity.SqlServer.<>c__DisplayClass1.<Execute>b__0() +18 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func1 operation) +234
System.Data.Entity.Core.EntityClient.EntityConnection.Open() +321

[EntityException: 基础提供程序在 Open 上失败。]
System.Data.Entity.Core.EntityClient.EntityConnection.Open() +741
System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions) +167
System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +63 System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__5() +203 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func1 operation) +234
System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) +357
System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() +30 System.Data.Entity.Internal.LazyEnumerator1.MoveNext() +39
System.Linq.Enumerable.LastOrDefault(IEnumerable1 source) +239 Proview.Controllers.GirisController.Giris(GirisViewModel model) +112 lambda_method(Closure , ControllerBase , Object[] ) +139 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +280
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +35
System.Web.Mvc.Async.<>c__DisplayClass42.b__41() +33
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +42
System.Web.Mvc.Async.<>c__DisplayClass39.b__33() +80
System.Web.Mvc.Async.<>c__DisplayClass4f.b__49() +396
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +42
System.Web.Mvc.Async.<>c__DisplayClass2a.b__20() +32
System.Web.Mvc.Async.<>c__DisplayClass25.b__22(IAsyncResult asyncResult) +187
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +38
System.Web.Mvc.<>c__DisplayClass1d.b__18(IAsyncResult asyncResult) +30
System.Web.Mvc.Async.<>c__DisplayClass4.b__3(IAsyncResult ar) +25
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +52
System.Web.Mvc.Async.<>c__DisplayClass4.b__3(IAsyncResult ar) +25
System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +38
System.Web.Mvc.<>c__DisplayClass8.b__3(IAsyncResult asyncResult) +44
System.Web.Mvc.Async.<>c__DisplayClass4.b__3(IAsyncResult ar) +25
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +38
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +431
System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +75
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +158

1. 确认数据库存在且名称正确 #

• 打开SQL Server Management Studio (SSMS)，检查是否存在名为projectAdelet2的数据库，确保名称拼写完全一致（SQL Server在区分大小写的实例中对名称大小写敏感）。

2. 为IIS应用池账户添加SQL Server登录权限 #

• 在SSMS中，展开安全性 > 登录名，右键选择新建登录名。
• 在常规选项卡，选择Windows身份验证，输入登录名IIS APPPOOL\DefaultAppPool，点击确定创建登录。
• 选中刚创建的登录名，右键选择属性，切换到用户映射选项卡：
  • 勾选projectAdelet2数据库，在下方数据库角色成员身份中，根据业务需求勾选权限（至少勾选db_datareader和db_datawriter，若需要架构修改权限可勾选db_owner）。
  • 点击确定保存设置。

3. 验证连接字符串配置 #

• 检查项目Web.config文件中的连接字符串，确保数据库名称、服务器名称、身份验证方式正确：
  • 使用Windows身份验证时，连接字符串需包含Integrated Security=True，示例：

    <connectionStrings>
      <add name="YourDbContext" 
           connectionString="Server=你的SQL服务器名;Database=projectAdelet2;Integrated Security=True;" 
           providerName="System.Data.SqlClient" />
    </connectionStrings>
    

  • 确认服务器名称正确，本地实例可使用localhost、.\SQLEXPRESS（默认Express实例）或具体实例名。

4. 检查IIS应用池身份设置 #

• 打开IIS管理器，找到DefaultAppPool，右键选择高级设置。
• 在进程模型下的标识选项，确认当前使用ApplicationPoolIdentity（即IIS APPPOOL\DefaultAppPool）。若需切换到其他账户，确保该账户已拥有SQL Server对应数据库的访问权限。

5. 测试连接权限 #

• 编写简单代码验证连接：

  using (var connection = new SqlConnection("你的连接字符串"))
  {
      try
      {
          connection.Open();
          Console.WriteLine("连接成功");
      }
      catch (Exception ex)
      {
          Console.WriteLine($"连接失败: {ex.Message}");
      }
  }
  

• 或在SSMS中执行EXECUTE AS LOGIN = 'IIS APPPOOL\DefaultAppPool'切换到该账户，尝试访问projectAdelet2数据库，排查权限问题。

6. 检查SQL Server服务及防火墙 #

• 打开服务（services.msc），确认SQL Server和SQL Server Browser服务处于运行状态。
• 若连接远程SQL Server，检查目标服务器防火墙是否开放了SQL Server默认端口1433。

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