SA0236 : The xp_cmdshell system stored procedure used

The topic describes the SA0236 analysis rule.

Message

The xp_cmdshell system stored procedure used

Description

The rule checks the T-SQL code for calls to xp_cmdshell system stored procedure. It is recommended to avoid usage of the xp_cmdshell system stored procedure unless it is absolutely needed.

The xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. It allows execution of operating system commands and host executables from the T-SQL code directly in the Windows command outside the controls of database access permissions. This could be exploited by malicious users to take control over the host OS and perform additional malicious activity.

How to fix

Review the usage of the xp_cmdshell stored procedureIf and if it cannot be avoided, consider the following in order to try to reduce the security risks:

– If you need for your application to run a specific command or utility on the SQL Server, be careful not to build a dependency on direct access to xp_cmdshell into your application.

– Use an unprivileged account for the xp_cmdshell Proxy Account and for the SQL Server instance’s service account.

– Do not grant EXECUTE permissions directly on xp_cmdshell, but wrap xp_cmdshell calls within another stored procedure.

– Avoid concatenating any user – or application – supplied string parameters to the command that will be executed through xp_cmdshell in order to avoid potential code injection attack via xp_cmdshell.In case string parameters need to be supplied, add the shell escape character(^) to escape any occurrences of the ampersand character or other characters.

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
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