Re: Linked Server access fails when executed as a job
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Fri, 15 Feb 2008 15:22:48 -0500
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
<staxcarrington@xxxxxxxxxxx> wrote in message
news:501fdf8b-2d6d-4304-80bb-6b97feb8dfb7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Linked Server access fails when executed as a job
- From: staxcarrington
- Linked Server access fails when executed as a job
- Prev by Date: Re: permissions not working
- Next by Date: Re: Integrated Security in the DMZ
- Previous by thread: Linked Server access fails when executed as a job
- Next by thread: permissions not working
- Index(es):
Relevant Pages
|
|
