scheduling jobs that query linked servers

I have some SQL Servers that are linked and have created
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>