January 13, 2004 at 12:44 pm
I know about the security risks of running xp_cmdshell by non-sysadmins and I am already taking those issues into account. I have a question about a problem I am having letting users run this extended stored procedure.
I have created an Active Directory group and added it to our SQL Server as a login and made it a user on the master database. The only thing this user has authority to do is execute xp_cmdshell. I made one of our users a member of this AD group and had him execute a stored procedure that executes the xp_cmdshell procedure. (We are sure he has execute permissions on the stored procedure that executes xp_cmdshell.)
When he runs the stored procedure he gets an error that access is denied.
Next I removed him from the AD group and added a login for him, with Windows authentication, and added him as a user to the master database, then I granted execute permissions on xp_cmdshell. When he runs the stored procedure now, it executes the xp_cmdshell procedure properly.
Is there some rule about how permissions can be granted to xp_cmdshell? The only difference between my two scenarios is in one case he is executing with a connection directly based on his personal AD account and in the other case, the permissions are set via an AD group.
Thanks for any help.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 13, 2004 at 1:31 pm
There shouldn't be a difference. I'd have to do some testing and see. Will try a bit later.
January 14, 2004 at 1:43 am
Have you setup a Proxy Account?
from the BOL:
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.
Good Luck
Dave
January 14, 2004 at 2:53 pm
Update: It started working. I did make changes with assigning rights to individuals and groups and back again just for testing but it never worked right yesterday. I put all the settings back the way they were before I left yesterday.
All of a sudden it started working today.
Also, I had already taken care of the proxy account issue. I had this working before this new problem popped up.
Thanks for all input.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply