Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

Question

Any help would be very very much appreciated as I am about 15 hours into this :(

Background is a development system with ASP.NET 2.0 and SQL express 2005. The server is SQL 2005 standard edition. Any ASPX pages that connect to a database results in errors.

I have 2 identical servers with Windows server 2003, one has SQL Express and the other has SQL Server 2005 standard. that is the only difference between these systems. The scripts that work seamlessly when uploaded to the SQL Express server dont work on the SQL Server 2005

My connection string is

 <remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Datasource=servername;Integrated Security=SSPI;initial catalog=C:\INETPUB\WWWROOT\test\app_data\aspnet.MDF;"
   providerName="System.Data.SqlClient" />

two interesting tidbits

1.) No matter what the initial catalog is pointing to, I get the same error. EVEN if the database doesnt exist

Cannot open database "C:\INETPUB\WWWROOT\test\app_data\aspnwet.MDF" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

2.) second interesting thing is this, if I change the datasource to "MSSQLSERVER" which is the instance name of SQL 2005. the error changes to

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have reinstalled SQL server 2005

I have verified that under SQL Server 2005 Surface Area Configuration that remote connections has Both TCP and Named Pipes enabled

I have verified that under the Network Configuration that the protocols for TCP/IP and Named pipes are enabled.

I have tried atleast 20 different variations of Connectionstrings

I have ran aspnet_regsql against the database

I have verified that the SQL Server Browser is started

I have verified that the TCP/IP is set in Network Configuration to default port 1433

I have gave all authentication rights to Network Service and ASPNET accounts for testing against both the MASTER and ASPNET databases using Management Studio and attaching the Database

I have created a custom SQL account with access to the database in question and added the username password syntax to the connectionstring and get the same login failed message.

If anyone can give any insight that would be MUCH appreciated!! thanks in advance.

 

Answer 1

Akiren.

  (1) You are failing on user permission check to sql server as whole not individual database, so no matter what catalog your refering to. I assume the sql server is running on a local machine. This is a IIS and ASP.Net configuration issue, I would recommend you to check with ASP.NET forum and IIS forum w.r.t connection authentication to sql server. I think you probably need to set connection impersonation in IIS or ASP. Also, you can check SQL security forum. There is a difference that special to sqlexpress in authenticate user connections.

 

   (2) datasource, if it is not (local), "." or "<empty", it will be refer to as <servername>[\<instancename>]. In your case, MSSQLSERVER is not a valid <servername>, so the connection string does not work.

 

 

Answer 2

Its not an IIS or ASP.NET configuration issue as far as I can tell. Even if I goto the command prompt and type

sqlcmd -s SQLINSTANCE -e

I get the same named pipes sql server does not allow remote connections. The SQL Server is local.

One interesting tidbit, whenever I try to put SERVERNAME\INSTANCE in the connection string or even dos I get an invalid connection string.

Answer 3

Maybe I didn't explain to you clearly.

The item(1)  was to your first issue, and (2) was to your second issue. Your first issue looks like a ASP/IIS issue. The second issue is because you are composing a connection string that does not point to a real server.

 

Is SQLINSTNACE your machine name?

For the default instance, you can use <machinename>,

For nameinstance, you can use <machinename>\<instancename>.

 

Answer 4

Hi Akiren,

How are you?

