Can this work when your SQL server instance is on another host machine then your IIS host machine ? Because I need to fix the same issue, but SQL and IIS are not on the same machine. So using Windows Authentication for that new user wont work
For me the user to add was 'IIS APPPOOL\DefaultAppPool'. Then it worked.
In SSMS, under the server, expand Security, then right click Logins and select "New Login...".
In the New Login dialog, enter the app pool as the login name and click "OK".
Instead better to change 'Identity' to 'LocalSystem' from IIS, as described in next answer.
VERY IMPORTANT: DO NOT CLICK SEARCH TO TRY TO CONFIRM THE LOGIN! It won't recognize it but it will work. Just type it in as IIS APPPOOL\SimonsAppPoolName. See this stackoverflow.com/questions/1933134
You can then right click the login for the app pool, select Properties and select "User Mapping". Check the appropriate database, and the appropriate roles. I think you could just select db_datareader and db_datawriter, but I think you would still need to grant permissions to execute stored procedures if you do that through EF. You can check the details for the roles here.
You need to add a login to SQL Server for IIS APPPOOL\ASP.NET v4.0 and grant permissions to the database.
thanks, I did what you sad, now i receive this error: Cannot open database "SiteNameExtension" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\DefaultAppPool'.