SqlClient login fail when trying to connect to database

Question

This is so weird. Ok here's my connection string in the web.config of my MVC 4 project:

Here is the connection string in my Unit Test Project's App.Config file:

 <add name="ConnectionString" connectionString="Data Source=THESERVERNAME;Initial Catalog=THETABLE;Persist Security Info=True;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

(I've replaced real names with fakes for this posting privacy only)

My unit test is:

[Test]
public void GetMember_ByContractNumber_ReturnsAValidMember()
{
    // Arrang
    const Int64 contractNumber = 2604914977;

    // Act
    DTO.Member member = MemberCRUD.RetrieveMember(contractNumber);

    //Assert
    Assert.IsNotNull(member);
    Assert.IsNotNullOrEmpty(member.FirstName);
    Assert.IsTrue(member.ContractNumber > 0);
}

It passes just fine! I get a valid member object back from the database CRUD call.

The Problem:

But at the same time, when I try to make essentially the same call and same connection string from an MVC controller, the the same CRUD method in my DL bombs, it out at this point in code:

and I get this exception:

{"Login failed for user 'OURDOMAIN\\MYPCNAMEISHERE$'."} System.Data.SqlClient.SqlException

This database is remote, not on my local system.

So obviously it's permissions but I don't see how to resolve this. I think the reason my unit test passed is because it was maybe using my windows account as the identity when making the call to the DB whereas IIS for my MVC app was using the AppPoolIdentity. So not sure how to fix this. In my web.config I have Authentication set to None but I also tried setting it to Windows auth and enabling windows auth in IIS...no luck.

I'm trying to run through my settings but no combination has worked so far for this permission issue.

Again IIS is setup to use AppPoolIdentity but I also tried Network Service as the identity and added Network Service as a user and gave it permissions to my SQL DB..no luck. I'm gonna try to add the AppPoolIdentity to my SQL Server DB but I doubt it's gonna work still. Not sure what to try. -------------------------------- SUPPORTING PICS / Very Latest Settings ------------------

in my web.config

(I've got dbo rights on my AD account below)

UPDATE:

Here's what I just tried as of the latest:

1) Added the following users to SQL Server Security for my DB and gave them DBO rights:

• NT AUTHORITY\ANONYMOUS LOGON
• DOMAIN\MYPCNAME$
• NT AUTHORITY\NETWORK SERVICE

2) Added the following users to the file system security permissions for my app folder:

• Authenticated Users
• IUSR
• SYSTEM
• NETWORK SERVICE
• MY AD ACCOUNT (DOMAIN\MYACCOUNT)
• IIS APPPOOL\MyAppPoolName

3) Then tried out a few things..all failed with the same error (Login Failed) when I render the MVC View:

changed the app pool identity to use the Local Service. It then tried to connect to the SQL DB using NT AUTHORITY\ANONYMOUS LOGON - I get login failed for NT AUTHORITY\ANONYMOUS LOGON

changed the pool identity to use Local System. It tried to auth to SQL using 'DOMAINNAME\MYPCNAME$' - I get login failed for DOMAINNAME\MYPCNAME$

changed the app pool identity to use Network Service. It tried connect using 'DOMAINNAME\MYPCNAME$'. - I get login failed for DOMAINNAME\MYPCNAME$

changed the app pool identity to use ApplicationPoolIdentity. It tried to connect using 'DOMAIN\MYPCNAMEN$'. - I get login failed for DOMAINNAME\MYPCNAME$

Answer 1

You have a few options:

• Change the account your application pool runs under to a (domain) service account, giving this "user" appropriate permissions to the database and running iisreset /noforce after making changes.
• Use impersonation in your code before connecting to the database (e.g. http://www.codeproject.com/Articles/10090/A-small-C-Class-for-impersonating-a-User).
• Give your computer account access to the database (i.e. the account which you see in the exception message can be added as a DB user).