Add user to SQL Server 2008 - permission denied

Question

I have recently had some problems that I think might be helped by debugging my ASP.NET MVC application in IIS instead of with the default ASP.NET Development Server. However, when I try this the application can't access the database server (MS SQL Server 2008 Express) - it works fine on the Development Server though.

The error I get is

Cannot open database 'myDbName' requested by the login. The login failed. Login failed for user 'NT INSTANCE/NETWORK SERVICE'.

I assume it worked before because the development server accessed the database through my user account, and not via the network service like IIS.

I tried adding the user NETWORK SERVICE to the database, but it turns out I don't have the user privileges to do so - it doesn't matter that my Windows account that I log on to the server with is admin, or that I run the program in an administrator context. I just can't add another user to the DB.

How do I solve this problem?

Answer 1

NETWORK SERVICE is a special account. If your database server is on another machine from your IIS server, you need to add the account:

\$ as a user for the database. So if you're on a IIS Server called LISA on the SPRINGFIELD network, you would need to grant access to SPRINGFIELD\LISA$ to your database. Note the dollar sign which denotes your user as a machine account and not an actual person.

If your database server and your IIS server are one and the same, grant access to the account "NT AUTHORITY\NETWORK SERVICE". That's the whole name of the account. And that should work.

Answer 2

You could change the user that IIS runs under: it's a setting in the properties for the ApplicationPool that your web site's running under.

We do this all the time as it gives us better control over user access - that NT INSTANCE/NETWORK SERVICE account's a bit too magic for my liking...

Answer 3

Are you running a non-english Operation System?

For some reasons, the account name of "NT Authority\Network Service" has been localized in other languages.
Sadly, a lot of programs have the account name hard coded to the english name, and won't find the Network Service when running on foreign versions of Windows.