Why is my MVC app trying to log into my DB as my machine, and not as the App Pool identity?

Question

When I try and access my newly deployed (to lcoal IIS 7.5) MVC4 app, I get the error:

Login failed for user 'DOMAIN\MACHINE-NAME$'

where the '$' is appended and not part of the machine name.

The connection string in web.config looks like this:

<add name="ComairRIEntities"
     connectionString="metadata=res://*/Data.ComairRI.csdl|res://*/Data.ComairRI.ssdl|res://*/Data.ComairRI.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=MyDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
     providerName="System.Data.EntityClient" />

Answer 1

This is what's going on:

In your connection string you have the following setting: integrated security=True What this means is that the SQL Server connection will be authenticated with the credentials of the process which initiates the connection. Since you are running under IIS and IIS uses application pools, the connection will be authenticated with the Windows user which runs the application pool. By default this is a user with almost no permissions called NetworkService. NetworkService (or maybe in IIS7.5 it's a different one) will never have access rights to your database. The nuances of your particular scenario might be a bit different because there is a bunch of different security inheritances in IIS and a bunch of different users your process may end up, however, the basic problem is that you have integrated security=True and the user the IIS process is running with is a standard user with almost no rights.

To fix you have a few options:

1. Change integrated security=True to username\password authentication. This will solve it 100%, but you may not want to store your password clear text in the web.config file.
2. In your IIS virtual directory settings, configure the anonymous user to be a meaningful one which has access rights to your db. This will help eventually, but you will have to play with different settings to get it right.

If you need more help with #2, you have to provide the following information:

1. The identity of the AppPool
2. The identity of the Virtual Directory and all the authentication settings of the virtual directory.

Answer 2

There is plenty of good information in this question: Login failed for user 'DOMAIN\MACHINENAME$'.

If you see a failure like Login failed for user 'DOMAIN\MACHINENAME$' it means that a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization.

What seems odd here is that you are still trying to access a local database, yet a username of DOMAIN\MACHINENAME$ implies that it is accessing a non-local database.

Are you certain the connection string you posted is in fact the one that is used?

The other thing you could look at doing is creating a specific user account for the application pool your site is running in - it would most likely need read and write permissions.

The type of user account will depend on your environment: if you are running within a domain, you could create a domain user and continue to use integrated security=True in your connection string, or if not you could investigate using SQL authentication.

Edit:

I had this exact error once, doing almost exactly the same thing. In my case the database was on a separate server (i.e., not the same machine as it appears to be in your case), but the solution was this:

1. Create a domain account.
2. Add it to Security\Logins and Security\Users in SQL Management Studio.
3. Provide it with db_datareader and db_datawriter role membership in SQL Management Studio.
4. On the web server, run aspnet_regiis -ga domain\account_name
5. Set this account to be the one used for anonymous access.
6. Create a new application pool for this web application.
7. Set the identity of the application pool to be this account.

Note that this was for IIS 6, so if you are in IIS 7+ you may not need steps 4, 5 and 6.