sql server 2008 Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

Question

I am trying to connect my website to my sql server 2008 r2 on windows server 2003 with .net framework 4

This is the connection string:

<add name="TestDbConnectionString" 
     connectionString="Data Source=(local);Initial Catalog=RESv5;integrated security=SSPI;"
     providerName="System.Data.SqlClient" />

I got an exception, which is:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I saw this question Login failed for user 'NT AUTHORITY\NETWORK SERVICE' and I tried to make a user named as NT AUTHORITY\NETWORK SERVICE but I still have the problem,

edit1

I have already tried to use a username and password. this is the connection string

<add name="TestDbConnectionString" 
     connectionString="Data Source=(local);Initial Catalog=RESv5;integrated security=SSPI;User Id=sa;Password=myPassword;"
     providerName="System.Data.SqlClient" />

but still have the problem

Answer 1

You're using Windows authentication to enter the server, so it uses the Windows account access from the client to validate access/permissions. When in development, the "client" is really the VS development server, but when you deploy to a real web server, the "client" runs as another account, the one that is used to start the its service, NOT yours.

Often SQL Servers are configured at installation to allow yourself sysadmin access, but barely anything else to other users, that's why you get an access denied. One option would be to use SQL authentication with user/password. Security-wise, this would be the best option if the same web server runs many websites (as they may be isolated from each other data's).

If you absolutely want to use Windows authentication, the real solution would be to give permissions to the built-in account NT AUTHORITY\NETWORK SERVICE at SSMS:

CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH DEFAULT_DATABASE=[RESv5]
GO
USE [RESv5]
GO
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
GO
ALTER ROLE [db_owner] ADD MEMBER [NT AUTHORITY\NETWORK SERVICE]
GO

Answer 2

Try removing the integrated security=SSPI from the connection string, or set it to false. See below my successful login with sa user:

EDIT: Here is a detailed explanation, maybe it help you to find where you're wrong:

http://msdn.microsoft.com/en-us/library/ff648340.aspx