September 5, 2012 at 3:21 pm
Sql agent jobs fail via schedule when using linked server. These same Jobs run fine when kicked off manually via sql agent job?
I recently got the whole team including myself off the sa account and we’re now using AD/win auth.
Problem is that all agent jobs were created with sa as the owner, so now as I change them back to the appropriate AD owner, I am getting failures unless I give the user sysadmin server access :w00t: on the host box only.
Could it be the sql agent account doesn’t have the appropriate AD access? It does have sysadmin server role in SQL.
Linked server settings: Be made using the login’s current security context
User server role: processAdmin
User Db roles: dataReader, dataWriter, ddlAdmin
Auth_Schem: Kerberos
Host Box: SQL 2005 Ent.
Target Box: SQL 2008 R2 Ent.
Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed
Any ideas? Thanks
September 6, 2012 at 2:36 am
I tried once to figure this one out, but I didn't make it all the way. But if I remember correctly it had something to do with Kerberos and double hop. Something called setspn could possibly resolve it.
September 6, 2012 at 6:40 am
SQL Surfer '66 (9/6/2012)
But if I remember correctly it had something to do with Kerberos and double hop. Something called setspn could possibly resolve it.
We do have the issue of KERBEROS changing to NTLM every so often, so MAYBE the host box was linking to the other server and that particular spid was using NTLM on the target box. I didn't check the auth scheme on the target instance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply