SQL Server Performance

XP_CMDSHELL NOT working

Discussion in 'General DBA Questions' started by contactjimmys, May 24, 2007.

  1. contactjimmys New Member

    Dear all,

    The command Exec Master..XP_CmdShell 'PStat' is not returning values for sishell sql user.For sa it is working fine
    ALL of the options for XP_CmdShell is not working for sishell sql user.What is the minimum permission required for the sql user to run the command.

    The sql services and proxy account are running on same windows account


    Thanks

    DBA
    SHAMS
  2. MohammedU New Member

    Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

    Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

    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.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. FrankKalis Moderator

    Anyone facing problems posting xp_cmdshell here?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. Adriaan New Member

    I'll give it a try - xp_cmdshell ...
  5. Adriaan New Member

    Now where did that forum software bug disappear to?
  6. FrankKalis Moderator

  7. satya Moderator

    If you have any security policies within the network then check and ensure that shell user has relevant access (read) on Windows/system32 folder.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. contactjimmys New Member

    The sql service is running under a domain account and the user tsg is a member of administrative group in localmachine.also this is the proxy accout set on the server .xp_cmdshell is failing with execute permission denied on object.
    Iam able to execute the xp_cmdshell using sa account successfully

    Please suggest
    Thank,

    DBA
    SHAMS
  9. Adriaan New Member

    quote:ALL of the options for XP_CmdShell is not working for sishell sql user.
    You need to grant execute permission on xp_cmdshell for that individual login. Note that the extended stored procedure is located in the master database.

    On top of that, the domain account for the SQL service needs to have sufficient permission to execute the command that you're passing - for instance, if you're issuing a DIR command on a drive then the domain account must have at least list permissions on for the drive.

    So the domain account should have only limited permissions in the OS domain, to prevent abuse of the backdoor that you put wide open by using xp_cmdshell.
  10. contactjimmys New Member


    I have given explicit execute permission to the sql user 'sishell' but Iam not able to use the commnad.I am using the below commands with xp_cmdshell.

    Exec Master..XP_CmdShell 'PStat'
    Exec Master..XP_CmdShell 'DumpEL -l Application'
    Exec Master..XP_CmdShell 'DumpEL -l System'
    Exec Master..XP_CmdShell 'DumpEL -l Security'
    Exec Master..XP_CmdShell 'Tlist' etc .

    For some servers this options are working

    Please suggest what permission is to be granted to the user so that this can be rectified


    Thanks

    DBA
    SHAMS
  11. satya Moderator

    SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"

    http://support.microsoft.com/kb/833559&http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  12. Adriaan New Member

    From Satya's first link -
    quote:Do not allow the users who are not members of the sysadmin fixed server role to run the xp_cmdshell extended stored procedure. However, if you have to allow the non-sysadmin SQL Server users to run the xp_cmdshell extended stored procedure, the Windows service accounts that are configured for SQL Server must be included as members of the Administrators group on the computer that is running SQL Server.

    In SQL Server 2000, if you have to allow the non-sysadmin SQL Server users to run the xp_cmdshell extended stored procedure, you must configure the proxy account. When SQL Server executes jobs or commands for users who are not members of the sysadmin fixed server role, the SQL Server Agent and the xp_cmdshell extended stored procedure use the proxy account. The Windows security credentials for the proxy account are stored in the Local Security Authority (LSA) Secrets database, and only the Windows Administrators can access the information. Therefore, if the domain user account is not a member of the local administrator group, the user cannot store or retrieve the Windows security credentials to log on as the proxy account. Therefore, the xp_cmdshell extended stored procedure fails, and the user receives the error message that is described in the "Symptoms" section of this article.
    The article also says that local logins are always able to execute xp_cmdshell on a local instance of SQL Server. That would explain why it is working on local instances, but not on remote instances.

Share This Page