Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm trying to migrate a website from SQL Server 2008 Express to SQL Server Web, and the website is having trouble accessing the database. In truth, Express was only ever used by running the website from Visual Studio's builtin web server, so I'm not sure about the configuration working all that well under full-blown IIS 7. At any rate, when I try to access a page that uses Linq to Entities (requiring the website to connect to the database), I get the error "Login failed for 'IIS AppPool\ASP.NET v4.0'". So, I opened SQL Server Management Studio, and I attempted to add a login for the user, but I just get a message stating "Windows NT or user group '*\Asp.Net v4.0' not found". This happens even after I ask Management Studio to check the name of the user, which it does successfully. Can someone tell me what I'm doing wrong? Also, I'm open to alternative suggestions as well... Everything's running on the same server, so there are no remote connections. For this reason, I'd love to use Windows Authentication if at all possible.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

IIS 7 is trying to use the IIS application pool identity to access your SQL database.

So you must first grant the IIS 7 apppool access to SQL server.

To do that, go in SQL Server Management Studio -> Server Instance -> Security -> Logins -> New Login

Login name will be "IIS APPPOOL\ASP.NET v4.0". This is because everytime you create an application pool in IIS7, it will create an identity which uses the same name as your app pool. In your case, you are using the default app pool which comes with asp.net 4.

Then on your database, go to Security -> Logins -> New login , give it a alias name, then select your user object you added in your previous step (IIS APPPOOL\ASP.NET v4.0) then give it db_owner (or whatever your needs are) schemas/role privileges.

share|improve this answer
    
@kimphamg: Thanks for the reply. I tried that, but after entering the login name you suggested, Management studio says "Windows NT or user group '[Server Name]\Asp.Net v4.0' not found". Also, since the website hasn't been able to access sql server, I can't seem to find the database in the object explorer of Management Studio. I don't think it's been attached yet... –  user55974 Oct 2 '10 at 17:24
    
Should I create a local user on the server called "Asp.Net v4.0"? –  user55974 Oct 2 '10 at 17:38
    
Is that necessary? –  user55974 Oct 2 '10 at 17:38
    
Wait, are you using IIS 7 or IIS 7.5 ? –  kimphamg Oct 2 '10 at 17:58
    
It's IIS 7.5... –  user55974 Oct 2 '10 at 18:04

EDIT: Please don't do this -- I wasn't thinking when I originally wrote this answer.

For more details why my original answer isn't a good idea please review the link in the comments below.


Another solution is to go into IIS and edit the application pool itself to use the identity "LocalSystem." This can be found by right click the app pool -> advanced settings -> click Identity's '...' button (right below "Process Model") -> select "LocalSystem" under the Built-in account drop-down.

Doing it this way you don't need to set up a new user in your database.

share|improve this answer
    
Please don't do this. –  Amit Naidu Nov 7 at 20:08
    
@AmitNaidu Can you explain why, please? –  emragins Nov 7 at 20:43
    
Sorry, reading it again, I had assumed that there was an application-specific application pool. I can totally agree with not changing the user on the default application pool. –  emragins Nov 7 at 20:45
    
It's a huge security hole to run a site under that account. I'd explain further but the commenters on this answer have already done a great job of it. –  Amit Naidu Nov 7 at 21:30
    
Gotcha. I've updated my answer to steer people away from this approach. –  emragins Nov 7 at 22:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.