As per SQL sever hardening xp_cmdshell is a security risk.
Actually, it's not a security risk if you do it correctly.
First, you should never give an individual proxy privs to it. The only proper way to use it for automation purposes is to write a stored procedure that uses it and have the stored procedure do all the work using deloused parameters.
Shifting gears, a lot of people consider just turning on xp_CmdShell to be a security risk and that's the wrong thing to worry about. What you really need to concern yourself with is keeping unauthorized people from logging in with SysAdmin or ControlServer privs because even if you have xp_CmdShell turned off, someone with those privs can turn it on and grab their payload before you even know it. Someone with those privs can also use other methods to do the same thing even without turning on xp_CmdShell. As part of "hardening", it's also very helpful to limit what the SQL Server and SQL Server Agent logins can see.
For more information on hardening SQL Server, please download the following white paper.
There's a lot of pre-2005 FUD surrounding xp_CmdShell that still permeates the community. It was a well deserved fear before then. Since 2005, it's a fear because people don't know how to do it correctly and make the heinous error of giving low-prived users privs to run it directly through a proxy. Note that nowhere in that white paper does it say to turn off xp_CmdShell and never turn it on. In fact, it clearly states that if you need to use it, use it. I'll add that you must use it properly.
That being said, I do endorse the idea of using SQLCLR for this type of thing not because it brings extra security to the table but because it gives the impression that it does. Just don't relax your guard on things like password policy and keeping unauthorized people (they'll usually try to get in as an existing login) from getting in with the previously mentioned privs because having stuff in SQLCLR will not prevent you from seeing your company in the news the morning after you're breached from not having the right kind of security.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)