There are only  two solutions to your problem.
1. Close the MS Visual Studio (it will stop the SQL express) and run your application via IE (assuming that the you've configured your Virtual directory).
2. Detach from SQL Express and transfer your DB to other SQL Server 2005 edition.

SQL Server Express Edition allows only a single connection to an .mdf file

Erwin De Leon
www.mrwebservice.net

Answer 5

Hi,

 

You´ve to change "Integrated Security=SSPI" to "Integrated Security=false";

I think it will fix it!

 

[]´s Breno

Answer 6

 

This is an internal acct that is not displayed in user manager - you can't depend on a GUI....

 

submit this line into query analyzer on master or your database to grant access:

sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

 

Its scary to read about the security holes people opened up through ignorance in this blog 

Answer 7

Hi.  I am having the exact same problem!  I tried all the solutions presented and still no luck.  I have the small business starter kit installled on Win2k Adv Srvr where I have trial Studio Web Express w/SQL express no problems.  Move it to a Small Business Server change the .\SQLExpress to the localhost server name ( server11 ) no problem.  Move it out to a site with SQL 2005 trial, try every connection string possible and all the blog solutions - no luck.  Did you ever get a working solution?  Thanks ... I'm pretty new the developer scene but pretty saavy.

Answer 8

Hi Juan,

You are probably having problems because your SQLServer Security is not well-configured.

Try to check your confs again... on Security.

 

Answer 9

Thanks.  I added the NT AUTHORITY\NETWORK SERVICE user to the database and gave it all the permissions I could see through SQL Management studio.  Did the grant login recommended etc.  I thought I tried all possible.  I am guessing that you are correct.  There is no sensitive information on this server, so I haven't gotten into tightening security.  The personal web page starter kit, with some changes, runs fine on the same server.  I guess I could go XML with the Small Business Starter kit, but that would be whimping out I think!  Anything more specific would be helpfull.  Thanks again.

Answer 10

hi Akiren

i was facing the same problem but then after changing my connection string to following it was done correctly.

<remove name="LocalSqlServer" />

<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=Track;user id=sa;password=agile;" providerName="System.Data.SqlClient"/>

Please check your LocalSqlServer string again, provide userid, password for connecting sql server.

 

Answer 11

tommy123456 's method has solve my the same problem

Answer 12

I got almost frustrated last night, trying to get things working (setting up permisssions in sqlexpress etc.). Finally this morning, success!

 

I must have some type of error writing connection strings:

 

 

Now it works with both:

 

either

 

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

 

or

 

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

 

Allelujah!

Answer 13

G Jovan's second connection string (sql) just worked fine for me. But I wonder why first one (windows) is not working. I am still getting Login failed for user 'NT AUTHORITY\NETWORK SERVICE' no matter what you already suggested. I will appreciate very much  if  one can post another advice for the solution.

Answer 14

Hi

My problem has been solved by the following method:

Add permissions to  'NT AUTHORITY\NETWORK SERVICE' in the security folder of your SQL 2k5 express.

Answer 15

Ok, I tried everything but what I kept missing was that the Initial Catalog is case-sensitive. I had moved the site to a customer location and they had different casing (same name) for the database. It solved my issue after hours and hours of trying things. Hope it helps someone.

Answer 16

Thank you, thank you, thank you, tommy123456.  I've been beating on this all day and your solution works like a charm!  After running :

 

sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

 

in query analyzer against both master and my database, and turning on Role Membership for all ASP roles plus db_datareader and db_datawriter, I finally got past that annoyning login failed error message.

 

Thank you, thank you, thank you!

Answer 17

I'm having the same problem with SQL Server Analysis Server. sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' does not apply in this case. I have a web service that connects programmatically to it:
conn = new SqlConnection(
           Settings.Default.Connection);
            conn.Open();

The client is in a separate machine. If anyone can think of a way for it to work with SQL Server Analysis Server please reply. Thank you,

GF

Answer 18

It has solved my problem in SQL Server 2000
Hope it helps to you

 

Please follow below steps :
Please confirm the login [NT AUTHORITY/NETWORK SERVICE] is present under Security/Logins Node at SQL Server Instance
Right Click on the user[NT AUTHORITY/NETWORK SERVICE] - Select Properties - Go to Database Access Tab - Check the desired database -
Assign public - db_owner roles

 

Click Ok,

Refresh the database and Finally Try the Application once again.

Hope it helps.

Answer 19

Hi,

My problem is wierd.

 

I am trying to assign a user in my code to access my database but it keep defaulting to logging in as NT AUTHORITY/NETWORK SERVICE user.

 

I have no idea why.

1) This same user that am am assigning to another database works just fine.

2) I have made sure both database contains same user access.

 

My connection strings are as below. But I am being defaulted to NT authority when logging in to OpsTradingDB, Please advise.

 

Thanks in advance.

 

<connectionStrings>

<add name="OpsTradingConnectionString" connectionString="Server=DEVPC;Database=OperationsTradingDB;User ID=PETREDECS\\AppAcc;Password=Pa$$w0rd;Trusted_Connection=True"/>

<add name="EmployeeConnectionString" connectionString="Server=DEVPC;Database=EmployeeDetails;User ID=PETREDECS\\AppAcc;Password=Pa$$w0rd;Trusted_Connection=True"/>

</connectionStrings>

Answer 20

submit this line into query analyzer on master or your database to grant access:

sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

Thanks Tommy123456, that did the trick for me.  I also had to open that user in SQL and point them towards the appropriate database for my particular case. 

 

Answer 21

Well I followed the instructions above; I am running sql server 2005 enterprise; below is my conncetion string:

 

<add key="DatabaseProposals1" value="Provider=SQLOLEDB;Data Source=RGV-BOS-BL2;Database=Proposals;Integrated Security=SSPI;"/>
  

 

Answer 22

i also face the same problem, Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. i like to try with tommy's idea but how can i change the grant access for my aspnetdb database. i could not find it. i only have database master, model & tempdb. why my aspnetdb.mdf is not detected?.  i tested the connection at web admin tool. the connection is success.

 

