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