c# - ASP.NET Core Web 3.1 - IIS Express can fetch data from localdb but local IIS can not

Question

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?

Answer 1

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: