Login failed for user ''NT AUTHORITY\NETWORK SERVICE''

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.

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.

|||

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.

|||

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

|||

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

|||

Hi,

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

I think it will fix it!

[]′s Breno

|||

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

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

Hi Juan,

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

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

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

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.

|||

tommy123456 's method has solve my the same problem

|||

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!

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

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.