SA0236 : The xp_cmdshell system stored procedure used
Introduction
Section titled “Introduction”Using the xp_cmdshell poses a security risk.
Description
Section titled “Description”The issue at hand is the potential security vulnerability introduced by the use of xp_cmdshell in T-SQL code. This extended stored procedure is capable of executing system commands directly from the SQL Server environment. While this functionality may offer convenience, it also opens up significant security risks since it bypasses usual database access controls and can be exploited to run unauthorized commands on the server’s operating system.
For example:
-- Example of risky use of xp_cmdshellEXEC xp_cmdshell 'dir C:\';The above query allows a user to execute operating system commands, which can escalate into misuse of the server if unauthorized users gain access. This practice contradicts SQL Server best practices for maintaining security and stability.
-
Potential for unauthorized access to server resources.
-
Risk of escalated privileges if attackers exploit the functionality.
How to fix
Section titled “How to fix”Mitigate the security risks associated with using xp_cmdshell in T-SQL code by following these steps.
Follow these steps to address the issue:
1.Review the necessity of using xp_cmdshell and explore alternative approaches to achieve the same functionality without direct shell command execution.
2.If xp_cmdshell is unavoidable, avoid building direct dependencies on xp_cmdshell within your application.
3.Configure the xp_cmdshell Proxy Account to use an unprivileged account, and ensure that the SQL Server instance’s service account has the least privileges required.
4.Instead of granting EXECUTE permissions directly on xp_cmdshell , encapsulate xp_cmdshell calls within a custom stored procedure with controlled access.
5.Avoid concatenating any user- or application-supplied string parameters to commands executed through xp_cmdshell . If string parameters must be included, escape special characters to prevent code injection, such as by adding the shell escape character ( ^ ).
For example:
-- Example of secured xp_cmdshell use within a stored procedureCREATE PROCEDURE SafeProcedure ASBEGIN EXEC xp_cmdshell 'echo HelloWorld';END;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”3 hours per issue.
Categories
Section titled “Categories”Design Rules, Security Rules
Additional Information
Section titled “Additional Information”xp_cmdshell Server Configuration Option
Example Test SQL
Section titled “Example Test SQL”xp_cmdshell 'whoami.exe'
EXECUTE xp_cmdshell 'whoami.exe' ;
EXEC master..xp_cmdshell 'dir *.exe'
DECLARE @result int;EXEC @result = xp_cmdshell 'dir *.exe';Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0236 : The xp_cmdshell system stored procedure used. | 1 | 0 |
| 2 | SA0236 : The xp_cmdshell system stored procedure used. | 3 | 8 |
| 3 | SA0236 : The xp_cmdshell system stored procedure used. | 5 | 13 |
| 4 | SA0236 : The xp_cmdshell system stored procedure used. | 8 | 15 |