Problem
In this tip we look at an overview of SQL Server xp_cmdshell (i.e. command shell in the SQL Server database engine) to help a SQL Server DBA and Developer know when, why, and how to invoke the xp_cmdshell extended stored procedure in Microsoft SQL Server solutions. We will also look at some errors related to using xp_cmdshell.
Solution
The xp_cmdshell extended stored procedure enables the tight integration of SQL Server and the Windows operating system in the development of solutions. This command has been available as early as SQL Server 6.5. With this extended stored procedure, you can use T-SQL and Windows batch commands to automate the sharing of data between SQL Server databases and Windows files and batch jobs. Despite these advantages, security issues and even error issues limit the scope of contexts for which xp_cmdshell is suitable. This tip aims to expose you to issues that will help you decide when, where, how, and if to include xp_cmdshell in your solutions.
Before going further, it is probably worth mentioning that there are many opinions about xp_cmdshell security and implementation issues. Some believe it is not safe enough to use. On the other hand, others value the functionality provided by xp_cmdshell relative to its ease of use. See these links for a sample of opinions on safety and other topics pertaining to xp_cmdshell (here, here, and here).
At least five prior MSSQLTips.com tips present issues pertaining to invoking xp_cmdshell, illustrate how use it in unusual contexts, or describe workarounds to using it at all.
- A tip titled "Enabling xp_cmdshell in SQL Server" drills down into how to enable and disable the xp_cmdshell system stored procedure. The tip demonstrates how to enable and disable xp_cmdshell with both T-SQL and Policy-Based Management facets. This tip also includes some coverage of how to grant permission to use xp_cmdshell to logins that are not in the sysadmin role.
- Another tip titled "Run SSIS using xp_cmdshell in a SQL Server stored procedure" illustrates how to invoke an SSIS package with T-SQL and the Windows dtexec Command Prompt utility. The dtexec utility enables access to all SSIS package configuration and execution features ( here.
- A third tip, "Creating a SQL Server proxy account to run xp_cmdshell", drills down on why and how to use the sp_xp_cmdshell_proxy_account system stored procedure to create a credential that facilitates the operation of xp_cmdshell by non-sysadmin role logins.
- A fourth tip, "Scraping HTML Tables with Python to Populate SQL Server Tables", presents a web scraping application that captures content from the internet via Python and uses xp_cmdshell to transfer Python output to SQL Server for insertion into a SQL Server table.
- A fifth tip, "Replace xp_cmdshell command line use with SQL Server Agent", demonstrates the basics of how to use SQL Server Agent to replace the need for invoking the xp_cmdshell extended stored procedure.
Simple example of using SQL Server xp_cmdshell to copy files between folders
If you are running with a sysadmin login and the Windows service account for SQL Server has access to a couple of Windows folders, then you can use xp_cmdshell to copy files from a source folder to a destination folder. The following T-SQL script shows how to copy files between folders via xp_cmdshell and the copy command in a Command Prompt window.
Logins belonging to the sysadmin role have default permission to execute xp_cmdshell. A good practice is for the xp_cmdshell extended stored procedure to remain unconfigured for use within SQL Server except when it is required for a step in a solution. Given that you are following this guideline, you’ll have to configure xp_cmdshell before you use it, and then unconfigure it for use after you finish using it. Because xp_cmdshell is an advanced option, you’ll need to configure the showing of advanced options before you can configure (or unconfigure) xp_cmdshell for use in SQL Server.
Each line of code in the following script has a preceding comment explaining its role. For example, "exec sp_configure 'show advanced options', 1" and the trailing reconfigure statement configures SQL Server so that you can configure xp_cmdshell for use. Additionally, "exec sp_configure 'xp_cmdshell', 1" and its trailing reconfigure statement enables xp_cmdshell for use within a T-SQL script. After invoking reconfigure to enable xp_cmdshell through xp_cmdshell, use "exec sp_configure 'show advanced options', 0" and its trailing reconfigure statement to halt immediate access to advanced options.
The code below demonstrates how to invoke the xp_cmdshell extended stored procedure following the guidelines described above. This extended stored procedure resides within the master database. The string in the exec statement for xp_cmdshell is a parameter that gets invoked within Windows. In this demonstration, the Windows batch command copies all files from the c:\cmdshell_test_source path to the c:\cmdshell_test_destination path.
The remainder of the script disables xp_cmdshell after the use of xp_cmdshell is completed. Notice especially that the second parameter for the sp_configure preceding xp_cmdshell changes from 1 when it is enabled to 0 when it is time to disable it.
-- run from SQL SERVER admin login
-- To allow advanced options to be changed.
exec sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
reconfigure
-- To enable the feature.
exec sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
reconfigure
-- To disallow advanced options from changing.
exec sp_configure 'show advanced options', 0
-- To update the currently configured value for advanced options.
reconfigure
--------------------------------------------------------------------
-- now this command (exec xp_cmdshell) succeeds, but a security hole is open
exec master..xp_cmdshell 'copy c:\cmdshell_test_source\*.* c:\cmdshell_test_destination'
--------------------------------------------------------------------
-- To allow advanced options to be changed.
exec sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
reconfigure
-- To disable sys.xp_cmdshell component.
exec sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for advanced options.
reconfigure
-- To disallow advanced options from changing.
exec sp_configure 'show advanced options', 0
-- To update the currently configured value for advanced options.
reconfigure
It may help some to review the contents of a Command Prompt window before and after the running of the preceding script. The following two screen shots show before and after images of a Command Prompt window that runs a dir command for the destination folder.
- In the before image, which appears first, the destination folder is empty.
- In the after image, which appears second, the destination folder has a single file (Cars93_b_4_editing.csv), which is the same one as in the source folder.
Another way to clarify the operation of xp_cmdshell is to examine the Messages and Results tabs within SSMS after the invocation of xp_cmdshell in the preceding T-SQL script.
- The Results tab appears first. Notice that it has three rows returned from the execution of the copy command via the xp_cmdshell extended stored procedure; the third row contains a NULL value.
- The Messages tab appears second. There are seven lines of content.
- The first three lines denote the enabling of the xp_cmdshell option. The second of the first of three lines documents the changing of the xp_cmdshell option value from 0 to 1.
- The first and third lines in the first three lines reflect the turning on and off, respectively, of the "show advanced options" configuration feature.
- The fourth line — (3 row(s) affected) — indicates that three lines are returned from the Command Prompt window to the Results tab from the operation of xp_cmdshell.
- The last three lines denote the disabling of the xp_cmdshell extended stored procedure.
Letting non-admin logins use SQL Server xp_cmdshell
Logins that do not belong to the sysadmin role do not have default permission to enable and run the xp_cmdshell extended stored procedure. Developers, data integration specialists, and SQL data analysts are among those who typically have non-sysadmin logins. These types of professionals may sometimes need to integrate SQL Server with output from a Command Prompt utility, such as dtexec or Python, which can return results in a Windows batch environment.
For a sysadmin login to enable a non-sysadmin login to use xp_cmdshell, two bits of functionality need to be provided for non-sysadmin logins.
- One type of functionality is a special credential. The credential contains a standard Windows domain account that enables non-sysadmin logins to use as a proxy account in Windows.
- A second type of functionality is the granting of permission to enable and invoke xp_cmdshell. By default, only sysadmin logins can enable and invoke xp_cmdshell, but a sysadmin login can grant permission to enable and invoke xp_cmdshell to one or a set of non-sysadmin logins.
A sysadmin member can enable other SQL Server professionals to use xp_cmdshell through a special credential named ##xp_cmdshell_proxy_account##. A relatively easy way to create this credential for a Windows domain account is with the sp_xp_cmdshell_proxy_account system stored procedure; the Windows account should be a standard account – not an administrator account.
When someone with a non-sysadmin login needs to invoke xp_cmdshell, they can do so through the granting of a special permission (exec on xp_cmdshell) and a link to the proxy account in the ##xp_cmdshell_proxy_account## credential. The non-sysadmin login must also have a user account or membership in a database role within the master database because that’s where the xp_cmdshell extended stored procedure exists. You can learn more about custom database roles from this link.
A prior MSSQLTips.com tip demonstrates a process for invoking sp_xp_cmdshell_proxy_account and linking the resulting credential (##xp_cmdshell_proxy_account##) with non-sysadmin logins. This prior tip additionally covers how to perform the permission assignment. After the creation of the proxy account, two steps are required.
- First, a new database role in the master database is created with exec permission for xp_cmdshell.
- Second, master database users are created for one or more logins that need permission to invoke xp_cmdshell. Each user is added to the new database role with the exec on xp_cmdshell permission.
- The first and second steps complete the process of linking logins to the database role with exec permission for xp_cmdshell.
An alternative approach may be of interest when you only need to grant exec permission for xp_cmdshell to a single non-sysadmin login. Assume the name of the user is Domain\shellProxyUser. In this demonstration, Domain\shellProxyUser is also the name of the login for the user, and it is also the name of the Windows account. This naming practice reminds you that the Windows account must belong to a domain server (named Domain in this demonstration). Here’s the T-SQL for granting a permission to invoke xp_cmdshell to a user named Domain\shellProxyUser.
grant exec on xp_cmdshell to [Domain\shellProxyUser]
This section highlights the relationships between the underlying SQL Server and Windows entities that enable non-sysadmin logins to implement solutions with xp_cmdshell. As you can see from the prior discussion in this section, SQL Server provides some flexibility in how to link the SQL Server and Windows entities. At a very top-line level, a non-sysadmin login must have a user account in the master database or belong to a custom master database role. This is because the xp_cmdshell extended store procedure resides in the master database.
A credential is a database object for connecting to a resource outside of SQL Server. The ##xp_cmdshell_proxy_account## credential created by the sp_xp_cmdshell_proxy_account system stored procedure points to a Windows domain account. This Windows domain account is the proxy account. The xp_cmdshell extended stored procedure when invoked by a non-sysadmin login looks to the credential to know what Windows domain account to use when invoking command(s) in a Command Prompt window.
Here’s an excerpt from a modified SQL Server Credential dialog. You can open the Credential dialog box for the system-level credential folder in Object Explorer. The Credentials dialog displays after you select the Properties context menu item for Credentials, which are under Security in Object Explorer. The modifications and excerpt are to conceal confidential connection details. Nevertheless, all essential details are displayed.
- Notice that the credential name is ##xp_cmdshell_proxy_account##. This name is automatically assigned by sp_xp_cmdshell_proxy_account.
- The Identity is a two-part name.
- The Domain before the backslash is for a domain name.
- The shellProxyUser name is the name of a Windows account in the domain. This Windows account executes the commands passed to it by xp_cmdshell.
- Password and Confirm password fields contain the masked password for the shellProxyUser Windows account.
The T-SQL statement for creating the credential with the above details appears below.
exec sp_xp_cmdshell_proxy_account 'Domain\shellProxyUser', 'password_for_shellProxyUser'
It may be worth reminding you that xp_cmdshell uses the SQL Server Windows service account when run from a sysadmin user. Thus, there are two different Windows accounts through which xp_cmdshell can operate in Windows.
- The SQL Server Windows service account for sysadmin logins.
- The Windows account denoted in the ##xp_cmdshell_proxy_account## credential for non-sysadmin logins.
What can go wrong (and how to work around it) with SQL Server xp_cmdshell
There are three common types of issues associated with enabling non-sysadmin logins to invoke the xp_cmdshell extended store procedure.
- A relatively easy problem to fix is the case of a mis-typed password for the Windows domain account in the ##xp_cmdshell_proxy_account## credential. This type of error is documented in at least two prior resources (here and here).
- The error for a wrong password occurs when the Windows account has one password, and the credential that references the Windows account has a different password. It can result from the invocation of the sp_xp_cmdshell_proxy_account system stored procedure with a password that does not match the Windows account, such as because of a typing error.
- The SQL Server error has a message number of 15137 and its text is "The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information."
- One remedy is to drop the ##xp_cmdshell_proxy_account## credential and invoke the sp_xp_cmdshell_proxy_account system stored procedure again with a valid password for the Windows account.
- If that remedy fails, then drop both the credential and the Windows account. Next, create a new Windows account and carefully record its password. Then, use that password when invoking the sp_xp_cmdshell_proxy_account system stored procedure.
- When there is a failure to grant execute permission to xp_cmdshell for a master database user for a non-sysadmin login, then an error with a message number of 229 occurs whose text is "The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’."
- Another kind of error can occur when invoking the sp_xp_cmdshell_proxy_account system stored procedure.
- The error message number is 15137; the Error Code is 0 or 5. The text for the message is " An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the ‘##xp_cmdshell_proxy_account##’ credential could not be created."
- This error is documented in these sources (here and here).
- This kind of error can occur because you are invoking the command without a domain server, such as in a Windows workgroup.
- One remedy suggested by some for this kind of error (here and here) involves assigning the "Log on as batch job" permission to the Windows domain account.
- Another remedy for this kind of error is to invoke run as an administrator when launching SSMS (here). You can launch this remedy in Windows 7 for SQL Server 2014 by right-clicking SQL Server Management Studio from the Start menu and choosing Run as administrator. This approach is known to fail sometimes in Windows 10 for SQL Server 2016 and 2017. A suggested solution that is reported to work for Windows 10 is to run SSMS 2014 in Windows 2014 (here).
Next Steps
- This tip aims to provide an overview of the xp_cmdshell extended stored procedure and convey a script for invoking it by a sysadmin user or a non-sysadmin user; you can copy and paste the script code from this tip to your SSMS for your own review and customization. See the script in the "A simple example of using xp_cmdshell to copy files between folders" section. Recall that special setup operations are required to enable non-sysadmin logins to invoke xp_cmdshell. The commentary for the sample script attempts to equip you to determine if xp_cmdshell works as intended through Command Prompt windows before and after the operation of the demonstration script as well as through reviews of the content in the SSMS Messages and Results tabs.
- This tip does not aim to duplicate coverage in many other resources both at MSSQLTips.com as well as other sites. If you find a reference to issues that you are encountering, examine the link(s) pertaining to the issue for additional context.
- Also, this tip closes with a brief overview of several kinds of errors and remedies that can occur when attempting to invoke the xp_cmdshell extended stored procedure or the sp_xp_cmdshell_proxy_account system stored procedure. If you have an error message like one referenced in this tip, then try the remedy offered for that kind of error.
