This is an old thread - and people are still hitting it. I just installed SQL 2012 express and IIS7, and set the app pool of my application to NT AUTHORITY\NETWORK SERVICE. And immediately ran into the trouble that is mentioned on this thread.
I want to summarize to help all of those who come after me - this was not a "one thing will do it" for my case.
What did work (all of the changes happen in SQL server management studio)
1. In SQL server management studio, create a DB account for the windows credential 'NT AUTHORITY\NETWORK SERVICE'
2. Then in your db for your app, set up a user account with the right permissions so that your new user account is known to your DB. (this is not enough though). I chose "db_reader" and "db_writer" for my web app that can read/write to DB, also
granted "execute".
3. Open a sQL query window and type the following and then execute
USE MASTER
GO
SP_GrantLogin 'NT AUTHORITY\NETWORK SERVICE'
GO
SP_GrantDbAccess 'NT AUTHORITY\NETWORK SERVICE'
GO
USE MYDBNAME <-- substitute your database name for "MYDBNAME" on this line, and omit everthing after the DB name
GO
SP_GrantLogin 'NT AUTHORITY\NETWORK SERVICE'
GO
SP_GrantDbAccess 'NT AUTHORITY\NETWORK SERVICE'
GO
Now - click the execute button (the red exclaimation point), and these commands will all run.
After that, at least for me, my app was able to connect to the DB just fine. My app is using .NET 4.5 and entity framework to access the DB - so if this helps you in the same circumstances, then good.
Some comments on this long thread. Many of the suggestions will open you up for problems security wise. Particularly, don't run an app pool as NT AUTHORITY\LOCAL SYSTEM - this is the system administrator on your machine, and if you get hacked,
it's an easy way to gain access and install all kinds of nasties.
I can't say that I am in favor of the new IIS 7 app pool default identity - it is too much of a nobody if your app needs to access a full SQL server DB on another server. This identity is great for MSFT because it doesn't translate to any credential
or identity over a network - so that protects us from bad reputation etc. But for apps that have to use the network to get to another service or a remote DB, it's a useless facade. In these cases, always create a domain and a domain account for
your app, and preferably a different domain account for each unique app so you can control access on a per app basis.
Another, sometimes better option, is to turn on ASP.net impersonation in the application pool in IIS. What this does is let an authenticated domain user have their own credentials passed through to calls made by the web page. It's a bit harder
to manage ACL's in the DB this way because you either have to create a group per app (or two or three depending on permission permutations) and then manage users in groups on a per called resource basis.
Of the two, it's a preference or policy decision - but I personally like when I can go with a domain id from the app pool, and use IIS role based access to control which users (in which groups) get to which pages. If they cannot run the page, they
can't perform the functions on the page, so the delegation of identity through IIS is a lower cost way to manage the administration of complex security.
Good luck all - may the NETWORK AUTHORITAY gremlins never bight you again (misspelling on purpose for humor)
Microsoft Corporation
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'
