MS SQL Server security - SQL Server Agent Proxy Account / XP

Author

SQL Server Agent Proxy Account / XP_CMDSHELL

John - PDX

2005-05-26, 8:23 pm

Thanks in advance.

I am trying to run the xp_cmdshell and am calling it with osql from a batch
file for a user. I have set the sql server agent proxy account with an
account that is both a domain admin & sql sysadmin (in our dev lab only) and
still I get EXECUTE permission denied.

I can make the user a sysadmin for test purposes only and all works fine. I
can also go directly to the xp_cmdshell permissions, give the user execute
permissions and all works OK there as well.

In reasearching this issue I should be able to set the sql server agent
proxy account and then my user should be able to run the batch file that runs
the osql that calls the sp that runs the xp_cmdshell - correct?
--
Thanks,
John

Dan Guzman

2005-05-27, 8:23 pm

> In reasearching this issue I should be able to set the sql server agent

> proxy account and then my user should be able to run the batch file that
> runs
> the osql that calls the sp that runs the xp_cmdshell - correct?

The proxy account is unrelated to SQL Server object permissions. A user
must have xp_cmdshell execute permissions when xp_cmdshell executed
directly. The proxy account is used as the OS security context when
xp_cmdshell is invoked by non-sysadmin role members.

The technique below shows how to implement an unbroken ownership chain to
xp_cmdshell so that you can provide xp_cmdshell functionality without direct
execute permissions on xp_cmdshell.

1) Create a dbo-owned user proc in an sa-owned database that executes
xp_cmdshell. Code the proc in such a way that only the intended command can
be executed and unintended code can't be injected.

2) Enable the 'db chaining' option (a.k.a. cross-database chaining) in your
user database. Note that this should be done when only sysadmin role
members can create dbo-owned objects in that database.

Also, there may be better ways to accomplish the task than xp_cmdshell. You
might provide details on what you are trying to do in case someone has a
better idea.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John - PDX" <JohnPDX@discussions.microsoft.com> wrote in message
news:41966260-1C3D-4208-B4B7- 573CAA519E35@microso
ft.com...
> Thanks in advance.
>
> I am trying to run the xp_cmdshell and am calling it with osql from a
> batch
> file for a user. I have set the sql server agent proxy account with an
> account that is both a domain admin & sql sysadmin (in our dev lab only)
> and
> still I get EXECUTE permission denied.
>
> I can make the user a sysadmin for test purposes only and all works fine.
> I
> can also go directly to the xp_cmdshell permissions, give the user execute
> permissions and all works OK there as well.
>
> In reasearching this issue I should be able to set the sql server agent
> proxy account and then my user should be able to run the batch file that
> runs
> the osql that calls the sp that runs the xp_cmdshell - correct?
> --
> Thanks,
> John

Sponsored Links