:::: MENU ::::

Posts Categorized / Xp_CmdShell

  • Dec 06 / 2013
  • 0
Backup and Restore, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages, Xp_CmdShell

Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’

Recently i got an job failure email alert. This job is involved in backup of few databases and as a part of process it was required to create directories for each database as well. When i explored the job history to get the failure reason following message was found in log

Message
Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’ [SQLSTATE 42000] (Error 22048). The step failed.

sa is owner of this job and there is no apparent role of NT AUTHORITYSYSTEM as mentioned in the message. However there is a by design flow that involved NT AUTHORITYSYSTEM.
Point to note is that SQL Server service was configured to run under local system account.

Reason:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. In this case xp_cmdshell was being invoked by sa i.e. sysadmin so it is being executed in context of local system account. Local account was NT AUTHORITYSYSTEM that has no access to create the directories on backup location.

Solution:

To solve the issue i changed the SQL Server service login to a valid domain account that has access to create folders and files on network location for backup.

  • Aug 26 / 2013
  • 1
dbDigger, Security and Permissions, Server Level Configurations, System Administration, Xp_CmdShell

Enable and work with XP_CmdShell in SQL Server 2008 R2

Xp_CmdShell enables us to run cmd commands within T-SQL environment. The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. It requires SysAdmin rights to use Xp_CmdShell. 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 proxy account. As a security measure by default Xp_CmdShell is disabled and we have to enable it explicitly before use. If disabled then following error message will be used when tried to use

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.

Enable Xp_CmdShell

We may enable Xp_CmdShell through SSMS GUI or T-SQL. So let us explore both the ways to enable Xp_CmdShell.
To enable Xp_CmdShell through SSMS GUI perform following steps.

  • Right click on server instance
  • Click on Facets
  • Choose Surface Area Configuration from facets drop down list
  • ¬†Find Xp_cmdShell from the properties and set enabled to true
  • Click OK and Xp_CmdShell is enabled now

Following snaps will help you to perform above mentioned steps for enabling Xp_CmdShell through SSMS GUI.

To enable Xp_CmdShell through SSMS GUI step 1

 

To enable Xp_CmdShell through SSMS GUI step 2

To enable Xp_CmdShell through SSMS GUI step 3
To enable the Xp_CmdShell through T-SQL

 -- 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   

verify the current option

To verify the current status of xp_cmdshell you may use following T-SQL

-- Verify the current status of xp_cmdshell  
 SELECT * FROM sys.configurations where name = 'xp_cmdshell'  
 GO  

Using the Xp_CmdShell

Here i will quote a simple example of xp_cmdshell usage from BOL.
Executing the following xp_cmdshell statement returns a directory listing of the current directory.

 EXEC xp_cmdshell 'dir *.exe';  
 GO  
Consult us to explore the Databases. Contact us