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.
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.
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.
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:
|
1 2 3 4 5 6 7 8 9 10 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO |
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.
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:
|
1 2 3 |
GRANT EXECUTE ON xp_cmdshell TO TestUser |
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’.
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:
|
1 2 3 |
EXEC sp_xp_cmdshell_proxy_account 'account_name' , 'password' |
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.
Figure 7 – Starting SSMS as administrator
After restarting the SQL Server Management Studio, the stored procedure is executed successfully.
Figure 8 – Command executed successfully
The ##xp_cmdshell_proxy_account## credential will be created and viewed from the “Credentials” folder in the server explorer.
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.
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.
|
1 2 3 |
EXEC sp_xp_cmdshell_proxy_account NULL |
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.
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023