October 11, 2005 at 9:27 am
Hello,
I was wandering if someone could help with this. I am using a linked server in SQL2000 for a very simple update. i.e. I copy data from the linked server into the target server db within a stored procedure I previously created. This works fine from QA but not when run by the SQL Agent in a scheduled task. I have tried changing the security settings on the job. namely Run As parameter to a common name and password in both servers and running as 'sa' but nothing works. I believe this is a standard error but wandered if anyone had a way around it.
sp =
delete from lydb..temp_matteru
insert into lydb..temp_matteru
select 'S' + substring(mmatter,8,4),'S' + mclient,mname,mdesc1,mdesc2,mdesc3,mbillaty,msupaty,morgaty,mopendt,mclosedt,substring(mdept,1,6),mprac,mloc,mstatus,mmoddate,mname
from severname.db_db.dbo.matter
where mmoddate > getdate() -7
ERROR
Job 'update lk temp tables' : Step 1, 'exec sp' : Began Executing 2005-10-11 12:04:17
Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000]
when run from QA this works fine. But when scheduled the SQL authentication fails. I think there must be a way around this but i'm just not sure what it is. any help much appreciated. thanks.
October 11, 2005 at 12:29 pm
found an article about the error:
October 11, 2005 at 3:58 pm
Thanks for the reply Jo, I have read the attached article and have tried almost everything I can think of on the security settings and job owner. I have made the job owner sa, created a domain account with corresponding sysadmin access to both SQL servers and unfortunately to no avail. I tried the OSQL route a few months ago and from memory got the same problem.
Temporarily I have worked around the problem by using a DTS job to do the updates which proves in a way that this can be done. As this is uses OLEDB connections and relevant transformations from server1.db1 to server2.db2 and runs quite happily through the scheduler. The Agent service is started with a local system account on both servers btw.
I will continue to try other things and I'm sure I'll get it eventually but if you or anyone else has any other ideas they'd be gratefully received.
thanks again
Mike
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply