Running Transact-SQL script from SQL server agent with a different account

Hi,
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql