xp_cmdshell

Last post 05-08-2008 7:20 AM by v_wish. 10 replies.
Page 1 of 1 (11 items)
Active Topics My Discussions Unanswered Sort Posts: Previous Next

  • 01-09-2008 9:59 AM

    xp_cmdshell

    Reply Contact
    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?

     

  • 01-09-2008 12:54 PM In reply to

    Re: xp_cmdshell

    Reply Contact

    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.

  • 01-09-2008 1:09 PM In reply to

    Re: xp_cmdshell

    Reply Contact

    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.

  • 01-09-2008 1:27 PM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 23,751
    • Microsoft MVP
      Moderator

    Re: xp_cmdshell

    Reply Contact

    In SQL 2005 you have to enable XP_CMDSHELL using Surface area configuration tool in order to use it. http://msmvps.com/blogs/martinpoon/archive/2006/10/03/Enabling-xp_5F00_cmdshell-in-SQL-Server-2005.aspx fyi.

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.

  • 01-09-2008 1:34 PM In reply to

    Re: xp_cmdshell

    Reply Contact

    xp_cmdshell is configured correctly.

    I am not sure what's causing it. If you have Windows 2003 server set up, please try this.

     

  • 01-09-2008 4:08 PM In reply to

    Re: xp_cmdshell

    Reply Contact

    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.

    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

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

  • 01-09-2008 4:14 PM In reply to

    Re: xp_cmdshell

    Reply Contact

    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?

     

  • 01-10-2008 3:46 AM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 23,751
    • Microsoft MVP
      Moderator

    Re: xp_cmdshell

    Reply Contact

    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.

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.

  • 05-08-2008 6:40 AM In reply to

    • v_wish
    • Not Ranked
    • Joined on 05-08-2008
    • Posts 2

    Re: xp_cmdshell : URGENT

    Reply Contact
    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 'SHIPPING\KobeR','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?

  • 05-08-2008 7:12 AM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 23,751
    • Microsoft MVP
      Moderator

    Re: xp_cmdshell : URGENT

    Reply Contact

    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.

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.

  • 05-08-2008 7:20 AM In reply to

    • v_wish
    • Not Ranked
    • Joined on 05-08-2008
    • Posts 2

    Re: xp_cmdshell : URGENT

    Reply Contact
    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.
Page 1 of 1 (11 items)
Active Topics   My Discussions    Unanswered Posts


© 2010 Jude O'Kelly All Rights Reserved.