Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – 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)

  1. Renamed the database to something simple with only small and capital letters
  2. Changed the database username to something simple (instead of IIS APPPOOL…)
  3. Confirmed permissions in the database, for the login and for the user
  4. In IIS, modified the application host configuration setting to Windows Authentication enabled (even though for SQL express it’s already working)
  5. Added permissions of IIS APPPOOL user to the database file and folder
  6. Dropped the EF database and recreated it with Update-Database
  7. Repeated all the operations with Administrator run SQL management studio and Visual Studio
  8. 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()
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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:

    1. Obtained info on the mssqllocaldb instance where the EF database is installed, by running via command prompt: sqllocaldb i mssqllocaldb
    2. Shared the instance of my user via command prompt: sqllocaldb h "MSSQLLOCALDB" "new shared name"
    3. Connected to the shared database in Visual Studio
    4. Copied the connection string from the properties of that connection to appsettings.json
    5. Ran again Update-Database in the NuGet Package Manager Console
    6. Gave the IIS worker file-system access to the folder and contents where the actual files that make up mssqllocaldb reside.
    7. 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!


  2. 0

    A couple of things to look at:

    1. Replicate the production configuration on your local machine. Set up IIS to mirror your production server and access your localhost app with IIS…this should make troubleshooting much easier.

    2. Remove the untrusted connection string and use a fully defined user / password instead: <add name={name} connectionString="Data Source={server_name};Initial Catalog={database_name};MultipleActiveResultSets=True;User Id={user};Password={password}" providerName="System.Data.SqlClient" />. If you do decide to do this, make sure you have a login mapped to the user in SQL Server: {server_name}/Security/Logins. For a production server, this is the better way to go (I feel), as you are giving specific privileges to the app user.

    3. Check the permissions in SQL Server. The permissions for the user connecting via the IIS App pool should look something like this:

    Membership settings for IIS APPPOOL user

    Let me know if any of the above helps, we can then proceed from there.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search