ASP.NET Core Web 3.1-IIS Express可以从localdb获取数据，但是本地IIS无法 [英] ASP.NET Core Web 3.1 - IIS Express can fetch data from localdb but local IIS can not

问题描述

如上所述，我有一个很奇怪的问题.

I have a very weird problem as stated above.

运行的代码完全相同，唯一的区别是所使用的调试配置文件.我正在使用Microsoft Visual Studio Community 2019版本16.6.3.

The code running is exactly the same, the only difference is the Debug profile used. I'm using Microsoft Visual Studio Community 2019 Version 16.6.3.

使用IIS配置文件时，代码显示我的数据库中没有媒体.

When using the IIS profile the code says I have no Media in my database.

将配置文件切换到IIS Express，现在我的媒体使用了与在即时Windows中可以看到的相同的连接字符串.

Switching profile to IIS Express, now I have Media using the exakt same connection string as can be seen in Immediate Windows.

我首先怀疑ASP.NET Core Web 3.1在从appsettings.json获取连接字符串时遇到问题，并且在web.config中也需要它.尝试添加它，但没有任何区别.选中context.Database.GetDbConnection().ConnectionString也会在两个配置文件中显示相同的内容，所以不是这种情况.

I first suspected that the ASP.NET Core Web 3.1 had a problem fetching the connection string from appsettings.json and that it was needed in web.config as well. Tried adding it but it did not make a difference. Checking context.Database.GetDbConnection().ConnectionString also shows the same for both profiles so this is not the case.

<connectionStrings>
  <add name="DefaultConnection" connectionString="Server=(localdb)\\mssqllocaldb;Database=<MYDB>;Trusted_Connection=True;MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />
</connectionStrings>

如果我通过SSMS查看数据库，则可以看到预期的媒体.

If I look at the database via SSMS I can see my Media as expected.

IIS应用程序池以LocalSystem的身份运行.如果我切换到像ApplicationPoolIdentity这样无权访问数据库的用户，则会收到Windows Event Viewer中预期的异常，并且应用程序无法正确启动.

The IIS Application Pool runs as LocalSystem. If I switch to a user that does not have access to the database like ApplicationPoolIdentity I get an exception as expected in Windows Event Viewer and the application does not start correctly.

Microsoft.Data.SqlClient.SqlException(0x80131904):与网络相关 或建立与以下对象的连接时发生特定于实例的错误 SQL Server.服务器未找到或无法访问.核实 实例名称正确并且已将SQL Server配置为 允许远程连接. (提供者:SQL网络接口，错误:50

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: SQL Network Interfaces, error: 50

• 发生本地数据库运行时错误.无法创建自动实例.有关错误详细信息，请参见Windows应用程序事件日志.

使用LocalSystem在事件查看器中一切正常，但它表示我没有媒体或任何其他数据.

Using LocalSystem everything looks fine in Event Viewer but it says I have no Media or any other data.

使用相同代码的两个相同的连接字符串如何显示不同的结果?

How can two identical connection strings using the same code show different results?

推荐答案

尝试将应用程序池标识切换到我知道可以通过SSMS访问的当前登录用户:

Tried switching Application Pool Identity to my currently logged in user that I know has access via SSMS:

与网络相关或特定于实例的错误发生在 建立与SQL Server的连接.找不到服务器或 无法访问.验证实例名称正确，并且 SQL Server配置为允许远程连接. (提供者:SQL 网络接口，错误:50-发生本地数据库运行时错误. LocalDB实例启动期间发生错误:SQL Server进程 未能启动. )

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. Error occurred during LocalDB instance startup: SQL Server process failed to start. )

有了这个和@nilsK的评论，我像这样修复了它:

With this and the comment from @nilsK I fixed it like this:

首先使用Visual Studio的Developer PowerShell或Developer Command Prompt作为管理员，并确认您只有LocalDb的一个实例.

Start by using Developer PowerShell or Developer Command Prompt for Visual Studio as Administrator and confirm that you only have one instance of LocalDb.

键入sqllocaldb info以查看您的LocalDb实例.

Type sqllocaldb info to see your LocalDb instances.

然后，我遵循了这篇文章，并且仍将应用程序池标识设置为我当前登录的用户，信誉: https://stackoverflow. com/a/38294458/3850405

I then followed this article and had Application Pool Identity still set to my currently logged in user, credit: https://stackoverflow.com/a/38294458/3850405

我发现在其中将应用程序池的Load User Profile设置为true是不够的，您还需要在通常位于C:\Windows\System32\inetsrv\config的applicationHost.config中将setProfileEnvironment设置为true.通过这种配置，它可以正常工作:

In there I found that it is not enough to have Load User Profile set to true for your Application Pool, you also need to set setProfileEnvironment to true in applicationHost.config normally located at C:\Windows\System32\inetsrv\config. With this configuration it worked:

这篇关于ASP.NET Core Web 3.1-IIS Express可以从localdb获取数据，但是本地IIS无法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！