another wierd thing is i can view my page (client.aspx) using localhost but when i use IP address http://231.222.170.244/client.aspx, i got error login failed for user 'NT AUTHORITY\NETWORK SERVICE'. please help me to solve this. tQ 

 

Answer 23

Hi,

I got help from someone who knows this stuff inside and out.  They pointed me to these two white papers.  Hope that it helps.

 

The problem you are experiencing is an issue with how ASP.NET handles impersonation and access to remote resources.  One of the best solutions for this is to run your web service in the context of a domain user that has access to all the resources that are needed, and do not impersonate the client.  This means your domain user will need to have access to both the remote machine where it is creating directories and have access to the database to update records.

 

This issue in ASP.NET is well discuss in these two documents, from identifying the issue of identity management to designing solutions based around the infrastructure in place:

-          http://www.microsoft.com/technet/security/guidance/identitymanagement/idmanage/P3ASPD.mspx?mfr=true

-          http://msdn2.microsoft.com/en-us/library/aa291350.aspx

Answer 24

if,  you acess it in system like intranet, then delete Intigrated Security from connection string or set it off and specify user id, password of sql-server authentication

 

Answer 25

 

Thank you, thank you, thank you, tommy123456 and VegasDave.  The combination of your posts worked for me.

Answer 26

Your connection string is wrong. Meaning you wanted to use SQL authentication but for some reason it is using Integrated Security  ....

Answer 27

VegasDave wrote:

Thank you, thank you, thank you, tommy123456.  I've been beating on this all day and your solution works like a charm!  After running :   sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'   in query analyzer against both master and my database, and turning on Role Membership for all ASP roles plus db_datareader and db_datawriter, I finally got past that annoyning login failed error message.   pls tell me  whatever  u did in  detailed procedure bcoz i am very  new  to  sql server  2000.

Answer 28

pls tell me this thing in detail procedure bcoz i am new to sql server 2000.

Answer 29

When you have created virtual directory of your application created in .Net2.0 and application is not running.
Even everything is fine in web.config, still it is giving error in web.config file.

 

Error :- The current identity (NT AUTHORITY\NETWORK SERVICE) does not have write access to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files'.

 

Solution is
goto command prompt then navigate to directory

 

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

 

Now run this command

 

aspnet_regiis -ga "NT AUTHORITY\NETWORK SERVICE"

 

Everything will wok fine.

Answer 30

 

I think this is not a solution , because I would need to give different right of database accessing to the different users who connect to the web site.

I  retrieve the user straight away from Windows authentication , so i need to give it an access to the database with his user and not 'NT AUTHORITY\NETWORK SERVICE' .

Could someone help me?

Answer 31

Hi Closh,

As per my experience ASP.Net is not able to get windows authentication till you have specified Impersonate. Even if we are maintaining Windows authentication at IIS level, I am not able to get Windows logon username when authentication is happening with Sql Server.

If I really need to authenticate user under Windows in asp.net, Enable Impersonate as:

<identity impersonate="true"
          userName="domain\user" 
          password="password" />

Now ASPNet will run under specified username instead of  default ASPNET User Account (DomainName\MachineName).

Try to understand 

ASP.NET Process Identity in http://msdn.microsoft.com/en-us/library/aa291350.aspx

Thank You.

Answer 32

thanks thanks thanks Sidheshwar it worked for me.

RS

Answer 33

Hi Cinni,

Would you be more specific? my email is "Akshay Patel" [e-mail]

Answer 34

This worked for me! Thanks! Only difference, I need to set the server role to 'sysadmin' for NT AUTHORITY/NETWORK SERVICE

---

Developer

Answer 35

I just have the same problem.
And wants to use this solution, but DB Admin not allowed.

Just make sure that Persist Security Info=True, when user & password need to be set.
This works.

It is better & secure way

 

Answer 36

write below line in new query window


sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

run it under your database and also under master


On your Particular Database follow the below step

go to security -> Users -> NT AUTHORITY\NETWORK SERVICE ->Right Click -> Properties -> Check datareader, datawriter

Answer 37

.
I give up... If this post does not result in a solution for me, I am dropping Microsoft SQLServer
I have tried so many combinations, I don't know what is what anymore.
My site connection worked great under WINDOWS XP Pro, I can't get it to work under WINDOWS 7 Pro
Fails at the Conn.Open statement.

My small contribution: (This maybe or may not be related, but a good thing to know)
If anyone has had the nagging Visual Studio Debugger Timing out problem, here is what I ran into:
If the DefaultAppPool in IIS7 is set to:
    - Managed Pipeline Mode=Integrated  (if change that to Classic ...debugger starts timing out again)
    - Identity=NetworkService  (if change that to LocalSystem ...debugger starts timing out again)
