c#asp.net-core iis localdb windows-server-2016

Cannot open database aspnet requested by the login. The login failed. Login failed for user IIS APPPOOL

I'm getting this error trying to access the local SQL DB for entity framework. Would be grateful for any tip that could help.

Production server: ASPNET Core 7 web application on IIS 10, Windows server 2016. Physical machine.

Developing on VS 2022. Running also SQL express on the same server, accessing from the same application.

The Windows AppPool user can access SQL express, but can't access the database in (localdb)\mssqllocaldb (the error message says "can't open database", i.e. the login works).

I've run this on my development machine, created the EF database and everything works. Deployed to the server, and the IIS worker can't seem to access the EF database.

I can log into both with the SQL management studio 18, both on the production server and the development machine. The login/user and permissions seem appropriate.

Connection string:

Server=(localdb)\\mssqllocaldb;Database=aspnet;Trusted_Connection=True;MultipleActiveResultSets=true;

In program.cs I have this code line, a few lines before app.run():

if (await userManager.FindByEmailAsync(sUserEmail) == null)

which triggers an EF database query. This line is where the exception in the subject is thrown (full exception text below).

Steps I've taken so far (that didn't make a difference)

Renamed the database to something simple with only small and capital letters
Changed the database username to something simple (instead of IIS APPPOOL...)
Confirmed permissions in the database, for the login and for the user
In IIS, modified the application host configuration setting to Windows Authentication enabled (even though for SQL express it's already working)
Added permissions of IIS APPPOOL\ user to the database file and folder
Dropped the EF database and recreated it with Update-Database
Repeated all the operations with Administrator run SQL management studio and Visual Studio
Enabled the EnableRetryOnFailure (just for the heck of it)

I've been reading up on custom storage for Identity. Basically my next step is to try and recreate the database in SQL express, and modify the connection string to point there and try that. I would've actually preferred just one SQL instance for everything so if that works might be even better for me.

But I would love to know and remove the obstacle that's preventing the database connection. For clarification, I did not write the full names of the database and the App Pool user here.

Please let me know if there's any other info I can provide that could help with this.

Exception:

System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "aspnet" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\'.
   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.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   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(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
ClientConnectionId:1a0e38a7-1bee-4d50-b1f4-2a10fdd3e7aa
Error Number:4060,State:1,Class:11
   --- End of inner exception stack trace ---
   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()

Solution

After almost 2 weeks of being stuck on this, finally found the following read on the SQL local DB:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-2016#permissions

By default, access to the instance of LocalDB is limited to its owner. The data contained in the LocalDB is protected by file system access to the database files. If user database files are stored in a shared location, the database can be opened by anyone with file system access to that location, by using an instance of LocalDB that they own. If the database files are in a protected location, such as the users data folder, only that user, and any administrators with access to that folder, can open the database.

And-

To connect to a shared instance of LocalDB, add .\ (backslash + dot + backslash) to the connection string to reference the namespace reserved for shared instances. For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb).\AppData as part of the connection string. A user connecting to a shared instance of LocalDB that they don't own must have a Windows Authentication or SQL Server Authentication login.

So as it turns out, when the IIS APPPOOL worker ran the web application, it was loading its own copy of mssqllocaldb which didn't have the EF Identity database and tables.

To work with mssqllocaldb, I did the following:

Obtained info on the mssqllocaldb instance where the EF database is installed, by running via command prompt: sqllocaldb i mssqllocaldb
Shared the instance of my user via command prompt: sqllocaldb h "MSSQLLOCALDB" "new shared name"
Connected to the shared database in Visual Studio
Copied the connection string from the properties of that connection to appsettings.json
Ran again Update-Database in the NuGet Package Manager Console
Gave the IIS worker file-system access to the folder and contents where the actual files that make up mssqllocaldb reside.
Confirmed permissions in the database for the IIS worker login and user via SQL management studio

And then everything just worked normally, all the Identity functionality works!