What are the security risks using cmdexec?

Question

Guys,

We are in the process of migrating from SQL 2000 to SQL 2005. We have hundreds of DTS pacakges, that the development team is reluctant to redevelop using SSIS.

When migrating these packages to SSIS, I am faced with a problem - many of these packages read from Excel files.

Given that my production Box is 64 bit, I am forced to use CmdExec sub-system to call the 32 bit runtime to execute these packages.

My question here is : What are the security risks involved with using CmdExec subsystem to schedule these SSIS packages as SQL agent jobs?

Thanks, Raj

Answer 1

xp_cmdshell is the biggest security risk in SQL Server because it allows a compromised SQL Server box to elevate the attack to the host operating system itself, and from there to the entire network.

The typical vector of attack is web site HTTP form -> SQL injection -> xp_cmdshell -> take over SQL hosting machine -> take over domain. If xp_cmdshell is shut down then the attacker has to find other means to elevate its attack from SQL to the host.

Other scenarios exists, like insider users using it to elevate privileges, or using the cmdshell for other purposes, eg. steal a database. All are based on the fact that xp_cmdshell allows arbitrary commands to be executed and on the host, and in some cases the commands executed also inherit the SQL Server service account privileges.

There are other commands and extend procedures that can be used by an attacker if xp_cmdshell is blocked, but they far less known. Using the xp_cmdshell vector is in every SQL injection cheat sheet and forum discussion, so is known by everyone and their grand ma.

Answer 2

Whatever account running the job will potentially have access to run commands from the command line - so you need to think about how it will be running and what permissions the account will have.

For example, if a user could create a job that would run under the context of your sqlagent and your sql agent was overpriviledged (rights to change security), she could grant herself elevated privs or hurt your machine.

Answer 3

SQL 2008 introduced a switch for DTExec that allows you to run the packages in 32 bit mode using the native SQL Agent task for SSIS. On the execution tab of the job step properties there is a check box for 32 bit, which translates to the "/X86" switch when looking at the command line view.

If you are stuck using SQL 2005 then the CMDEXEC option is the only one I know of.