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

To keep it simple, use SQL Server authentication with a username/password. It'll save you hours of troubleshooting. — Andomar, Mar 22 '14 at 14:21
@Andomar I really really prefer that solution, but how to do it please? the user of IIS is the one mentions above — user3432257, Mar 22 '14 at 14:23
Instead of `integrated security=SSPI;`, specify `User Id=myUsername; Password=myPassword;` — Andomar, Mar 22 '14 at 14:24
@Andomar I just understand what you said. I tried to use the `sa` with the password but still have the same problem — user3432257, Mar 22 '14 at 14:25
@Andomar I will show you the connection string of that connection — user3432257, Mar 22 '14 at 14:25
@Andomar please check the edited question, not I got error login for that user,which is mention in the question, not the sa. — user3432257, Mar 22 '14 at 14:28
@user3432257 Andomar said INSTEAD of integrated security - not "in addition." — TomTom, Mar 22 '14 at 14:28
Looks like you're specifying `Integrated Security` and `User ID` at the same time. Remove the `integrated security=SSPI;` setting to use SQL auth. — Andomar, Mar 22 '14 at 14:29
@Andomar I got this error `Cannot open database "RESv5" requested by the login. The login failed. Login failed for user 'sa'.` — user3432257, Mar 22 '14 at 14:32
@Andomar I checked the users of that database and sa is not existed there — user3432257, Mar 22 '14 at 14:32
Make sure the `sa` login has the server role `sysadmin`. If so, it should be able to connect to every database. For non-admin logins, check out the `User Mapping` tab. — Andomar, Mar 22 '14 at 14:34
@Andomar the sa has the sysadmin. but as i told you the sa is not a user for my database. help please — user3432257, Mar 22 '14 at 14:35
@Andomar I tried to create a new login with new user but i got that the new user login is falied — user3432257, Mar 22 '14 at 14:49
Right click the SQL Server, go to the Security tab, and check if "SQL Server and Windows Authentication mode" is checked — Andomar, Mar 22 '14 at 14:51
@Andomar ofc the mix mode is selected, otherwise, I wouldn't be able to create a login. right? — user3432257, Mar 22 '14 at 14:53
Can you connect using Management Studio? (For example, File -> Connect Object Explorer) — Andomar, Mar 22 '14 at 14:57
@Andomar Yes I can. Also, I can connect using windows Authentication. Please help me. I don't know what to do — user3432257, Mar 22 '14 at 15:00
Double check the database name; are you using the exact same name in SSMS as in the config string? You can also browse through the SQL Server logging and see why your connection was rejected. [See this MSDN article](http://technet.microsoft.com/en-us/library/ms366351%28v=SQL.105%29.aspx). — Andomar, Mar 22 '14 at 16:11
is your database located on the same machine as your webserver? — bhupendra patel, Mar 22 '14 at 16:12

score 7 · Answer 1 · answered Mar 22 '14 at 16:45

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

Milica Medic Kiralj · Answer 2 · 2014-03-22T15:50:02.280

2

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

enter image description here

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

edited Mar 22 '14 at 15:50

answered Mar 22 '14 at 15:06

Milica Medic Kiralj

3,580
31
31

I already figured that. now my connection string is `` 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 – user3432257 Mar 22 '14 at 15:15
Please see an update to my post, this may help you with step by step explanations :) – Milica Medic Kiralj Mar 22 '14 at 15:50

score 1 · Answer 3 · answered Mar 22 '14 at 16:03

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).
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.
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"

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

edit1

3 Answers3

Linked