 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / February 2008Linked Server access fails when executed as a job
I have two SQL Server 2005 machines each running a default instance at SP2. Both servers are running Windows 2003 SP2. On the first server, S1, I have created a linked server definition to the second server, S2. The defintion has its security config set up as connections will be made with the login's current security context. I log onto S1 with a Windows auth logon and then I am able to query tables sitting on the second server, S2. However when I put this same exact script into a job and make the job owner the same Windows auth logon, the job fails with: Executed as user: WindowsDomain\user. Could not connect to linked server 'S2' (OLE DB Provider 'SQLNCLI'). Enable delegation or use a remote SQL Server login for the current user. [SQLSTATE 42000] (Error 7413). Why does this not work when executed as a job? When I give the job ownership of SA it runs fine. Doesn't the job run under the security context of its owner? Any help on this would be greatly appreciated. Thanx, jeff jeff, Do these provide any clues? http://msdn2.microsoft.com/en-us/library/ms175537.aspx http://msdn2.microsoft.com/en-us/library/ms189580.aspx I remember with SQL Server 2000 that I had trouble going through a linked server with a job that ran as a specific user. This (IF I remember correctly) is because the job actually starts as the server account and then is switched using SETUSER like functions (found by using Profiler) which make the security context invalid for a linked server. Does this happen with 2005? I do not know because I have not tried it. Another possibility is to use a CREDENTIAL and a SQL AGENT PROXY to get a new security context to run the job step in question. If it is a TSQL step this will not work, but you can change the step to Operating System and run OSQL or SQLCMD with the query and it will run as the underlying account set by the proxy. FWIW, RLF >I have two SQL Server 2005 machines each running a default instance at > SP2. Both servers are running Windows 2003 SP2. On the first server, [quoted text clipped - 18 lines] > Thanx, > jeff |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.