21

I having the following error message:

Cannot open database "SmallBakery" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\MyAppPool'

How can correct this? I am using windows 7 Enterprise Edition and Sql server 2012.

Linus Caldwell
  • 10,908
  • 12
  • 46
  • 58
Freddy
  • 319
  • 1
  • 3
  • 7

5 Answers5

21

If you don't change, each app pool has it's own identity. In your case, just add a new user to your database SmallBakery with the name IIS APPPOOL\MyAppPool using SQL Management Studio. You find the users list in the "Security/Users" subnode of your database. This should look something like that:

enter image description here

For testing, let the user be member of the db_owner role. If that works, remove this role and just let it be member of db_datareader and db_datawriter.

This way, each app pool (perhaps each website, if they all use their own app pool) only has access to the corresponding database.

Linus Caldwell
  • 10,908
  • 12
  • 46
  • 58
  • 2
    The additional insight in the following answer helped me resolve the same error: http://stackoverflow.com/a/1973895/704808 – weir Nov 14 '14 at 21:18
  • 1
    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
1

It depends on how you want to authenticate in your app. Are you trying to use impersonation in your app?

What's happening right now is the identity of your app pool in IIS is getting passed when trying to access the database. You can change the identity of your app pool to be something that has access to the database or you could change your connection string to use a specific login instead of integrated security.

squillman
  • 13,363
  • 3
  • 41
  • 60
  • I'm using the following connection string: connectionString="Data Source=HAIDRWHP5005160\SQLEXPRESS;Initial Catalog=SmallBakery;Integrated Security=True" In IIS App Pool I have: Idendity: ApplicationPoolIdentity – Freddy May 07 '13 at 20:11
  • Right. So like I said, you will need to change the Identity of the app pool to something that has access to the database or change your connection string. – squillman May 07 '13 at 20:24
  • 1
    ... or add the app pool identity as an user to your database. – Linus Caldwell May 07 '13 at 20:26
  • Yes, that'll work if you keep the database on the same box as IIS. If you move them to different servers it's best to do it as a regular domain user. – squillman May 07 '13 at 20:35
0

Check this post out. Your problem sounds similar to one I was running into, with the same exact error message.

http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx

Mike Marks
  • 10,017
  • 17
  • 69
  • 128
  • i was seeing something similar in http://stackoverflow.com/questions/7698286/login-failed-for-user-iis-apppool-asp-net-v4-0 – Freddy May 07 '13 at 20:37
  • @Freddy, As the comment on the answer you're refering to says: "If you are concerned about security, don't do this". – Linus Caldwell May 07 '13 at 20:46
0

Like others said so far you need to take your app pool system user (IIS AppPool\myapppool) and add it as database user for that database with appropriate permissions.

Note that this will work just find on your IIS/Server but if you plan on migrating application to a different IIS/Sql server it will require changes in both SQL Server and IIS. I’d suggest you also consider sql server authentication – it may be more convenient for your specific case.

Orland Mendes
  • 516
  • 4
  • 5
0

In my case these steps lead me to successfully handle this error.Hope it will help you also

enter image description here

Irtiza
  • 35
  • 10