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

This error usually means that the user that your site is running as (or more to the point the application pool), does not have permissions to use the DB. You can either check in IIS what user the pool is running under and give them permissions, or instead change your SQL connection string to not use trusted authentication and supply the credentials of a user that does have permission in the connection string.

Edit: If you right click on the pool Identity section and go to properties, it should come up with a box that lets you either choose from 3 builtin system accounts, or specify your own account. Either give one of the builtin accounts permission for the DB, or use an account that has permission. Or leave it as is and change your connection string.

Answer 2

The "Identity" property for my ApplicationPool is currently set to "ApplicationPoolIdentity", which doesn't seem to be an existing account that I can add to SQL Server. Does that mean that I've not actually set the account and I need to? Does this need to be set to NETWORK SERVICE or something like that?

Answer 3

Well...changing the ApplicationPoolIdentity property and setting it to NETWORK SERVICE seems to have fixed my problems. Not sure if that's the "right" way to do things or not (as in, I'm not sure if that's the recommended way to do things in IIS7 or not), but it seems to at least be working and has gotten me past this hang-up for now. Thanks.

Answer 4

I'm using windows authentication.

Answer 5

The first step is to verify which account your website is running under. Create a simple aspx page with:

<%@ Page Lenguage="C#" %>
<% Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name); %>

If you're using windows authentication the WindowsIdentity account will need to have a login in your SQL Server. Under Security -> Logins -> Login New you'll want to add whatever name that was displayed by WindowsIdentity and make sure Windows authentication is selected.

If you ever happen to move your database to a separate machine you'll have to create a domain account and use impersonation in your web.config. Google <identity impersonate="true"> for more info.

Answer 6

I'm familiar with the idea of giving permissions to the user that the application is running under...my problem is that in IIS7, the "user" seems to be virtual or something strange like that. Prior to me changing the "Identity" property of the Application Pool properties to NETWORK SERVICE, it was set to "ApplicationPoolIdentity", and the error I was getting was that "IIS APPPOOL\MyApplicationName" didn't have access to the database. When I attempted to add the "IIS APPPOOL\MyApplicationName" user to the database, it didn't appear to exist...not that I could find anyway.

So my ultimate problem is not understanding or being able to find any good documentation on how the IIS7 security model works. When I created the application, it seemed to create an AppPool with the same name just for this application. I don't know exactly what changes I need to make to give the application and/or the user it runs under privileges to the database, considering the fact that the user that the AppPool runs as doesn't appear to actually exist.

As I mentioned, changing the Identity of the AppPool to NETWORK SERVICE seems to have worked for now, but I'm trying to find out what the best practice is for this kind of thing under IIS7. Thanks.

Answer 7

leave the hard problems for someone else -

create a sql user and use SQL Auth. :D

Answer 8

If you look in the description of the field it states that running under "Network Services" account is the recommended account to use. Not sure why in Win7 it defaults to the ApplicationPoolIdentity setting.

Answer 9

Here is an article that explains why AppPoolIdentities are in use; basically, it's about enhanced security: http://learn.iis.net/page.aspx/624/application-pool-identities/

(That article claims I can use these virtual accounts just like any regular account but on my Windows Server 2008 that does not seem to be possible; adding e.g. IIS AppPool\DefaultAppPool just produces an error: "The following object is not from a domain listed in the Select Location dialog box, and is therefore not valid.")

Answer 10

I have the exact same issue. I'm running Windows 7 RC. When I'm trying to usa a .mdf file (located in App_Data), there is now way to make that thing work. I did try to change the AppPool's identity for LocalSystem, but it simply won't work.

If I use a "standard" database, then it will work if I'm using LocalSystem, but it won't work with the famous 'IIS APPPOOL\DefaultAppPool'.

I find it a bit disturbing not to find any information on that matter, it seems that the 'IIS APPPOOL\DefaultAppPool' user is totally useless if you are using a database of any kind...

I have it running, but I'm also bit frustrated not to understand the security model, as stated by ryexley.

Answer 11

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

Answer 12

If you follow Mr. Fernández' advice, you will get everything working. This is the new way of giving least privilege to a site.

So don't do the easy, less secure thing (NETWORK SERVICE). Do the right thing. Scroll up. ;)

Answer 13

If you are NOT using ActiveDirectory, then ignore all of the other solutions above. 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.

Answer 14

Todd Smith,

I have same problem trying to use ASP.NET Configuration Icon in VWD2008EE. Clicking the Icon and then security.aspx hyperlink gives me the following message: "Unable to connect to SQL Server database". I took your advice to verify which account the website is running under and it reported back as "NT AUTHORITY\IUSR". I verified this account exists in "SQL Server Management Studio 2008 R2" and that it has "public" and "sysadmin" server roles. And that the account login uses "Windows Authentication".

IIS 7.5 Application Pool Identity is "ApplicationPoolIdentity" and the name is "DefaultAppPool". Is this part of my problem? Shouldnt the account that the website is using be the same name that IIS 7.5 is configured to use? I dont know.

Opening a "File->New Web Site" in VWD2008EE and then clicking on ASP.NET Configuration Icon produces the following error. "Unable to connect to SQL Server database". The App_Data folder is created and added to the new website which can be seen in VWD2008->Solution Explorer and Windows Explorer but it fails to create a new "ASPNETDB.MDF" file.

What am I missing? Anyone that can help me with this issue will be greatly appreciated. I have wasted way too much time on this problem with no success in resolving it. Microsoft hasn't exactly implemented this new feature in the most intuitive manner. At least not in my opinion. Thanks for any help.