SQL Server Performance

xp_cmdshell

Discussion in 'SQL Server 2005 General DBA Questions' started by fahdmalik, Jan 9, 2008.

  1. fahdmalik New Member

    I have a proxy account set up in SQL2005. This account is a regular user on the machine that has windows 2003 operating system. When I run the command
    exec xp_cmdshell 'dir C:1.txt' under the proxy account it runs fine
    but when I run exec xp_cmdshell 'dir C:1.txt | findstr 1.txt' or exec xp_cmdshell 'dir C:1.txt | dir C:1.txt' it throws the error "Access is Denied"

    I have done the above on Windows XP and everthing works as it should. Is there something peculair in Windows 2003 server that I may be missing?
  2. Adriaan New Member

    Not sure what you're trying to do ... In XP, you can add a > operator with a file name to create a new file containing the results of the DIR command, or overwrite an existing file with that name.
    I assume that "findstr" should be the first parameter of the DIR command, and not a separate parameter:
    DIR *.* /s > C:1.txt
    You probably need to concatenate the flexible part of the string into a string variable, then use the string variable as a parameter for xp_cmdshell.
    Finally, note that if you do not supply the drive letter for the results file, it will be created in the current directory in use by the xp_cmdshell process.
  3. fahdmalik New Member

    I have ran the command in DOS (dir C:1.txt | findstr 1.TXT) using the proxy account, the command works fine. But when I run it through TSQL using the proxy account (xp_cmdshell 'dir C:cd.txt | findstr cd.txt') it fails. Throwing error "access denied"
    The reason why I need this command is its convinient way to get the date modified of a file.
  4. satya Moderator

  5. fahdmalik New Member

    xp_cmdshell is configured correctly.
    I am not sure what's causing it. If you have Windows 2003 server set up, please try this.
  6. MohammedU New Member

    I think it your proxy account security configuration which cuasing this issue...
    I tried on my laptop without any issues where as my account has the admin access.
  7. fahdmalik New Member

    so you did the following
    -using sql2005/windows 2003, created a proxy account which can run xp_cmdshell and which is not part of any admin role on either SQL 2005 or Windows 2003.
    -logged in with the proxy account and ran exec xp_cmdshell 'dir C:1.txt | findstr 1.txt'
    Did this work for you?
  8. satya Moderator

    I have tried as per BOL and it worked:
    xp_cmdshell Proxy Account

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password.
    Further I suggest to look at the local security policy on SQL Server & Client's machine as you mention XP, it was tightened since XP SP2 for security reasons.
  9. v_wish New Member

    Hi, I have discovered a very strange problem in MS SQL Server 2005. When we reset the credentials of "##xp_cmdshell_proxy_account##" through the command
    EXEC sp_xp_cmdshell_proxy_account 'SHIPPINGKobeR','sdfh%dkc93vcMt0' it works always but when you try doing it from Enterprise manager it doesn't works
    In fact in my siatuation the ##xp_cmdshell_proxy_account## was already running under an accound which had sysadmin privilages on sql server and also Administrative privilages on OS but when I tried running it through another user which was a non sysadmin, the xp_cmdshell failed with error code 1385. Here is the step I took
    1) In sql server server property I enabled the proxy settings under security tab.
    2) enabled xp_cmdshell for execution by using sp_configure and reconfigure. current value ots showing fot xp_cmdshell
    is 1 for all except min value.
    3) created one user called 'test' and assigned it sys schema for master database and dbo for other user databaes.
    4) I logged in to sql server with this account and try executing exec master..xp_cmdshell 'dir'
    5) this command failed with error code 1385.
    even user test was granted with the execute permission on xp_cmd_shell stored procedure
    I tired it by creating other proxies also but no luck. When I reset the sp_xp_cmdshell_proxy_account through command line it works in one shot.
    actually I have a situation where I can not reset the sp_xp_cmdshell_proxy_account from command line as it reveals the password in clear.
    can some one tell me if its a Bug in sql server 2005?
  10. satya Moderator

    Think about granular security policies:
    For error 1385, the problem is that the user does not have the user right to "Access this machine from network." This usually occurs if an administrator has removed the everyone group from this right.
  11. v_wish New Member

    Hmm..not exactly..the thing here is that, if I do the same password reset of ##xp_cmdshell_proxy_account## without granting or revoking any permisssions to anyone..(user test already granted exec on xp_cmdshell) its working fine..Only difference is that the same thing SQL Server 2005 is not recognizing if done through Enterprise manager by exploring to SQL Server > Security > Credentials > ##xp_cmdshell_proxy_account##
    and then reset its Identity account from here its not working...
    if you do the same thing by the help of TSQL command it will work.. THIs is ALL THE GLITCH.

Share This Page