iis - 使用 (LocalDb) 连接字符串在本地主机上托管 Web API 无法连接

Question

我已在本地 IIS (Windows 10) 上设置了 Web API 应用程序。然后我也进入了我的主机文件并将其设置为:

api.domain.com         127.0.0.1

当我尝试使用 Postman 进行 POST 时:

http://api.api.domain.com/api/account/register

然后我收到以下错误:

{
    "Message": "An error has occurred.",
    "ExceptionMessage": "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: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.\r\n)",
    "ExceptionType": "System.Data.SqlClient.SqlException",
    "StackTrace": "   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\r\n   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)\r\n   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)\r\n   at System.Data.SqlClient.SqlConnection.Open()\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)\r\n   at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32()\r\n   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()\r\n   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)\r\n   at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript)\r\n   at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)\r\n   at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)\r\n   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)\r\n   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)\r\n   at System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext)\r\n   at System.Data.Entity.Database.Create(DatabaseExistenceState existenceState)\r\n   at System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)\r\n   at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)\r\n   at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()\r\n   at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)\r\n   at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)\r\n   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)\r\n   at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()\r\n   at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()\r\n   at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()\r\n   at System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, Expression`1 predicate, CancellationToken cancellationToken)\r\n   at System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, Expression`1 predicate)\r\n   at Microsoft.AspNet.Identity.EntityFramework.UserStore`6.<GetUserAggregateAsync>d__6c.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.AspNet.Identity.TaskExtensions.CultureAwaiter`1.GetResult()\r\n   at Microsoft.AspNet.Identity.UserValidator`2.<ValidateUserName>d__4.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.AspNet.Identity.UserValidator`2.<ValidateAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.AspNet.Identity.UserManager`2.<CreateAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.AspNet.Identity.UserManager`2.<CreateAsync>d__d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Asano.Websites.Api.Controllers.AccountController.<Register>d__20.MoveNext() in C:\\Users\\Lars Holdgaard\\Documents\\Git\\Eesy\\Eesy\\Eesy.Websites.Api\\Controllers\\AccountController.cs:line 339\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.AuthenticationFilterResult.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"
}

但是，如果我在 VisualStudio 中运行该应用程序，它就可以正常工作。

这是什么原因，通常如何解决？ :-)

编辑:

我的连接字符串是:

<add name="AsanoWebsitesApiContext" connectionString="Server=(LocalDb)\MSSQLLocalDB;Database=Eesy;Trusted_Connection=True;" providerName="System.Data.SqlClient" />

这又可以在 Visual Studio 中运行，但不能在本地主机上运行。

Answer 1

症状:

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.

网络上有很多关于此问题的帖子，可能令人不知所措，但根据我的经验，此问题可能是由 3 种不同的设置引起的。

• 您的 MS-SQL 数据库不允许远程连接。
• 您的 MSSQLSERVER 协议(protocol)设置中不允许使用 TCP/IP。
• 数据库的 TCP/UDP 端口被阻止。

<小时/>

让我们按照以下步骤一一解决所有这些问题:

1. 打开 MS-SQL Management Studio，连接到数据库服务器后，右键单击服务器(不是数据库)，然后选择属性。
2. 点击连接部分，并确保选中到此服务器的允许远程连接。如果不是，请检查它，并检查这是否可以解决问题。
3. 如果未选中或问题仍然存在，请关闭 MS-SQL Management Studio，然后打开 SQL Server 配置管理器。
4. 展开节点SQL Server 网络配置并选择MSSQL-Server 的协议(protocol)。
5. 确保 TCP/IP 已启用，然后重试。
6. 如果问题仍然存在，请右键单击“TCP/IP”并选择属性。
7. 查看使用的端口号(默认为 1433)。
8. 打开控制面板并导航至Windows 防火墙。
9. 点击左侧的高级设置，您应该会看到具有高级安全性的 Windows 防火墙。选择左侧的入站规则，然后单击新建规则...
10. 您可以创建规则以允许端口 1433(或 TCP/IP 步骤 (7) 中的端口号)上的入站流量。选择端口> TCP> 并输入步骤 (7) 中的端口号 > 选择允许连接> 选择域> 为规则命名，即可完成。
11. 重复第 10 步，这次是针对 UDP。