locked
xp_cmdshell enabled but still throws error (2008) RRS feed

  • Question

  • SQL Server 10.0.1787.0 (X64)

    I have run sp_configure to enable xp_cmdshell (with reconfigure for it to take effect), but when I execute 

    exec xp_cmdshell 'dir c:\temp'
    


    I receive the usual not-configured error:

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

    I even restarted the service on the outside chance that would help.  I'm confused.  Any ideas?

     

    Thursday, August 4, 2011 3:19 AM

Answers

  • Hi GopherGold,

    It seems that you are using SQL Server 2005 from the error message. However, the version of SQL Server 10.0.1787.0 is SQL Server 2008. Please make sure that you have enabled the xp_cmdshell on SQL Server 2005 instance. There are two ways to enable it:
    • SQL Script
    EXECUTE SP_CONFIGURE 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE SP_CONFIGURE 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
    

    • Surface Configuration Tool
      Navigate to the tool from the start menu, the path is below:
      Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool
      Then select Surface Area Configuration for Features, expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply.

    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Stephanie Lv Thursday, August 11, 2011 8:58 AM
    • Marked as answer by Stephanie Lv Friday, August 12, 2011 12:34 AM
    Friday, August 5, 2011 6:58 AM

All replies

  • What does the below return?

    select * from 

    sys.configurations 

    where name like 'xp_%'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 4, 2011 5:31 AM
  • I would review the current setting with sp_configure.

    I notice that you are using a fairly old version of SQL 2008. Not that it is related to your issue, but I would recommend that you install Service Pack 2.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 4, 2011 7:27 AM
  • SQL Server 10.0.1787.0 (X64)

    I have run sp_configure to enable xp_cmdshell (with reconfigure for it to take effect), but when I execute 

     

    exec xp_cmdshell 'dir c:\temp'
    
    

     


    I receive the usual not-configured error:

     

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

    I even restarted the service on the outside chance that would help.  I'm confused.  Any ideas? 

     


    Did you run reconfigure statement??
    run sp_configure 'xp_cmdshell'
    Unless Run_value is 0 it won't work. if you see config_value as 1 then execute "reconfigure with override" and hopefully you would see some error to point us to some direction


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Thursday, August 4, 2011 7:41 AM
  • Hi GopherGold,

    It seems that you are using SQL Server 2005 from the error message. However, the version of SQL Server 10.0.1787.0 is SQL Server 2008. Please make sure that you have enabled the xp_cmdshell on SQL Server 2005 instance. There are two ways to enable it:
    • SQL Script
    EXECUTE SP_CONFIGURE 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXECUTE SP_CONFIGURE 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
    

    • Surface Configuration Tool
      Navigate to the tool from the start menu, the path is below:
      Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool
      Then select Surface Area Configuration for Features, expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply.

    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Stephanie Lv Thursday, August 11, 2011 8:58 AM
    • Marked as answer by Stephanie Lv Friday, August 12, 2011 12:34 AM
    Friday, August 5, 2011 6:58 AM
  • hi Stephanie Lv ,

    i need ur help

    actually i want to like this

    when we delete data from particular table "table1"

    then deleted data move in txt file or excel file or csv file

    at same time genrated txt file or xls file in particular folder

    pls help me

    thanks



    Wednesday, May 23, 2012 3:35 PM
  • Rather than hi-jacking an existing thread, start a new one.

    It's difficult to see what this has to do with security. From your description, the best solution would be a CLR trigger that selects the data and and writes the result to a file.

    However, that sounds like quite a crazy thing do. If you want backups, wouldn't it be better to invest in a backup strategy?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 23, 2012 9:56 PM
  • Hi there,

    Me too facing the same problem. I have done what you are saying as a solution. After i did that it remains same. Even i enabled the xp_cmdShell. What should i do.

    Is there any way to move a file from one folder to another folder.

    Thanks in advance.

    Tuesday, July 10, 2012 12:03 PM
  • Me too facing the same problem. I have done what you are saying as a solution. After i did that it remains same. Even i enabled the xp_cmdShell. What should i do.

    Is there any way to move a file from one folder to another folder.

    As Erland suggested, it is better to start a new thread for your question rather than responding to an old answered thread.  In your new question, specify the actions you have taken and the error you are getting. 

    Once xp_cmdshell is enabled and the caller has permissions, you can execute a MOVE command to move a file from one folder to another using the T-SQL code below.

    --move with same file name as source
    EXEC master.dbo.xp_cmdshell 'MOVE C:\DataFiles\MyFile.txt C:\DataFiles\Archive\';
    
    --move and rename
    EXEC master.dbo.xp_cmdshell 'MOVE C:\DataFiles\MyFile.txt C:\DataFiles\Archive\MyFileOld.txt';
    

    If the caller is a sysadmin role member, the command is executed in the context of the SQL Server service account so the service account needs permissions to the file/folder/share.  If the caller is not a sysadmin role member, the command is executed in the context of the xp_cmdshell proxy account credential, which can be configured with sp_xp_cmdshell_proxy_account.  See http://msdn.microsoft.com/en-us/library/ms190359.aspx.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, July 10, 2012 12:19 PM
  • First, don't post to an old thread, but start a new thread where you describe your problem from the start, as your problem may lay elsewhere than in the original thread.

    Moving files is outside what you normally use SQL Server for. When you use xp_cmdshell, which permission that applies on the user invoking xp_cmdshell as described in Books Online.

    There is all reason to be restrictive with enabling xp_cmdshell. It may be better to write a CLR stored procedure for the task.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 10, 2012 12:22 PM