SA0236 : The xp_cmdshell system stored procedure used

Using the xp_cmdshell poses a security risk.

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:

SQL
Copy
1-- Example of risky use of xp_cmdshell
2EXEC 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

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:

SQL
Copy
1-- Example of secured xp_cmdshell use within a stored procedure
2CREATE PROCEDURE SafeProcedure AS
3BEGIN
4    EXEC xp_cmdshell 'echo HelloWorld';
5END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 hours per issue.
Categories

Design Rules, Security Rules

Additional Information
Example Test SQL
SQL
Copy
1xp_cmdshell 'whoami.exe'  
2
3EXECUTE xp_cmdshell 'whoami.exe' ;  
4
5EXEC master..xp_cmdshell 'dir *.exe'
6
7DECLARE @result int;  
8EXEC @result = xp_cmdshell 'dir *.exe';
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
See Also

Other Resources

Analysis Rules