Friday, December 7, 2012

Enable xp_cmdshell in SQLServer

“xp_cmdshell” is a stored procedure and this will allow to run operating system commands from SQLServer.

You can run below command to get a listing of the backup files under “d:\backup\*.bak”

xp_cmdshell 'dir d:\backup\*.bak '
or
execute xp_cmdshell 'dir d:\backup\*.bak '

You will get below error if xp_cmdshell is not enabled, by default xp_cmdshell is turned off.  

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', search for 'xp_cmdshell' in SQL Server Books Online.

The xp_cmdshell can be enabled and disabled by executing “sp_configure” or using the Policy-Based Management.

-- Run below command to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Run below command to update the currently configured value for advanced options.
RECONFIGURE
GO

-- Run below command to enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Run below command to update the currently configured value for this feature.
RECONFIGURE
GO

You should see below output results when you execute the commands

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Users may receive below backup failure errors when xp_cmshell is disabled.

2012-12-07 06:00:17.03 spid50      Error: 18204, Severity: 16, State: 1.
2012-12-05 06:00:17.03 spid60      BackupDiskFile::CreateMedia: Backup device '\\SQL backup\MSSQL01\Full_Backup\AppCenter\AppCenter_backup_2012_12_07_060001_8233143.trn' failed to create. Operating system error 1330(Logon failure: the specified account password has expired.).
2012-12-07 06:00:17.03 Backup      Error: 3041, Severity: 16, State: 1.
2012-12-07 06:00:17.03 Backup      BACKUP failed to complete the command BACKUP LOG AppCenter. Check the backup application log for detailed messages.

If users are taking database backup over network drive, they may still encounter backup failures after enabling xp_cmshell .

When SQLServer is unable to connect or open connection with shared resource, you still see the backup failures. To resolve the issue create a job using below syntax in SQLServer and schedule the job, it will help to resolve connection with shared resource.

Syntax
 xp_cmdshell 'net use  \\SQLbackup\MSSQL01\ password /User:domain\userid'

Net use command - Connects / disconnects the computer from a shared resource like network drive, mapped drivers.

Refer below link for more information on xp_cmdshell
http://msdn.microsoft.com/en-us/library/aa260689(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

5 comments:

  1. Pretty section of content. I just stumbled upon your site and in accession capital to assert that I get in fact enjoyed account your blog posts. Anyway I will be subscribing to your feeds and even I achievement you access consistently quickly. mobile marketing

    ReplyDelete
  2. Media One would be your best bet for all kinds of Event Marketing Singapore needs. The agency has been serving to the needs of all businesses in the region for a significant length of time. They would ensure that you get high-quality services for a nominal price.

    ReplyDelete
  3. Simply wish to say your article is as amazing. The clearness to your post is simply great and that i could assume you're knowledgeable in this subject. Fine with your permission allow me to seize your feed to stay up to date with forthcoming post. Thanks 1,000,000 and please carry on the gratifying work.seiko watches for men

    ReplyDelete
  4. Hey there! Someone in my Facebook group shared this website with us so I came to look it over. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Exceptional blog and great design and style. jobs search app

    ReplyDelete
  5. Hey there! Someone in my Facebook group shared this website with us so I came to look it over. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Exceptional blog and great design and style. affordable eyelash extensions singapore

    ReplyDelete