Post reply

xp_cmdshell returns null

  • August 25, 2004 at 7:14 am

    #108998

    Until recently, non-admin users were able to use xp_cmdshell (with a proxy account set up) to start DTS packages on our client's SQL 2000 server.

    Unfortunatly, they have a habit of tweaking the server settings and one tweak seems to have broken xp_cmdshell. When running any command (I tried dir through Query Analyser) as a user who is not sysadmin it returns a return value of 0 but an output of NULL and doesn't actually run the command.

    Any idea what could have been changed on the server? I haven't been able to replicate it here, thus far.

  • August 25, 2004 at 9:28 am

    #520548

    Typically in order to do this you have to set the SQL Server Agent proxy account. Verify this account still is valid (username/password). You can find out who this is by:

    xp_sqlagent_proxy_account 'GET'

    K. Brian Kelley
    @kbriankelley

  • August 25, 2004 at 9:39 am

    #520552

    When I run that, I get the expected response. The proxy user is a member of the Power Users group, and can run the commands in a CMD window if I put them in manually.

  • August 25, 2004 at 10:52 am

    #520574

    See if you can get a list of what they changed. Verify the account in question has the right to Log on as a batch job. This often gets broken when a group policy is applied. You can check the local setting as well as the effective setting by viewing the Local Security policy on the server under administrative tools.

    K. Brian Kelley
    @kbriankelley

  • August 27, 2004 at 8:43 am

    #520893

    Unfortuntatly, their IT department has gone into hiding and are refusing to acknowledge that they have made any other changes

    I checked the batch job permissions and the proxy does have the right still. While there, I also tried giving the proxy user Administrators membership on the machine and that didn't solve the problem either. Currently the only way round it I've found is giving the SQL user sysadmin membership, which is not a long term solution.

  • August 30, 2004 at 10:48 am

    #521089

    Instead of just making your proxy account a member of the sysadm role, just grant it execute permissions to the xp_cmdshell Extended Stored Proc (i.e. GRANT execute on xp_cmdshell to YourSQLProxyAccount).

    This of course is assuming that your SQL Server proxy account has been set up and granted access to the SQL Server

    (i.e. sp_grantdbaccess 'YourProxyAccount')

  • August 31, 2004 at 8:21 am

    #521228

    The proxy user doesn't have access to the SQL server at all (and doesn't require it on my test machine). I gave the proxy user Windows Administrator rights, which didn't help out. It's the user running the command who currently seems to need to have sysadmin rights on the server.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply