IIS7, SQL 2008 and ASP.NET MVC security

Question

I have an ASP.NET MVC application that I'm working on. I've been developing it on Windows Server 2003 with IIS6 and SQL 2008 Express, and everything was working great. I recently decided to try out the Windows 7 beta, so now I'm using IIS7, and have run into a problem with connectivity to my database that I can't seem to figure out.

I can run/debug the app just fine, but whenever I try to access a page that needs to access the database, I get the following error:

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

I've obviously got some security configuration setup incorrectly, but I can't seem to find any good documentation on how to set it up correctly. I've tried giving NETWORK SERVICE permissions on the database, but that didn't seem to work. Anyone know what I need to do to give "IIS APPPOOL\MyApplicationName" permissions to this database? Am I missing something obvious?

Thanks...

Answer 1

If you are NOT using Active Directory, then ignore all of the other solutions mentioned here. The confusion stems from the new ApplicationPoolIdentity setting default in IIS 7.5+ (MS keeps changing the identity mechianisms)

1. Open SQL Management Studio, connect to your local machine as an admin.
2. Expand the Security branch.
3. Right click on Logins and select New Login
4. Into the Login Name field, type "IIS APPPOOL\MyApplicationName". Do NOT click the search button. The user profile dosn't actually exist on the local machine, it's dynamically created on demand.

While you're looking at it, don't forget to add the user to a database or a server role.

Answer 2

The error means the web application doesn't have access to your database. On Windows 7 / IIS 7, by default each application pool has its own user. It seems the idea is to improve security by restricting what that web application can do (in case it gets compromised and controlled from the outside). You can change what user the application pool is running under but that will defeat its own purpose. A better way seems to give the pool's user the needed permissions (and not a bit more).

On the SQL Management Studio connect to the server you want your web app to connect (tested with SQL server 2008). Go to

Security -> Log ins

right click, New Log in. In the form that comes up leave everything as default except username, where you have to type whatever username the web app is trying to use, in this case 'IIS APPPOOL\MyApplicationName'. Note that the search function of that dialog fails to find or check as valid that user, but nevertheless it works.

Still on the SQL Management Studio connected to the server go to

Databases -> *YOUR-DATABASE* -> Security -> Users

right click and New User. I'm not sure if the user name field there has any effect, I just set it the last part of the username, like MyApplicationName. Then I've set the login name to IIS APPPOOL\MyApplicationName. You can click on the ... button and use the check and search, this time it works. If you don't do the previous step, the user will not be present here. Then give it whatever permissions you want to this user, like db_datareader.

And that's it, you've given permission. If lack of permissions was your problem, then it should be solved (or at least, I've just solved it that way).

I have a total amount of 2hs of experience with IIS and about three weeks with SQL Server and less than two months with Microsoft technologies so take my advice with a grain of salt, I can be totally wrong. (If another person can confirm these are the right steps, feel free to remove the last warning).