ASP.NET连接远程SQL Server 2022认证失败问题求助

环境信息 #

• 原环境：单节点Windows Server 2008 R2，部署IIS、ASP.NET Web应用及本地SQL Server 2014数据库
• 新环境：分离部署
  • 数据库服务器：Windows Server 2022 + SQL Server 2022 Web版
  • Web服务器：Windows Server 2022 + IIS + Web应用

问题现象 #

环境搭建完成后，Web应用可正常通过浏览器访问，但登录认证时出现错误：

Login failed for ''

连接字符串配置 #

原web.config连接字符串 #

<add name="ConnectionString" 
     connectionString="data source=localhost;initial catalog=AP;Integrated Security=SSPI" />

修改后的连接字符串 #

已将远程数据库配置为SQL Server认证，创建了拥有db owner权限的user1账号，且可通过SSMS从Web服务器以user1身份访问远程数据库，因此修改连接字符串为：

<add name="ConnectionString" 
     connectionString="data source=10.1.0.5,1433;initial catalog=AP;Integrated Security=False;User ID=user1;Password=pass1" />

错误日志 #

SQL Server管理端错误信息 #

Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 10.1.0.4]

Error: 18456, Severity: 14, State: 58.

ASP.NET Web应用错误信息 #

[SqlException (0x80131904): Login failed for user ''.]
   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) +1481
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1150
   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) +1124
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +103
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1598
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +159
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +382
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +307
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +198
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +422
   System.Data.SqlClient.SqlConnection.Open() +199
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +193
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +521
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +153
   Data.DAL.UserProfileDSTableAdapters.UserProfileTableAdapter.GetUserByUserName(String UserName) in C:\Users\boydj\source\repos\DAL\UserProfileDS.Designer.cs:2801
   
Data.UserBLL.GetUserByUserName(String userName) in C:\Users\boydj\source\repos\BLL\UserBLL.cs:231
   Classroom.SignIn.IsActiveUser(String userName, LoginFailureReason& loginFailureReason) in C:\Users\boydj\source\repos\Classroom\Authentication\SignIn.aspx.cs:517
   Classroom.SignIn.ValidateUser(String userName, String password) in C:\Users\boydj\source\repos\Classroom\Authentication\SignIn.aspx.cs:398
   Classroom.SignIn.signInButton_Click(Object sender, EventArgs e) in C:\Users\boydj\source\repos\Classroom\Authentication\SignIn.aspx.cs:347
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +138
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5445 

解决方案 #

问题根源为：连接字符串存在拼写错误。

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