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

问题描述

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

运行的代码完全一样，唯一不同的是使用的Debug配置文件.我使用的是 Microsoft Visual Studio Community 2019 版本 16.6.3.

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

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

我首先怀疑 ASP.NET Core Web 3.1 在从 appsettings.json 获取连接字符串时出现问题，并且在 web.config 中也需要它.尝试添加它，但没有任何区别.检查 context.Database.GetDbConnection().ConnectionString 也显示两个配置文件相同，因此情况并非如此.

<添加名称=默认连接"connectionString=Server=(localdb)\mssqllocaldb;Database=<MYDB>;Trusted_Connection=True;MultipleActiveResultSets=true";providerName=System.Data.SqlClient"/></connectionStrings>

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

IIS 应用程序池作为 LocalSystem 运行.如果我切换到无权访问数据库的用户，例如 ApplicationPoolIdentity，我会在 Windows 事件查看器中收到预期的异常，并且应用程序无法正确启动.

<块引用>

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

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

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

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

解决方案

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

<块引用>

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

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

首先以管理员身份使用 Developer PowerShell 或 Developer Command Prompt for Visual Studio，并确认您只有一个 LocalDb 实例.

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

然后我关注了这篇文章，并将应用程序池标识设置为我当前登录的用户，信用:

I have a very weird problem as stated above.

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.

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

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

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>

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

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): 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.

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?

解决方案

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

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. )

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

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

Type sqllocaldb info to see your LocalDb instances.

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

https://docs.microsoft.com/en-us/archive/blogs/sqlexpress/using-localdb-with-full-iis-part-1-user-profile

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:WindowsSystem32inetsrvconfig. With this configuration it worked:

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