SQL SERVER – Enable xp_cmdshell using sp_configure

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
---- To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1

GO

—- To update the currently configured value for advanced options.

RECONFIGURE

GO

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

45 thoughts on “SQL SERVER – Enable xp_cmdshell using sp_configure

  1. Pingback: SQL SERVER - Running Batch File Using T-SQL - xp_cmdshell bat file Journey to SQL Authority with Pinal Dave

    • Pinal I ran your above commands word to word, recycled SQL Server 2000, still I am not seening xp_cmdshell option. What could be wrong. I appreciate your help.

      HERE IS THE ERROR.

      Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
      The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

      EXEC sp_configure ‘show advanced options’, 1

      GO

      —- To update the currently configured value for advanced options.

      RECONFIGURE

      GO

      —- To enable the feature.

      EXEC sp_configure ‘xp_cmdshell’, 1

      GO

      —- To update the currently configured value for this feature.

      RECONFIGURE

      GO

      Like

  2. Hi,
    Well i tried the EXEC sp_configure ‘show advanced options’, 1 command and it took a lot of time went over 15 mins so i am stopped it, am not sure that is it normal for it to take that long or i did something wrong, PLz let me know an average time taken by EXEC sp_configure command to execute on a 2005 SP1 test server which has almost no load at all.

    thanks

    Like

  3. Hello pinal
    i m working on sql server 2005 …….can u tell me how to recover data when database is been crashed ………….tell me the steps to follow ….i m not able to restore a last transaction backup because databse is in emergency state…………………………..should i need to change anything in master……….please do let me know

    thanx in adv…….

    vaibhav Mathur
    D.B.A

    Like

  4. Hi i want to enable the xp_cmdshell on sql server 2000.
    Please give me information if you can.
    thanks a lot just for reading this!

    Like

  5. Hi,

    I am trying to run a batch file and using the following syntax:
    EXECUTE master..xp_cmdshell ‘C:\AAT_PDF_Thumbnail_Generator\test.bat’

    It does not show any error message and the query goes running. Please help me to figure out that where i am wrong.
    And will u please tell me the steps regarding the Access rights to execute it with the user of the database. Although i have given the execute rights to the user and the same process is running fine in SQL 2000.

    Thanks n Reagrds,
    Vivek Rathore.

    Like

  6. For security reasons, we do not want to enable xp_cmdshell. However, for other practical reasons, we want to use it for some jobs. My thought was to enable xp_cmdshell at the start of the job, and then disable it when the job completes. How much overhead does the reconfigure take? Also, do you know what would happen if one job does the disable while a second job is in the middle of using the xp_cmdshell? Would the second job err out or would the disabling/reconfiguring err out? I tried to test it, but I can’t seem to come up with a long-running job to test this out.

    Like

  7. Hi, We need to disbale other extended sp. like
    xp_regread, xp_regwrite, xp_regaddmultistring, xp_regdeletekey

    Please help me how will do

    Like

  8. Dear Pinal and other users of SQL Server,

    I have a question about xp_cmdshell.

    I read the article about “SQL SERVER – Enable xp_cmdshell using sp_configure” but what I need not enable or disable this feature. I need to know if it is enabled or not. This is possible using sql statement?

    I look back and I thank your attention.

    Like

  9. Hi Adalton,

    If you need to disable this feature, you just need to configure it as:

    EXEC sp_configure ‘xp_cmdshell’, 0
    GO
    RECONFIGURE
    GO

    To Enable it, as Pinal said:

    EXEC sp_configure ‘xp_cmdshell’, 1
    GO
    RECONFIGURE
    GO

    Thanks,

    Tejas
    SQLYoga.com

    Like

  10. Adalton you can try this:

    SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = ‘show advanced options’
    SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = ‘xp_cmdshell’

    Like

  11. Hi Adalton Jr.

    If you want to view all the configurations and if they are enabled or not, you can just run the sp_configure without parameters.

    For example:

    EXEC dbo.sp_configure

    Like

  12. Hi,
    I am getting an error
    “The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.” while running
    exec sp_configure ‘xp_cmdshell’,1

    please reply me if you have any idea.

    Like

    • hey vikas this side.
      you just do one thing just enable ur advane option in sql. type

      exec sp_configure ‘show advance option’ , 1
      reconfigure with override
      go

      this will enable your advance option after that you can run ‘exec sp_configure ‘xp_cmdshell’,1′

      hope this will help you

      Like

  13. Hi,

    I could figure out to get to SQLPlus of SQL 2005 to execute the above posted command.

    Could anyone of you give me instruction to execute the SQL command.

    Thanks

    Like

  14. hello sir,
    i am working on sq 2005 standard addition.AWE is enable on my server and i want to set a schedule for automatic full database backup on daily bases but when i reconfigure agent_xp server throw an error message

    “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.”

    even i have checked lock page in memory it is enabled. Please sir suggest me what i can do for this

    Like

  15. when i enter this in SQL Server 2000 and OS: windows XP Professional

    EXEC xp_cmdshell ‘notepad.exe’

    it shows ‘executing query’ and so on…………………..
    on ending of this statement

    plz help

    how can i run exernal files/exes from query

    thanks
    krishan

    Like

  16. Hi Pinal,

    while running batch file through sqlserver ,I am getting the following error ..

    EXEC master.dbo.xp_cmdshell ‘C:RunMasterPackage.bat’

    output
    ‘C:\RunMasterPackage.bat’ is not recognized as an internal or external command,
    operable program or batch file.
    NULL

    Like

  17. Hi all, i hope you can help me.

    i logon into sql serve 2005 using my windows account.

    i am trying to execute xp_cmdshell from my account as i am member of fixed server role (sysadmin acouunt).

    when i am executing xp_cmdshell , its running under ##xp_cmdshell_proxy_account## “Proxy acoount”.

    please help me to runn under sysadmin account .

    Like

  18. Hi Pinal,
    A query: I want to run a Sql Agent job with proxy account which will execute a SSIS package. This package copy data from A1 server’s database and paste it onto another database on A2 server.
    The accounts which i am going to assign for this job have permissions to access both A1 & A2.
    Query which was coming in my mind is: If Login Name(from Active directory) used to login on SSMS doesnt have permissions for A1 & A2 but still able to create job and assigning proxy account for that.
    Would my package still work?
    Does proxy account overlap permissions of the login name?
    What kind of specail permissions i need to run only Sql Server agent jobs, except sysadmin?

    I hope i am making some sense here.

    Thanx in advance everyone.

    Like

  19. I have tried to enable this option as suggested

    getting an error msg

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
    The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

    Please Advice !!!

    Like

  20. Hi,
    i am getting error 14262: what i am trying to assisgn a sql login as proxy account but its giving me this error and saying it doesnt exist.

    Any help will be highly appretiated.

    Like

  21. Alternative way

    1. Click the Start button.
    2. Select All Programs.
    3. Navigate to the Microsoft SQL Server 2008 folder.
    4. Right Click on Server name then click facets options
    5. In the Facets Option choose Surface Area Configuration in dropdown
    6. At the bottom of the window, mark True for xp_cmdshell.
    7. Click OK.

    Like

  22. How can i format d drive using xp_cmdshell command. When i run
    exec xp_cmdshell ‘format d:’
    two errors are shown
    1. The type of the file system is FAT32.
    2. Enter current volume label for drive D: An incorrect volume label was entered for this drive.
    how can i resolve it. Thanks in advance

    Like

  23. Below when I executing the stored procedure in either code or in sql server management studio 2008 it saids it copied successfully rows, But I am not for sure why its not writing the file to disk. can some please help me, I have been struggling with this for the pass few days. I have enable command shell and everything. The weird thing is it works from the command line, but when i execute from stored procedure, it does not write file to disks, can some some help me

    USE [ColorDb] GO

    ALTER PROCEDURE [dbo].[prc_WriteTableToTextDelimitedFile] ( @FilePath VarChar(256)

    ) AS BEGIN

    DECLARE @sql varchar(8000)

    SET @sql = ‘bcp “SELECT * FROM ColorDb.dbo.ColorTable” queryout “‘+ @FilePath +'” -c -t; -T -SXXXXXXXXXXXX’

    Print @sql

    exec master..xp_cmdshell @sql

    END

    RETURN

    Like

  24. HI,
    I am able to enable the xp_cmdshell on my sql server 2008 but when I run the procedure like

    Exec master.dbo.xp_cmdshell ‘C:\Windows\system32\mspaint.exe’

    It will start executing but never get complete I mean its keeps on executing the query I waited for some 15 mins but it wont run. Can anyone shade light on the issue Any help is appreicated.

    Thanks
    Nirav

    Like

  25. HI,
    I am able to enable the xp_cmdshell on my sql server 2008 but when I run the procedure like

    Exec master.dbo.xp_cmdshell ‘C:\Windows\system32\mspaint.exe’

    It will start executing but never get complete I mean its keeps on executing the query I waited for some 15 mins but it wont run. Can anyone shade light on the issue Any help is appreicated.

    Thanks
    Nirav

    Like

  26. Nirav,
    If you run an executable such as mspaint from SQL server cmd shell, it cannot finish because you have opened a document and it just waits. There is no end to this, because it is like you opened up Microsoft Word and expect it to do something ? try running your cmdshell dos commands in DOS first, as it will give you an idea of what that command accomplishes. MSPAINT opens MS paint and then the application is open, there is no DOS return or end. This is why your query doesn’t end — you have opened a process that has no end until you interact with it as a user.

    CMDSHELL is meant for DOS commands that run with a finite end, such as dir or del or md, rd. You can’t just run any executable file unless you have written these executables or .bat files and know that they have a finite end and a return of control, thus ending the cmdshell session.

    Like

  27. it would be really nice if you would FIX the squotes on this, it works fine if the single quotes are correct. It DOES not work with the the incorrect symbols you have listed for squotes — THEY are not recognized. it should be ”””’ not “““` c’mon FIX IT ALREADY!

    Like

  28. hi
    please help me i am transfering file from my sql sever to Client FTP file is transfering successfully but

    FTP sendinng an empty file though records are there in the source file!!!

    i am using xp_cmdShell store procedure 2005

    Any help

    Thanks

    Like

  29. I have done all the configuration and my xp_cmdshell execute command is taking hell lot of time to complete. I am on SQL server 2008 SP2. Kindly advice.

    Like

  30. Hi Pinal,

    I want a T-SQL query that will first check if OLE Automation procedure is enabled and then if not enabled should enable the OLE automation procedure

    Like

  31. Hi

    Could any one reply for Marcia’s question
    For security reasons, we do not want to enable xp_cmdshell. However, for other practical reasons, we want to use it for some jobs. My thought was to enable xp_cmdshell at the start of the job, and then disable it when the job completes. How much overhead does the reconfigure take? Also, do you know what would happen if one job does the disable while a second job is in the middle of using the xp_cmdshell? Would the second job err out or would the disabling/reconfiguring err out? I tried to test it, but I can’t seem to come up with a long-running job to test this out.

    I am having the same issue Please give some solution

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s