Hadi Fadlallah
Starting SSMS as administrator

xp_cmdshell and sp_xp_cmdshell_proxy_account stored procedures in SQL Server

June 22, 2022 by

This article explains the xp_cmdshell and sp_xp_cmdshell_proxy_account system stored procedures in SQL Server and how developers can use them to execute Windows commands.

What is the xp_cmdshell stored procedure?

In brief, xp_cmdshell is a system stored procedure in SQL Server. It allows executing Windows shell commands from the SQL Server environment. While commands are passed as an input string, the shell’s output is returned as rows of text.

The xp_cmdshell takes two parameters; one required and one optimal:

  • Windows shell command: (Required, direction: input, data type: NVarchar(4000) or Varchar(8000)): The command that will be passed to the Windows operating system
  • no_output (Optional, direction: input): when this keyword is passed to xp_cmdshell no outputs are returned

The default directory for xp_cmdshell is the Windows system32 folder. (C:\Windows\System32).

Let us check the difference between executing a command in the Windows shell or using the xp_cmdshell stored procedure. Let us execute the dir command to list all executable files in this directory. Let us first execute the command from the Windows command shell. The following image shows the command’s output.

Windows cmd output

Figure 1 – Windows cmd output

Now, if we execute the same command using the xp_cmdshell stored procedure, we can check that the output is identical.

xp_cmdshell output

Figure 2 – xp_cmdshell output

Now, let us try to use the no_output keyword. As shown in the image below, no result was returned.

using no_ouptut parameter

Figure 3 – using no_ouptut parameter

How is xp_cmdshell enabled?

By default, when we try to execute the xp_cmdshell stored procedure, the following exception is thrown:

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.

Based on the Microsoft documentation, this feature is disabled since malicious users sometimes attempt to elevate their privileges by using it. To enable this feature, we must first use the sp_configure stored procedure to enable advanced configuration, then to enable the usage of xp_cmdshell:

How xp_cmdshell execute Windows commands?

One question can come to our minds, how does xp_cmdshell execute Windows commands? What is the user account used for?

When it is called by a user that is a member of the SysAdmin role, xp_cmdshell uses the SQL Server Service account to execute commands in Windows. The main vulnerability is that the service account often has more privileges than the executed processes require, which means it should be enabled for some specific users only.

checking the windows user used to execute Windows commands

Figure 4 – checking the windows user used to execute Windows commands

If the user is not a member of the SysAdmin Role, the xp_cmdshell will execute the commands using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

Using sp_xp_cmdshell_proxy_account stored procedure

Suppose we need to execute xp_cmdshell stored procedure from a user not belonging to the SysAdmin role. In that case, we first need to grant the execution to the mapped database user using the following command:

Granting the execution of xp_cmdshell stored procedure

Figure 5 – Granting the execution of xp_cmdshell stored procedure

Or the following exception is thrown:

The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

The thrown exception when execution is not granted

Figure 6 – The thrown exception when execution is not granted

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 the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail with the following error message.

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.

Creating a proxy account credential

To configure the ##xp_cmdshell_proxy_account##, we need to use the sp_xp_cmdshell_proxy_account system stored procedure. This stored procedure has two use cases: creating and deleting a proxy account credential.

To create a proxy account credential, we should pass two parameters to the sp_xp_cmdshell_proxy_account stored procedure: the windows username and password as follows:

Even if we passed the correct credentials to the sp_xp_cmdshell_proxy_account stored procedure, an “Access is denied” exception can be thrown.

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. Error code: 5(Access is denied.), Error Status: 0.

We should close SQL Server Management Studio and start it again as an administrator to solve this error.

Starting SSMS as administrator

Figure 7 – Starting SSMS as administrator

After restarting the SQL Server Management Studio, the stored procedure is executed successfully.

Executing the sp_xp_cmdshell_proxy_account stored procedure

Figure 8 – Command executed successfully

The ##xp_cmdshell_proxy_account## credential will be created and viewed from the “Credentials” folder in the server explorer.

Credential created after the execution of sp_xp_cmdshell_proxy_account

Figure 9 – Proxy account credential created

Now, let us try to execute the “Whoami” command to check the Windows user executing the xp_cmdshell commands on Windows. The following image shows that the command is executed using the Windows credentials defined in the proxy account.

xp_cmdshell executed using proxy account credentials

Figure 10 – Executing the Whoami command

Deleting the proxy account credential

To delete the existing proxy account credential, you should pass a NULL value to the sp_xp_cmdshell_proxy_account stored procedure.

Summary

This article explained the xp_cmdshell system stored procedure in SQL Server and how to use it. Besides, it explained how to create a proxy account credential using the sp_xp_cmdshell_proxy_account stored procedure.

Hadi Fadlallah
SQL commands, Stored procedures

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

186 Views