时间戳

首页 > 解决方案 > 找不到或无法从 IIS 访问 SQL Server

c# - 找不到或无法从 IIS 访问 SQL Server

问题描述

我已经部署了我的.NET 5.0应用程序IIS，这是一个基本的API并且有context and controllers. 上下文有一个定义的配置，它有连接字符串：

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=localhost;Database=flutter;");
        optionsBuilder.EnableSensitiveDataLogging();
    }

当我在本地托管我的应用程序并且请求运行良好时一切正常，但是一旦我尝试API在我的服务器上调用它，它就会失败。

调用默认API调用返回200 OK ，但只要我调用从数据库获取数据的端点，我就会得到在此处输入图像描述 500 在此处输入图像描述

正如我Event Viewer在通话后检查过的那样，我发现了一个错误：

Category: Microsoft.AspNetCore.Server.IIS.Core.IISHttpServer EventId: 2 SpanId: 73e6b4810067764d TraceId: 2c38235f0fa745438ce6ecdcf1874c44 ParentId: 0000000000000000 RequestId: 80000072-0001-fe00-b63f-84710c7967bb RequestPath: /FlutterAPI/api/Users/5 Connection ID "18302628891539275888", Request ID "80000072-0001-fe00-b63f-84710c7967bb": An unhandled exception was thrown by the application. Exception: 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: SNI_PN11, error: 25 - Connection string is not valid) ---> System.ComponentModel.Win32Exception (87): The parameter is incorrect. 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.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable``1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, 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.Query.Internal.SingleQueryingEnumerable``1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable``1 asyncEnumerable, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable``1 asyncEnumerable, CancellationToken cancellationToken) at FlutterApi.Controllers.UsersController.GetUser(Int64 id) in G:\Projects\flutter_api\ApiFlutter\Controllers\UsersController.cs:line 39 at lambda_method6(Closure , Object ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask``1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Builder.Extensions.UsePathBaseMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT``1.ProcessRequestAsync() ClientConnectionId:00000000-0000-0000-0000-000000000000 Error Number:87,State:0,Class:20

我的 SQL Server 已启动并正在运行

在此处输入图像描述

我已将所需用户（在我看来）添加到数据库的登录名中 在此处输入图像描述

我的 IIS 设置如下所示： 在此处输入图像描述

我的应用程序池设置如下所示 在此处输入图像描述

在过去的几天里，我一直在网上搜索并尝试不同的方法来解决这个问题，但我就是做不到。我在 IIS 上尝试了不同的设置、不同的方法、不同的服务器，但无法使其正常工作。急于求救。提前致谢。

编辑 1： 将 SQL Server 更新到最新的 CU 版本

标签： c#sql-serverasp.net-mvciis.net-core

解决方案

你有连接字符串的问题。您可以使用无需密码的集成安全性

optionsBuilder.UseSqlServer(@"Data Source=localhost;Initial catalog=flutter;Persist Security Info=True;Integrated Security=SSPI;");

或将带有密码的用户帐户添加到 SQL Server 安全性并使用它

@"Data Source=localhost;Initial catalog=flutter;Integrated Security=False;User ID=..;Password=...;"

这个变体更加灵活，因为您可以定义一组所有需要的权限。

由于您使用的是网络核心，因此您必须为您的 web api 添加一个特殊的池。例如称它为 FlatterApi。在高级设置中将“Net Clr Version”更改为“No Managed Code”，将“Identity”更改为“App Ppool Idenity”选择此池作为您的 Web 应用程序的应用程序池。

使用 sql management studio 添加到 Flatter db 安全新帐户（无需登录）并键入 IIS APPPOOL\FlatterApi 作为帐户名并选择此数据库的所有权限

推荐阅读