Asp Net Core MVC: Login failed for user ''IIS APPPOOL \ Readdy''.

 

The following error message comes when you want to access an SQL Server in an Asp Net Core 2 MVC application.

-> An error occurred using the connection to database

 

Status: solved

 

Error, error message in the stdOut logfile

An error occurred using the connection to database ''readdy'' on server ''.\SQLEXPRESS''. System.Data.SqlClient.SqlException (0x80131904): Cannot open database "readdy" requested by the login. The login failed. Login failed for user ''IIS APPPOOL\Readdy''.

 

 

Problem:

The IIS Internet user and the IIS AspNetCore application do not have access to the local SQL server as the SQLExpress server.

That makes sense, because otherwise any unauthorized person could access the database.

So you have to give the current Internet user the rights to access the SQL Server.

 

Solution:

You create a new user,

Allocates this new user as Identiy in the ApplicationPool

And in addition, the database is assigned as a mapping for this user in the SQL Securtiy area.

 

 

 

 

Step 1: Create a Windows user

Under Computer Management-> System Tools-> Local Users and Groups

If you create a new user among the users and give this a fixed password, which does not go out

 

 

Step 2: Create application pool

Under the IIS Server -> Applications Pools you create a new Application Pool with Add Application Pools

This is what you can do with NET CLR Version: unmanaged code, because this is not part of IIS in AspNetCore applications. is managed

 

Applicationpool -> Identity

And in the new application pool in Advanced Settings, it enters the identity on the new Windows user

 

Applicationpool -> Identity-> Enter Custom User

Under Custom User then select the new Windows user and confirms it with the default password

Set application pool

Then you put in the AspNet Core application under Basic Settings

Then open Application Pool with Select and select the new application pool AspNetCore_Sql_User

Step 3:

Create new SQL Server user

In SQL Server you create under SQL Server-> Security -> Logins

A new user.

With the new user, you then enter the newly created Windows user as Readdy-Server \ AspNetCore_SQL_User

 

Step 4: Database-> User Mapping

And then enters the user in the database under Mapping

To do this, select under SQL Server-> Security-> Logins the new SQL user login.

In the dialog: Login Properties, enter the table as active in User Mapping ->.

In the lower part you enter the roles Database role memberschip db_datareader, db_datawriter and others.

 

 

 

Restart the AspNetCore applications

If one then addresses the Asp Core2 application in the browser with the URL of the website, the new SQL data should appear from the database

 

 

 

 

 

 

 

 

 

 

 

Log details Error log

.. Application started. Press Ctrl+C to shut down. fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]       An error occurred using the connection to database ''readdy'' on server ''.\SQLEXPRESS''. System.Data.SqlClient.SqlException (0x80131904): Cannot open database "readdy" requested by the login. The login failed. Login failed for user ''IIS APPPOOL\Readdy''.    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling).. C:\_Daten\Desktop\VS_Projects\Web\Readdy\Readdy\Readdy\Controllers\NotesController.cs:line 42 ..