Securing a computer is a challenge. There are all sorts of potential issues in every platform, and ensuring safety for your data can be less a reflection of your ability and more the good fortune there isn’t a focused effort to attack your systems. However, we certainly also face issues with inside users, many of which may make mistakes that are accidental more than malicious. It’s for these reasons that we look for secure by default applications and a reduced surface area for any system.
Many people refuse to turn on xp_cmdshell as an option for scripting in SQL Server. This is disabled by default, and quite a few DBAs are glad of this setting. However, there are plenty of people that think xp_cmdshell isn’t a big security risk. There are certainly ways to mitigate the usage by non-privileged users, and this can be a tool that is very handy for accomplishing work without a lot of development time.
This week, as security issues become more important to us all, I’m curious how you feel.
Do you think xp_cmdshell is dangerous?
I have to admit that I’m torn. I don’t think this inherently dangerous. It does open up some attack vectors, but the last few versions of SQL Server have allowed some limitations, so I would enable this if needed to solve some issues without too many concerns. However, I wonder if many of you feel the same way.
Steve Jones
The Voice of the DBA PodcastListen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and Mevio . |
Voice of the DBA 
My issue with xp_cmdshell isn’t around security, but around performance. Because this extended stored procedure is initiating work that SQL Server has no control over, it means that a process can start that will get hung in the OS and cause problems within SQL Server itself. And because it’s outside of SQL Server, it’s difficult to cancel/kill (if at all). I’ve seen this several times in my career and it’s the primary reason I avoid xp_cmdshell.
This falls under my philosophy of “let SQL Server do SQL Server things”. SQL Server is very good at managing data. It sucks at managing the file system. Use tools appropriate to the task and don’t try to square hole/round peg something just because the option is there. (I’m looking at you xp_deletefile and xp_dirtree)
LikeLike
I agree. There are potential performance issues here, and you bring up good points. A few people have asked for an xp_posh command, which might help since you have better error handling or flow. What I’d also like is a timeout for xp_cmdshell that kills the process after xx seconds.
LikeLike
For the record, I’m against an xp_posh command as well. Again, let SQL Server do SQL Server things. We should not be calling native OS/shell commands from within T-SQL.
LikeLike
I think its a case of placement of code, usually. The stuff you want to do using xp_cmdshell should be the start of your processing, and that should access SQL Server from within that code, not accessing the external processes from within a stored procedure, for example.
Like mfal, my biggest problems with xp_cmdshell is that people use it for the wrong things (e.g. to bcp data in or out of sql, often in a transaction, not realising that its a totally seperate process and so will sit and block itself forever).
I can’t think of a solution using xp_cmdshell that wasn’t done in a better way using a different method.
LikeLike