the Visual Studio Debugger no longer times out after 1+ minute.

THE MAIN ISSUE:
I have WINDOWS 7 Pro
          IIS 7 and IIS7 Manager
          Visual Studio 2008
          SQLServer EXPRESS 2005
          SQLServer Express Management Studio.
          .NET Framework 2.xxx (3.5)

Connecting to database via ASP.NET site fails:
Many of the  options stated in above posts are not showing up on the WINDOWS 7 Pro, IIS 7, or the SQLServer Management Studio. I have to interpret them into what I see on WIN 7...
I have created the NetworkService User in SQLServer and set Full COntrol on the rootFolder of the Website(Folder where the project resides... and it shows under IIS)
My DB.mdf resides at: (I believe this is the 64-Bit side)
     C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf

WINDOWS 7 Pro base:
The MyDB.mdf and MyDB.ldf were copied from the previous XP Pro installation to this New WINDOWS 7 Pro setup.
SQLServer Explorer in Visual Studio and SQLServer 2005 Management Studio BOTH connect and allow management of the database. no problems, NO MATTER WHAT CHANGES I MAKE TO IIS.

If you say adjust this role to this and that... I will not know what it means.
I need a step by step, go here, set to that type help. (Thank you)
Will anyone please provide a simple list of all the settings that need to be addressed to establish a successful database connection/login via the website. The website functions smoothly in all respects except for the connection to the database.

IIS 7:  DefaultAppPool, Advanced settings etc...
  I choose not to list all options to avoid confusion as to which should be addressed and which need not.
  Provide complete list and corresponding settings, please. (Thank you)

SQLServer 2005 Management Studio...
If you say adjust this role to this and that... I will not know what it means.
I need a step by step, go here, set to that type help. (Thank you)

Connection String tag=value list...
I wanted to connect without using a user in the database(WINDOWS AUTHENTICATION I assumed) but I am starting to think it is not possible.
What does the NetworkService connect to? SQLServer? ALL databases?
Do I have to include My Windows Login account as a user in SQL?

2 of the 50 scenarios I have tried:
__________________________________________________________________
AppPool Identity: NetworkService
Managed Pipeline Mode: Classic (causes debugger to time out after 1+ minute!)
<add connectionString="Server=MYCOMPUTER\SQLExpress;Database=MyDB.mdf;Integrated Security=true" name="MYWEBSITE.My.MySettings.ConnString" providerName=".NET Framework Data Provider for SQL Server" />
Error:  Cannot open database "MyDB.mdf" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
__________________________________________________________________
AppPool Identity: NetworkService
Managed Pipeline Mode: Integrated (Debugger no longer times out after 1+ minute, stays on till stopped)
<add connectionString="Server=MYCOMPUTER\SQLExpress;Database=MyDB.mdf;Integrated Security=false" name="MYWEBSITE.My.MySettings.ConnString" providerName=".NET Framework Data Provider for SQL Server" />
Error:  Login failed for user ''. The user is not associated with a trusted SQL Server connection.

__________________________________________________________________
AppPool Identity: NetworkService
Managed Pipeline Mode: Integrated (Debugger no longer times out after 1+ minute, stays on till stopped)
<add connectionString="Server=MYCOMPUTER\SQLExpress;Database=MyDB.mdf;Integrated Security=true" name="MYWEBSITE.My.MySettings.ConnString" providerName="System.Data.SQLClient" />
Error:  Cannot open database "MyDB.mdf" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
__________________________________________________________________
AppPool Identity: NetworkService
Managed Pipeline Mode: Integrated (Debugger no longer times out after 1+ minute, stays on till stopped)
<add connectionString="Server=MYCOMPUTER\SQLExpress;Database=MyDB.mdf;Integrated Security=false" name="MYWEBSITE.My.MySettings.ConnString" providerName="System.Data.SQLClient" />
Error:  Login failed for user ''. The user is not associated with a trusted SQL Server connection.
__________________________________________________________________

Thank you anyone in advance.
.

---

SPA

Answer 38

write below line in new query window


sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

run it under your database and also under master


On your MyDB in SQL Server Management Studio under Security tab do the following

if a user like NT AUTHORITY\NETWORK SERVICE
is not present

if present db_datareader and db_datawriter must be checked

security -> Users -> Right Click->  New User

Write User Name and Login Name both as NT AUTHORITY\NETWORK SERVICE

Dont forget to check db_datareader and db_datawriter both in Owned Schemas and Database role membership windows ->OK


C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf
 
Right Click the folder Data from above path

Right Click -> Properties -> Security -> Edit ->Check Full Control for all Users

Users like SYSTEM etc