491

I have a web project (C# Asp.Net, EF 4, MS SQL 2008 and IIS 7) and I need to migrate it to IIS 7 locally (at the moment works fine with CASSINI).

Locally in IIS I have my Default Web Site with my deploy. Both my deploy and Default Web Site are on pool ASP.NET v4.0 (look image for settings) the pool target Framework 4 as my web project. Pool Settings When visiting the site, the browser does not show the page and allow the browser to download the page instead.

I have other projects running on IIS locally and they work with no problems (but they do not use Entity Framework).

Using the Event Logger I see errors as below:

Exception information: 
    Exception type: EntityException 
    Exception message: The underlying provider failed on Open.
   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)


    Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)

Related question

UPDATE: You can read in the resources on this question that permissions must be granted on MS SQL 2008 manually as arift explain in his answer. Using IIS 7.5 and MS SQL 2008 R2, setting manually permission should not be necessary.

Community
  • 1
  • 1
GibboK
  • 71,848
  • 143
  • 435
  • 658
  • 2
    have you allowed the app pool identity permission on the website folder? – Christian Oct 08 '11 at 16:46
  • i'm not sure, could you please tell me how to do it? – GibboK Oct 08 '11 at 16:47
  • 1
    http://learn.iis.net/page.aspx/624/application-pool-identities/ – Christian Oct 08 '11 at 16:49
  • actually, as adrift says, this could be an sql security issue. You are best to set an NT User account for the AppPool and then grant that permission to the website folder and to the appropriate tables in SQL – Christian Oct 08 '11 at 16:53
  • thanks Christina, i follow your resource and all is the setting for IIS and the folder seems as described, still I have the same problem :-( – GibboK Oct 08 '11 at 17:02
  • 1
    @GibboK : I would encourage you to review the accepted answer here and to choose a more appropriate answer. The accepted answer is leading many people into a security black hole. Yes, it works. No, it really isn't a good idea. See my comments below. – spender Feb 13 '14 at 12:33
  • Thanks for your comments, I have to add that my question was referencing a problem on my local machine for development so security issue was not a really concern. – GibboK Feb 13 '14 at 17:24
  • Local IIS, not express. was able to fix by adding the reported account in the error reported to sql server. In my case it was: IIS APPPOOL\DefaultAppPool this is for local development environment -- vs 2017 – Lawrence Patrick Jun 14 '17 at 02:54
  • GAAAHH! I was looking at this for about 4 hours trying to figure it out, when I finally realised my connection string was pointing at localhost but the database was on localhost\SQLEXPRESS !! A "database not found" error message would be nice, Microsoft! – komodosp Dec 18 '17 at 12:35

38 Answers38

677

Looks like it's failing trying to open a connection to SQL Server.

You need to add a login to SQL Server for IIS APPPOOL\ASP.NET v4.0 and grant permissions to the database.

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".

enter image description here

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.

Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127
  • 9
    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'. – GibboK Oct 08 '11 at 17:11
  • 90
    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 http://stackoverflow.com/questions/1933134 – Simon_Weaver Feb 22 '14 at 20:56
  • 7
    Instead better to change 'Identity' to 'LocalSystem' from IIS, as described in next answer. – Altaf Patel Oct 27 '14 at 06:18
  • 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 – Segers-Ian Dec 16 '14 at 21:28
  • @IanS. in that situation we've used SQL Server Authentication. Our web site uses EF so we specify the user id and password in the EF connection string in the web.config. – Jeff Ogata Dec 16 '14 at 22:11
  • I had to add IIS APPPOOL\DefaultAppUser as a New Login in SSMS instead. – Jacob Young Feb 12 '16 at 16:29
  • @JacobYoung I had to do the same thing on my machine. Question for Jeff - is this secure? I was worried that by adding IIS APPPOOL\MyAppPoolName that it would open up access to anyone using that app pool...I don't want that, I just want my EF to work correctly. – cr1pto Apr 15 '16 at 16:54
  • 10
    For me the user to add was 'IIS APPPOOL\DefaultAppPool'. Then it worked. – Marcel May 03 '17 at 09:01
  • It works for me too. My pool was DefaultAppPool and I had to enter that to the login Name instead of what in the above picture. – Hedego Aug 05 '17 at 08:55
  • Thanks a lot @JeffOgata this worked for me using SSMS v18.0 Preview 6. – Tim Kruger Mar 05 '19 at 08:29
367

You can change the ApplicationPoolIdentity from IIS7 -> Application Pools -> Advanced Settings. AdvancedSettings

Under ApplicationPoolIdentity you will find local system. This will make your application run under NT AUTHORITY\SYSTEM, which is an existing login for the database by default.

Edit: Before applying this suggestion you should note and understand the security implications.

slugster
  • 49,403
  • 14
  • 95
  • 145
Thea
  • 7,879
  • 6
  • 28
  • 40
  • 56
    @GibboK, If you are concerned about security, don't do this. See http://technet.microsoft.com/en-us/library/dd378907(v=WS.10).aspx – Jeff Ogata Mar 30 '12 at 12:22
  • if you're using a virtual directory be sure to do this on the virtual directory and not the web server root – Simon_Weaver Jun 25 '13 at 22:38
  • 4
    In addition to running the app pool as the LocalSystem identity, I also had to map the "NT AUTHORITY\SYSTEM" user to database roles – Phil Jul 23 '13 at 17:36
  • 32
    This stinks. Granting SYSTEM authority to a web app is a recipe for disaster and allows miscreants all sorts of opportunity to inflict badness upon not only your web app, but the entire hosting server. Just because the DB accepts logins from SYSTEM does not mean that you should run your web app as SYSTEM. The windows desktop won't even let you run as SYSTEM (without jumping through hoops). Running a webapp with this authority is a really, *really* stupid idea. You should make the DB accept the current apppool identity. I'd -100 if I could. -1. – spender Jan 20 '14 at 01:17
  • 9
    SYSTEM is more highly privileged than Administrator. You should **NEVER** run your web server with anything approaching that level. – slugster Feb 13 '14 at 13:34
  • 1
    I just make the Guest account a member of the Administrator's group. Simple, clean, never have any issues with security bullshit. –  Feb 13 '14 at 19:25
39

I solved this problem using sql as following image.

Right click on db-> properties -> permission -> View Server permission -> and then select IIS APPPOOL\ASP.NET v4.0 and grant permission.

db

DevT
  • 4,843
  • 16
  • 59
  • 92
33

ensure you have...

Trusted_Connection=false;

in your connection String

JGilmartin
  • 8,683
  • 14
  • 66
  • 85
28

Run this sql script

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\DefaultAppPool')
BEGIN
    CREATE LOGIN [IIS APPPOOL\DefaultAppPool] 
      FROM WINDOWS WITH DEFAULT_DATABASE=[master], 
      DEFAULT_LANGUAGE=[us_english]
END
GO
CREATE USER [WebDatabaseUser] 
  FOR LOGIN [IIS APPPOOL\DefaultAppPool]
GO
EXEC sp_addrolemember 'db_owner', 'WebDatabaseUser'
GO
Rolwin Crasta
  • 4,219
  • 3
  • 35
  • 45
16

If in the connection string you have specified:

User ID=xxx;Password=yyy

but in the connection string there is:

Trusted_Connection=true;

SQL Server will use Windows Authentication, so your connection values will be ignored and overridden (IIS will use the Windows account specified in Identity user profile). more info here

The same applies if in the connection string there is:

 Integrated Security = true;

or

 Integrated Security = SSPI;

because Windows Authentication will be used to connect to the database server. more info here

Community
  • 1
  • 1
spiderman
  • 1,565
  • 2
  • 16
  • 37
13

go to iis -> application pools -> find your application pool used in application

enter image description here

select your application pool used for the application right click select advanced settings

enter image description here

Select application pool identity enter image description here

select built in as Local System and click ok

Lijo
  • 6,498
  • 5
  • 49
  • 60
9

I had to create a user `IIS APPPOOL\DefaultAppPool' as shown below in SQL Server. Security > Login > Right click and press 'New Login'. You only enter the username as show in red in the screen.

enter image description here

Then go into that new user properties, check the database this user will access (marked in blue below) and select db_owner as well. I had to select because although connection was working but subsequent SELECT queries didn't had the access if this was not selected.

enter image description here

These two should do it. Basically you are making the new user owner for the database so it has full access.

You don't need to restart SQL Server or anything, should work.

zar
  • 11,361
  • 14
  • 96
  • 178
8

1_in SqlServer Security=>Login=>NT AUTHORITY\SYSTEM=>RightClick=>Property=>UserMaping=>Select YourDatabse=>Public&&Owner Select=>OK 2_In IIs Application Pools DefaultAppPool=>Advance Setting=>Identity=>LocalSystem=>Ok

mehranSattary
  • 81
  • 1
  • 1
7

First thing you need to clear if you are using windows authentication and you are not mentioning any username password in your connection string then:

What happens when you run your code through localhost: when you run your wcf test client from localhost, it will be able to communicate to database as local debug mode application is calling database by your account's service. So it has access to database because devenv.exe is running under your user account.

But when you deploy your web service in IIS. Now understand this service runs under IIS not under your account. So you need to assign access rights to IIS service to access the sql server for windows authentication. Here your web service would not be able to communicate to the SQL server because of access rights issue and Login Failed for user_______ (here your user will come)

So if you are using windows authentication to connect your database, you just have to change the IIS Application pool settings. You need to change IIS Application pool's identity to local System.

Below are the Steps for windows authentication WCF:

1) Open IIS (windows+R (run) then type inetmgr, then click ok)

2) double click your PC name under Connections

3) Click Application Pools

4) Select your app pool (DefaultAppPool)

5) Then under actions on the right click Advanced Settings:

6) Go to Process Model section and

7) click on Identity.

8) Now select LocalSystem.

Now open your sql server management studio: open run-> then type ssms ->then press ok In ssms, login using your windows authentication account. Open security tab expand logins tab then you will be able to view your account.

Now open properties of your account go to userMapping then select the database you want to connect then check the role membership services you want to use for the selected database. click ok.

(For network services i.e. intranet users you need to configure above settings for NT AUTHORITY\SYSTEM user too)

add Trusted_Connection=True; property in your connection string. Save it & deploy the web service. Restart app pool.

you will be able to connect the database now.

Amar Gadekar
  • 387
  • 4
  • 11
7

I hate the ApplicationPoolIdentity. I always set a Windows User Account as the account on AppPools.

As adrift says, it does sound like a database security issue. So create an NT user account, assign it to the ASP.NET v4.0 AppPool and then grant it permission on the website folder and to the relevant table(s) in SQL.

Christian
  • 3,708
  • 3
  • 39
  • 60
6

Don't use Integrated Security. Use User Id=yourUser; pwd=yourPwd;

This solves the problem.

bonh
  • 2,863
  • 2
  • 33
  • 37
4

I had this issue and it was actually caused by something different - I had the 'IIS APPPOOL\ASP.NET v4.0' user in my database but it still wasn't working.

I had recently upgraded my SQL Server Install and in the process the User had become disconnected from the Login - so there was an 'IIS APPPOOL\ASP.NET v4.0' under Database -> Security -> Users BUT no User not under Security -> Logins.

Added the Login 'IIS APPPOOL\ASP.NET v4.0' to Security -> Logins, SQL Server automatically mapped it to the User in the database (this used to have to be done manually) and problem fixed.

The Coder
  • 4,981
  • 2
  • 29
  • 36
  • 1
    just to add ... at the left-hand side, under Permissions ... tick db_writer and db_reader; and select the database which will utilize those permissions. – benjieb Oct 16 '13 at 08:11
3

I had this message and I use Windows Authentication on the web server.

I wanted the currently authenticated web user to be authenticated against the database, rather than using the IIS APPPOOL\ASP.NET v4 User specified in the App Pool.

I found by entering the following in the web.config fixed this for me:

<system.web>
  <identity impersonate="true" />
</system.web>

https://msdn.microsoft.com/en-us/library/bsz5788z.aspx

I see other Answers regarding creating the AppPool username in the SQL DB or just to use SQL Auth. Both would be correct if you didn't want to capture or secure individual Windows users inside SQL.

Tom

Julian
  • 33,915
  • 22
  • 119
  • 174
tommylux
  • 79
  • 5
  • This solved it for us and we are not sure why. IIS/AppPool just hijacks the connectionstring that explicitly says "Integrated Security=true"? Why?? – Guy Aug 23 '17 at 21:40
3

I Have the same problem I solved it by changing Integrated Security=True to false now its working

subramanya46
  • 451
  • 6
  • 9
2

Setting the identity only makes this work in my pages.

Widor
  • 13,003
  • 7
  • 42
  • 64
charles
  • 29
  • 1
2

Cassini runs your website as your own user identity when you start up the Visual Studio application. IIS runs your website as an App Pool Identity. Unless the App Pool Identity is granted access to the Database, you get errors.

IIS introduced App Pool Identity to improve security. You can run websites under the default App Pool Identity, or Create a new App Pool with its own name, or Create a new App Pool with its own name that runs under a User Account (usually Domain Account).

In networked situations (that are not in Azure) you can make a new App Pool run under an Active Directory Domain user account; I prefer this over the machine account. Doing so gives granular security and granular access to network resources, including databases. Each website runs on a different App Pool (and each of those runs under its own Domain User account).

Continue to use Windows Integrated Security in all Connection Strings. In SQL Server, add the Domain users as logins and grant permissions to databases, tables, SP etc. on a per website basis. E.g. DB1 used by Website1 has a login for User1 because Website1 runs on an App Pool as User1.

One challenge with deploying from the Visual Studio built-in DB (e.g. LocalDB) and built-in Web Server to a production environment derives from the fact that the developer's user SID and its ACLs are not to be used in a secure production environment. Microsoft provides tools for deployment. But pity the poor developer who is accustomed to everything just working out of the box in the new easy VS IDE with localDB and localWebServer, because these tools will be hard to use for that developer, especially for such a developer lacking SysAdmin and DBAdmin support or their specialized knowledge. Nonetheless deploying to Azure is easier than the enterprise network situation mentioned above.

subsci
  • 1,740
  • 17
  • 36
2

If you have your connection string added in your web.config, make sure that "Integrated Security=false;" so it would use the id and password specified in the web.config.

<connectionStrings>
    <add providerName="System.Data.SqlClient" name="MyDbContext" connectionString="Data Source=localhost,1433;Initial Catalog=MyDatabase;user id=MyUserName;Password=MyPassword;Trusted_Connection=true;Integrated Security=false;" />
</connectionStrings>
Daming Fu
  • 53
  • 7
2

As pointed out, Do not use Windows Authentication, Use SQL Server Authentication

Also if you created connection using "Server Connection" dialog, make sure to check the connections in web.config. It is likely that you created/modified connection and it was stored as trusted connection in web.config. Simply use this authentication

<add name="MyDBConnectionString" connectionString="Data Source=localhost;Initial Catalog=Finantial;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient"/>

which should fix the error.

TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
2

Another way of granting permission to the database for the user IIS APPPOOL\ASP.NET v4.0 is as follows.
enter image description here


  1. Add New User with User Name and Login name as IIS APPPOOL\ASP.NET v4.0 with your default schema.
  2. Go to Owner schema and Membership, Check db_datareader, db_datawriter
Shanaka Rathnayaka
  • 2,462
  • 1
  • 23
  • 23
2

You can face this wrong in specific database which is created after SSMS update. Open SSMS and select your databases and open your required database then click Security--> Users--> and right click on Users and click again on 'New User' and add 'NT AUTHORITY\Authenticated Users' and save you work and go to your Form on Web/Desktop whatever do you. Enjoy....

1

Thought I'd post this as an answer as it is relevant to the question and can answer it in some cases.

That same message appears also if the database does not exist!

Be sure your connection string has no misspellings, is pointing to the right server instance, etc.

komodosp
  • 3,316
  • 2
  • 30
  • 59
1

In Asp.net webform,

this error fixed when installing asp.net from:

Server Manager > Manage > Add Role and Feature > Server Roles > Web Server (IIS) > Web Server > Application Development > ASP.NET 3.5/4.6 is installed.

my problem fixed.

Zolfaghari
  • 1,259
  • 1
  • 15
  • 14
1

something similar happened to me what worked for me was changing the property Integrated Security = True to Integrated Security = false in the web.config of the website

1

There are two solution to solve that problem. First is go to your IIS then click on your apppool, than select integret right click on it and go to advance setting and change identity apppool to local system. This should solve your problem in IIS.

However, if your problem is not solved, then go to web congih just remove integrated security =true from connection string and just give user id and password.

Then I hope your problem will be solved.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
talha baig
  • 29
  • 1
1

go to iis -> application pools -> find your application pool used in application -> click it and then click 'Advance Settings' in Actions panel. Find 'Identity' property and change it to localsystem.

Dharman
  • 30,962
  • 25
  • 85
  • 135
1

I ran into this error with IIS and dotnet core. A quick work around for anyone who are using Sql authentication, have your appsetting.json read this to disable Windows Authentication and use SQL authentication. (This is in the appsetting.json(default one) or anywhere you have specified your db connection string)

"dbContext": "data source=servername;initial catalog=dbname;user id=your_server_username;password=your_server_password;trusted_connection=false;multipleactiveresultsets=true",

cheers and happy coding

Antoh
  • 31
  • 2
0

Have you done what @Teddy recommended and you STILL get the same error?

Make sure you're changing the settings for the app pool that corresponds to your virtual directory and not the parent server. Each virtual directory has its own AppPool and doesn't inherit.

Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689
0

In DefaultAppPool set NetworkService in the Identity property and in Sql Server add User Network Service and give it the appropiate permissions to your database, that's work very well for me, I've tested locally but I think this is the best configuration for connecting from any other computer in the network. when you set LocalSystem in the Identity in IIS that's work well and it is not necessary to create any other user in Sql Server but I think that will not work in a network environment.

Luis
  • 1
0

I ran into the same problem testing ASP.NET Web API

Developed Web.Host in Visual Studio 2013 Express Database created in SQL Server 2012 Express Executed test using built in IIS Express (working) Modified to use IIS Local (from properties page - web option) Ran test with Fiddler Received error - unable to open database for provider.... citing 'APPPOOL\DefaultAppPool'

Solution that worked.

In IIS

Click on application pool 'DefaultAppPool' Set Identify = 'ApplicationPoolIdentity' Set .NET framework = v4.0 (even though my app was 4.5)

In SQL Server Management Studio

Right click on Security folder (under the SQL Server engine so applies to all tables) Right click on User and add 'IIS APPPOOL\DefaultAppPool' In securables on the 'Grant' column check the options you want to give. Regarding the above if you are a DBA you probably know and want to control what those options are. If you are like me a developer just wanted to test your WEB API service which happens to also access SQL Server through EF 6 in MVC style then just check off everything. :) Yes I know but it worked.

Brian Quinn
  • 57
  • 1
  • 4
0

In case you add a new login, make sure that under server properties ( rightclick -> properties)/security, authentication mode is set to both sqlserver and windows not only windows.

badr slaoui
  • 1,023
  • 11
  • 27
0

Add "Everyone" under security. If you added the Server and the users logging in to the database, then this is something you are missing. Hope this helps.

avinava basu
  • 119
  • 4
  • We set permissions according to our needs.The authentication part is handled and we check the authorization via code to allow access. Do feel free to correct me if you feel there's any ambiguity. Thanks. – avinava basu Sep 24 '16 at 10:13
0

For the record, if you encounter this error after switching from LocalDB to SQLEXPRESS, make sure the database already esists in SQLEXPRESS. You can verify this in Management Studio.

I had the same problem when using Entity Framework after switching to SQLEXPRESS from LocalDB. I had to run Update-Database command. I was able to successfully connect after that.

Irshu
  • 8,248
  • 8
  • 53
  • 65
0

I did exactly as @JeffOgata said but I got the error:

Windows NT user or group 'IIS APPPOOL\ASP.NET v4.0' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

I looked at my error message again and it said Login failed for user 'IIS APPPOOL\DefaultAppPool'.

After adding a user named IIS APPPOOL\DefaultAppPool everything worked.

Ogglas
  • 62,132
  • 37
  • 328
  • 418
0

I used SQL Server Profiler (available in SSMS => Tools menu) and saw there (when IIS attempted to connect to database) that my IIS user was for some reason NT AUTHORITY\IUSR, no matter of all steps recommended in answers in this question. So I added that user to SQL Server, and it worked...

alexkovelsky
  • 3,880
  • 1
  • 27
  • 21
0

Important enable 'sysadmin' in Server Roles

edu
  • 434
  • 1
  • 8
  • 17
0

In my case, I have imported database and it is not mapped. Once I checked the new database and it started working. Just check the unchecked database

enter image description here

vineel
  • 3,483
  • 2
  • 29
  • 33
-1

I have faced the similar issue while host the appication in IIS

Solution

I change the Pool Identity and its work me

ApplicationPoolIdentity -> NetworkService
Satish Kumar sonker
  • 1,250
  • 8
  • 15