Login failed for user 'NT AUTHORITY\NETWORK SERVICE' errors in log

Question

Post reply

Login failed for user 'NT AUTHORITY\NETWORK SERVICE' errors in log

Robb.Hagan

Right there with Babe

Points: 772
More actions
June 27, 2008 at 7:52 am

#209001

After upgrading our database from SQL 2K to SQL 2005 SP2, I am seeing the SQL Server logs littered with this error:
Logon Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: XX.X.X.XX]
Logon Error: 18456, Severity: 14, State: 16.
NT AUTHORITY\NETWORK SERVICE is a user in the datase and has permissions to its assigned default database - Master.
Our web application does not use this user. Each user has their own SQL login using Windows Auth.
Server is running Windows Server 2003 SP2.
Users are also experiencing intermittent connection issues while using the application:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "MyDatabase" requested by the login. The login failed.
Refreshing the browser and trying again resolves the issue.
The bulk of the web application is classic ASP using DTC's with some ASP.Net pages.
What is not set up/configured correctly?
Don't know if the two are related some how?
Thanks.
Robb

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

MannySingh SSChampion Points: 12465 More actions · Answer 1

did you move the Logins as well and which method did you use.?

Try this goto the Database (use Database)

then sp_change_users_login 'Report'

if anything shows up in the RESULTS, they are Orphaned Users....

sp_change_users_login 'update_one','loginname','loginname'

this will update your Login.

Or Change the login password and Try agian, this should solve your Issue.

Maninder
www.dbanation.com

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 2

I manually redefined all of the users at the server level after attaching the production database.

The stored proc returned no results.

Which issue were you addressing? The intermittent user connectivity issue? Or the NT Authority issue?

Thanks.

Robb

MannySingh SSChampion Points: 12465 More actions · Answer 3

see if this is helpful:

http://groups.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Maninder
www.dbanation.com

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 4

The thread you suggest does not read like my problem.

I am not sure why I would need to touch each PC since my problem seems server related.

Robb

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 5

I used SQL Server Profiler to trace the failed logins but have only caught the failure of NT AUTHORITY\NETWORK SERVICE attempting to login into database master. This user has master as the default db and has been granted access to the database. The application is IIS.

Why would SQL Server fail/reject this login?

Thanks.

Robb

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 6

I figured out this problem.

I granted NT AUTHORITY\NETWORK SERVICE to our production database and both problems have gone away.

I am not sure I understand why this was necessary for SQL 2005 and not 2000?

I am I missing something?

Thanks.

Robb

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 7

The ASP.Net pages - the context being used is not always what you expect. Especially when making a hop between servers. If you're using NTLM on the site, switch to Basic Authenication for a quick test.

Greg E

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 8

Greg-

Thanks for the response.

We have a fairly straight forward setup for our Intranet - one server for web and database; user logins via Windows authentication; running IIS 6, classis ASP and ASP.Net, Windows 2k3 Server Standard, and now SQL Server 2005 Standard.

I am not familiar with NTLM so I am guessing we don't use it.

Robb

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 9

rhagan (7/2/2008)
Greg-
Thanks for the response.
We have a fairly straight forward setup for our Intranet - one server for web and database; user logins via Windows authentication; running IIS 6, classis ASP and ASP.Net, Windows 2k3 Server Standard, and now SQL Server 2005 Standard.
I am not familiar with NTLM so I am guessing we don't use it.
Robb

Robb -

Is it anonymous using forms and they authenticate with their windows credentials?

Or Basic prompting them for their credentials?

Basic should carry the context of the user (do the hop).

We generally use Kerberos when possible on our intranet to make the hops. We need to have the User context to enforce our cube security.

Greg E

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 10

Greg-

The site uses Integrated Windows Authentication and the database uses Windows Authentication.

Robb

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 11

Robb -

These links should help you out. You could also use Basic Authentication, although I'd recommend an SSL certificate.

If you're happy having generic access under the Network Service ID, leave it as is. Although I would highly recommend to at least read enough to understand why it broke and how to fix it.

Kerberos is sometimes a bit tricky to setup, and takes a Domain Admin to set up the SPN's and allow delegation on the needed accounts.

Greg E

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/523ae943-5e6a-4200-9103-9808baa00157.mspx?mfr=true

http://support.microsoft.com/?kbid=319723

http://support.microsoft.com/kb/326985

http://support.microsoft.com/kb/244474

http://support.microsoft.com/kb/917409

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 12

Greg-

I think I am still missing something here.

Technically, nothing really broke as in dead in the water. The web application still functioned for the users. They just experienced intermittent "Cannot open database "ProductionDB" requested by the login. The login failed." errors while using the application (not just trying to access it). The more they used it, the more likely they were to run in to the problem.

Example: User would navigate to a web page to run a report (hitting several other pages (menus) in the process that require database interaction because we use the users identity for security within the application as well). User would get the error running the report. (We have a custom error page that logs the error.) User step back from the error page and would try it again and it would run.

The NT AUTHORITY\NETWORK SERVICE login error in the SQL Server logs just confused me because I did not know where it was coming from and what it was trying to do. I still don't know.

The NT AUTHORITY\NETWORK SERVICE login failure happened far more frequently than the user login failure.

All in all, the only item that changed in the database software and we did not have these issues with SQL 2000.

Robb

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 13

What context is the Application Pool running under?

Sounds like Network Service.

If NTLM fails, this is the account that likely will be tried.

Greg E

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 14

Greg-

The website is assigned to the DefaultAppPool which does run under Network Service. Ding!

I have never messed with this in the past or changed it.

So why does SQL Server 2005 have gas with this and SQL 2000 did not? NT AUTHORITY\NETWORK SERVICE was not a user in the SQL 2000 database that I can tell?

Thanks.

Robb