SQLServerAgent login with xp

16 Sep 2005 8:55 AM

marcmc

Hi,

I have a SQL Job step that uses xp_cmdshell to run a 3rd party application
which in turn executes jobs on a remote server. To achieve this, I have had
to set the job as a TSQL Job type(i need to validate a parameter before
executing remaining SQL). Due to this the only way i can get the process to
work is if I switch the SQLServerAgent login from its current setting to
DOMAIN\Administrator.

In the plainest english, I would love to know:

What are the pitfalls and security implications of using this login with the
Job step?
What are my alternatives? I have heard of proxy accounts etc...but I am not
dba
and finally;
When I set Job step to run as Administrator the SQLServerAgent login seems
to override anyway. Is this normal?

16 Sep 2005 9:54 AM

John Bell

Hi

Having anything that is over privileged is not a good idea.

From BOL:
"By default, only members of the sysadmin fixed server role can execute this
extended stored procedure. You may, however, grant other users permission to
execute this stored procedure."

"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail."

HTH

John

Show quote

"marcmc" <mar***@discussions.microsoft.com> wrote in message
news:6BA0D7BC-37D3-4850-81B5-D2C1FB8C3C83@microsoft.com...
> Hi,
>
> I have a SQL Job step that uses xp_cmdshell to run a 3rd party application
> which in turn executes jobs on a remote server. To achieve this, I have
> had
> to set the job as a TSQL Job type(i need to validate a parameter before
> executing remaining SQL). Due to this the only way i can get the process
> to
> work is if I switch the SQLServerAgent login from its current setting to
> DOMAIN\Administrator.
>
> In the plainest english, I would love to know:
>
> What are the pitfalls and security implications of using this login with
> the
> Job step?
> What are my alternatives? I have heard of proxy accounts etc...but I am
> not
> dba
> and finally;
> When I set Job step to run as Administrator the SQLServerAgent login seems
> to override anyway. Is this normal?
>

SQLServerAgent login with xp_cmdShell