Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a service running on one server (A) which traditionally runs under the local system account. Now SQL server has moved from server A to a new server B.

I tried adding the computer account of server a [domain\servera$] to SQL server on server B and gave it all the rights it could possibly want (sa) but the service still cannot connect.

The error I find in the service log for that moment is the following

enbase 
ODBC database error:
Connect()
szSqlState = 28000
pfNativeError = 18456
szErrorMsg = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
pcbErrorMsg = 100
LoginId = !!UnknownUser!!
ODBCRowNumber = 0
SSrvrLine = 0
SSrvrColumn = 0
SSrvrMsgState = 0
SSrvrSeverity = 0
SSrvrProcname = 
SSrvrSrvname = 

I don't know why the service thinks that it is logging on as ANONYMOUS LOGON.

Any ideas?

Update: I wrote a test service running under localsystem that causes this message in SQL profiler:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."

Update: I think now that this is a Kerberos issue. Kerberos doesn't work because the domain account SQL is running under cannot register its Service Principal Names (setspn -L shows as much) and hence Kerberos cannot be used and NTLM is used and NTLM doesn't work for domain\computer$ for some reason.

Finally, ERRORLOG shows that SQL server logs an error about not being able to register the SPN for the SQL Server service. This confirms my theory, I think.

Update: I think the solution is to grant the domain account SQL Server is running under is trusted for delegation in AD so that it can register its SPN when SQL Server starts.

share|improve this question
    
Double Hop preventing forwarding credentials? –  vcsjones Jan 31 '12 at 15:59
    
I doubt it. The two servers are in the same network, I think. I'll ask. –  Andrew J. Brehm Jan 31 '12 at 16:07
    
That's not what double hop means. –  adaptr Jan 31 '12 at 16:11
    
is there an odbc configuration for this service? –  tony roth Jan 31 '12 at 16:25
1  
What does double hop mean here? –  Andrew J. Brehm Jan 31 '12 at 17:44

2 Answers 2

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

You're not using the machine account to connect...

LoginId = !!UnknownUser!!

You're not providing the right credentials.

You do know that what is known externally as DOMAIN\MACHINE$ is internally the NT Authority\Network Service account, I hope :)

share|improve this answer
    
I thought it was also localsystem? –  Andrew J. Brehm Jan 31 '12 at 17:42
    
Network service account is what you'll need to use if you are ACL'ing with the Computer Account$. –  Tatas Jan 31 '12 at 18:06
    
blogs.msdn.com/b/dataaccesstechnologies/archive/2010/01/29/… claims the opposite. But regardless, I cannot use the network service account. –  Andrew J. Brehm Jan 31 '12 at 18:07
    
Local System = admin rights on the machine, but no network access. Local Service = low-level permissions and no network access. Network Service = low-level permisisons + remote network access. –  adaptr Feb 1 '12 at 12:52
1  
Specifically, msdn.microsoft.com/en-us/library/ms143504.aspx says that the local service account has the same privileges as members of the users group while local system has "has extensive privileges on the local system and acts as the computer on the network". I don't know where you found your definitions of the accounts, but it is wrong. Local system does have network access. –  Andrew J. Brehm Feb 1 '12 at 15:05
up vote 0 down vote accepted

I believe this problem was solved by creating an SPN for the account that SQL server runs under. If the SPN exists, the client can authenticate with Kerberos and logs in as the client computer's domain account domain\clientcomputer$ which can be given the appropriate access to the SQL server.

share|improve this answer
    
This document microsoft.com/en-us/download/details.aspx?id=4754 is very helpful for troubleshooting such issues, as is this article blogs.msdn.com/b/dataaccesstechnologies/archive/2010/01/29/… –  Rory Sep 8 '13 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.