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

Solution 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

Solution 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

Solution 3

1. Your website is running under service account NETWORK_SERVICE in IIS, this is a computer user and can't access your SQL. To fix this you should set your website to use "identity impersonate=true" in your web.config. However if you are running this on the server you might lose the authenticated user in what's called a 'double-hop' where your IIS server cannot relay your authenticated user to the SQL server in another step (PC (1)-> IIS (2)-> SQL).

2. Milica Medic is correct in her answer but in your reply you specified a user "william" is this a SQL user or a windows user? If Windows user then this won't work, to relay windows users to SQL you need to do it in web.config (see 1. above). The sql connection assumes the user is a SQL user not windows user.

3. your attempt with username/password is close but you are still specifing Integrated Security=SSPI which tries to force windows user authentication to the SQL.

Try and make a connection string like this:

connectionString="Data Source=SQLSERVERINSTANCE;Initial Catalog=myDB;Persist Security Info=True;User ID=sa;Password=myPassword"

Comments

[user3432257]

I already figured that. now my connection string is <add name="TestDbConnectionString" connectionString="Data Source=(local);Initial Catalog=RESv5;User Id=William;Password=mypassword;" providerName="System.Data.SqlClient" /> but I got that william can't login. I want to connect using sa. is it possible? or what should I do to login. please help. it has been 7 hours without solving the problem

[Milica Medic Kiralj]

Please see an update to my post, this may help you with step by step explanations :)