Bonus summary: xp_cmdshell is limited to admins, unless you specifically grant permissions to users. And if you’re an admin, you have the power to turn on and use xp_cmdshell anyway. Xp_cmdshell is not a security hole.
This is a reprint of Sean McCown’s original post on DBARant. You know, reprinted with permission and all that.
I’ve been hearing it more and more the past year.
“XP_cmdshell should always be turned off.”
“Whatever you do, don’t turn on XP_cmdshell!”
“We can’t do that, it requires XP_cmdshell!”
“You’ll fail your audit if XP_cmdshell is turned on.”
And all the other variations.
And I suppose I’ve been hearing it more and more lately because Minion Reindex requires it and Minion Backup will require it even more so.
However, I’ll tell you I’m getting pretty tired of hearing it so true to my blog I’m going to rant.
XP_cmdshell has been around forever. And way back in the day, like 15-20yrs ago, it was installed wide open to the public. This is where the problem started. This was back in the day when SQL’s GUI allowed way too many people who had no idea what they were doing to create and manage DBs. That ease of use was a huge part of SQL Server taking hold in the industry. However, with the product being that easy to use, a lot of these untrained DBAs had no idea XP_cmdshell was even there, so their instance was completely vulnerable and they didn’t even know it. Honestly, this was Microsoft’s fault. They should never have packaged up something that dangerous completely open to the public. But you know what, back then they were also installing sa with a NULL password by default too. And Oracle had their scott\tiger username\password combo, so MS wasn’t the only one doing dumb security back then.
However, now XP_cmdshell comes turned off and when you enable it, it’s not open to public anymore. So seriously, what are you still afraid of? I understand that you used to be scared of it because there was no way to lock it down back then. In fact, Microsoft didn’t provide a way to lockdown XP_cmdshell until somewhere in the neighborhood of version 4.2. So back when it was open to public I can see how writing a DENY statement would be really taxing to you as a DBA.
But these days you don’t have any excuses. You have to go out of your way to open it up to public. XP_cmdshell is still really useful and I’m personally able to create many excellent solutions using it… things that would be much more difficult otherwise. And do you know what I tell people who tell me how dangerous it is? I ask them why they don’t lock it down.
Think about it… there are many dangerous features in SQL. And they’re all kept in check by controlling permissions to them. You don’t see anyone screaming that those other features should be allowed on the box because they just say, we use it but we keep its usage controlled pretty tightly. So why doesn’t that apply to XP_cmdshell? Do you think that SQL all of a sudden forgets how to deny execute perms when that gets called? Do you think that SQL honors all security except that one? Do you think XP_cmdshell is powerful enough to override SQL security and just do what it wants anyway?
Of course not. So what are you afraid of?
The truth is that XP_cmdshell can do a lot and in the wrong hands it can make a royal mess of things. Then again so can DELETE and UPDATE. So can SHUTDOWN. So can CLR. So can DROP DATABASE. So can Dynamic SQL. And you don’t see anyone saying that all of those should never be allowed on any server for any reason. And I would honestly venture to say that Dynamic SQL has been the cause of far more security breaches than XP_cmdshell ever has. I don’t have any numbers to back me up, but I bet if you look at the number of security issues caused by XP_cmdshell, they’re far out-weighed by other features.
And it’s not like people have to way to get that functionality just because XP_cmdshell is disabled. There are still cmdline job steps and cmdline SSIS tasks. And of course, you’ve got CLR. All of which can be just as dangerous as XP_cmdshell yet they run on systems all the time. And I know what you’re thinking… “But Sean, we control those through permissions so they can’t do anything really bad.” Yeah, so you’re making my point for me. But do you think that if an SSIS guy wanted to do something bad to your box that he couldn’t find a way if he weren’t locked down? Of course he could.
The cool thing about the cmdline task in Agent jobs is that they can be run via proxy. You can setup a proxy user to run that step under so that its Windows perms are limited and it can’t run haywire. You wanna hear a secret? There’s a built-in proxy mechanism for XP_cmdshell too. I could tell you how to do it, but DatabaseJournal has already done such a fine job. So here’s the link to setting up the cmdshell credential.
I don’t want you to just turn on XP_cmdshell on all of your systems for no reason. But I don’t want you to completely rule it out as a solution just because you’re afraid of it. Tell your Windows admins who are afraid of it to mind their own business and stick to what they know. You’re a DBA and it’s time for you to take back your SQL instances. Lock them down. Don’t be afraid to use cool functionality because so many people refused to read the documentation 20yrs ago. You know better now. So go out there and do the right thing. Lockdown XP_cmdshell, but use it.
I’ll have to disagree, strongly. xp_cmdshell most certainly IS evil.
Your comment about proxies is incomplete. There is only a single xp_cmdshell proxy for the entire instance meaning that ALL user requests will be handled by it. That aside, like lots of other tools that are deemed security risks xp_cmdshell allows an interactive user to obfuscate their identity on the host OS without providing a username and password. With SQL Agent every job step that uses CmdExec (or other Agent subsystems for that matter) use a different proxy account and while the same obfuscation potential exists…it is an unattended job scheduler designed to do that task whereas xp_cmdshell is an ill conceived interactive feature.
I could write a comment addressing every other twist amd turn the rant but I won’t do that right now mainly because it has all been said before, and hashed, and rehashed over on SqlServerCentral.com’s Forums between myself and several others.
You can frame it as a rant, fine, be frustrated, but in my opinion it is irresponsible to talk about the tool without providing all the parts that are in fact dangerous about it. It is a dangerous tool from a security perspective and from a design perspective. With Azure it is game over for xp_cmdshell. For on premises I propose Microsoft make xp_cmdshell a separate Engine feature one has to install, like Replication, and let people close the security hole once and for all, on their terms.
Ok, so while you’ve got some nice sound bytes there, you too are giving only half the story. There was actually a more complete follow-up to this that discussed the proxy. And sure, users can have only a single proxy to cover all of them, but that’s not the point. The point of the piece is that shops close off the use of cmdshell across the board w/o understanding it.
By default, it’s available only to sysadmins. You have to specifically open it up to users. I’m personally not that comfortable with end users being able to use cmdshell, but I think it’s fine for admins. So why not turn it on for admins? It gives us some great functionality that we didn’t have before and you can’t keep us from turning it on and then turning it back off when we’re done anyway.
So while you’re concerning yourself with sound bytes and vague accusations about things that can happen, I’m concerned with the truth about cmdshell. And that truth is that when available only for admins it poses no more harm than anything else we could do.
This was the point of the pieces we’ve written about cmdshell.
It’s not a wise tool to put in the hands of end users, but in some circumstances it may be necessary. It should be severely limited though. But to just outlaw it outright and say that not even sysadmins can use it is just childish. We’re not cavemen who are afraid of thunder, we’re supposed to be in IT and giving these issues real consideration.
So what does turning on cmdshell for admins do to you that otherwise would be more safe? Because you either have rights to run it (because you’re an admin) or you don’t. And if you have those rights and it’s not on, then you could just turn it on and run it. So where’s the evil in knowing how to properly configure and use a feature?
I *believe* this is the follow up blog you’re talking about: Security Theater
I don’t know cause that’s not the one you reprinted here.
Orlando:
You can say anything is evil when you don’t understand it and misuse it.
Inserts are evil because they cause blocking… so are updates actually.
Agent is evil because you can do harm with that too.
Maxdop is evil because I brought down my server with it.
The sa account is evil because I didn’t set my password strong enough.
Backups are evil because I didn’t encrypt them and someone stole my data.
etc. etc. etc.
So it doesn’t really matter which piece the comment was about. Something isn’t evil because you refuse to use it where it’s meant to be used.
Oh y, and let’s not forget cursors are evil too. I’m not sure why but everyone says they are so I believe it even though I can’t really name anything wrong with them.
I picked out one point of the post where you only gave part of the story to make a point. You would need to write a book to detail and qualify all the areas where xp_cmdshell use can go wrong and how to mitigate any risks. The big problem I have are the anecdotes handed out to “just turn it on” because a sysadmin can do anything they want anyway. That is not the point. There are plenty examples in technology where governance goes a long way towards stability and security and this is no exception.
Your position is interesting because you built a tool on top of xp_cmdshell that by all accounts (I have not used it for obvious reasons) is good and now you are advocating for the safety of that tool. I wish you would have spent your energy developing Minion in PowerShell to be capable of running from any machine against any instance, with no dependencies on the installed toolset of the target instances. Minionware may be great but why you chose to employ xp_cmdshell I’ll never understand. Maybe I could get on board with its use for the backup tool if you said you wanted a trade-up from the undocumented procedures Ola employs. However, why you chose to use it for re-indexing I will never understand. It just wasn’t needed there.
The argument for using xp_cmdshell is essentially deprecated. The on premises story is changing rapidly as well while things move towards private clouds where tools like xp_cmdshell will finally be made obsolete in favor of distributed architectures that leverage tools like PowerShell. I have hashed this out to the core on SqlServerCentral.com with you and others but your blog posts deserved a nod in that direction if for nothing else so people always know there is more to the story. xp_cmdshell has issues when it comes to security and is a very poor choice for developing tools, especially new ones.
Well I think you’re overstating your case just a little. Maybe you could write a book on cmdshell issues, but it would be very small. And even then it would just be one example after another of pretty much the same thing again and again.
So the main reason why my routines use cmdshell is for extra info and error handling. Unlike Ola who only spits errors out to thousands of files, I wanted something that gave you a very easy way to see the errors in your routines. So with this method I can very easily put the errors into my log tables and make them accessible to the user. I can also get a lot more info very easily through powershell, which from tsql I have to get at through cmdshell. In 2016 they gave us a way to run R scripts from tsql, but still no way to run PS scripts. And writing commercial software I’m limited in what I can do. I can’t put it in CLR because I can’t require that everyone turn on CLR just to take backups or do index maint. I can’t run PS scripts because that would not only make the install more complicated, it would make the routine more complicated because I can’t guarantee they’ll be in the same place on every server because some companies don’t allow anything on C: and I don’t know what other drives will be around. I can get around that, but it’s just one more thing. And they’d still have to copy the scripts to their servers, and I don’t like putting scripts out on servers.
And that’s another issue… PS. Some companies don’t have PS turned on and don’t want it turned on. Some have different versions of PS and even different versions of windows so I can’t count on the same version of PS everywhere, or even the ability to even run it.
Let’s not forget also that these are maint tools for DBAs. DBAs like their maint in tsql. I don’t like PS for maint because I’m already in SSMS. And there are still tons of DBAs who don’t know PS and who aren’t comfortable with it.
Simply put, there are pros and cons to each one and I chose tsql because cmdshell isn’t really that bad.
You keep harping on the fact that cmdshell is evil instead of just using it correctly. The fact that admins can do whatever they want with it IS the point. That’s the point entirely. Anyone with sysadmin can do whatever they want. So let’s get rid of sysadmin. It’s not good enough to limit who has access. Sysadmin is a security risk because you can do some horrendous damage with it. So get rid of it. If you get rid of everything that can be misused you wouldn’t have any features left. So yeah, the fact that sysadmins can do anything with cmdshell IS the entire point. Open it up to them and let only them use it. And until you offer me a way that turning off cmdshell will keep a regular user from promoting themselves to sysadmin and taking advantage of whatever they like, then you have no argument. Because the point is to use SQL security to lock things down. If you’re telling me that SQL security doesn’t work for cmdshell then show me the repro code. Because if that were the case then I’d stop trying to lockdown anything in SQL.
But what you’re telling me is that SQL security procedures that we put into place simply don’t work. There’s no point in trying to lockdown DBs, or tables, or SPs, or anything because someone can just come in and do whatever they like. Yeah, someone could be given perms they don’t need by accident, but that’s true with ANY feature, good or bad. So where is cmdshell so incredibly evil in this scenario.
I fear you’re really being way too hyperbolic on this issue. Either you trust that security works everywhere or you think it doesn’t and needs to be regulated by removing features left and right. So if anyone gets access they don’t need, that features gets yanked from the product because it’s impossible for companies to regulate who gets what.
Really?
|| Well I think you’re overstating your case just a little. Maybe you could write a book on cmdshell issues, but it would be very small. And even then it would just be one example after another of pretty much the same thing again and again.||
The fact that you think a book explaining the perils of employing xp_cmdshell could be short, let alone should be short, tells me all I need to know about how little you know about xp_cmdshell. You may know how to use it for your product or for some of the primary DBA use cases but it is clear you have no clue what damage people can do with it under certain circumstances or what damage it does to create invalid inroads into a systems security hierarchy when it is a central piece of an application’s design. How about you stop bastardizing SQL Server trying to twist it into a one-stop-shop-application-server and join us in the world of distributed computing?
|| So the main reason why my routines use cmdshell is for extra info and error handling.||
It is a ridiculous comment to portray cmdshell as a better error erporting vehicle (a 255 character 1-column resultset) to an object-pipeline that you have in native PowerShell. I am confident you cannot do better than native PowerShell in this area. You may have done the T-SQL gymnastics but how many broken wrists have you suffered because of it, hmm?
|| Unlike Ola who only spits errors out to thousands of files, I wanted something that gave you a very easy way to see the errors in your routines. ||
I am not sure what kind of errors your systems are encountering but the CommandLog table Ola stands up and populates with his routines has given me what I needed every time when I needed to get in there and troubleshoot, and I have been using his routines for 4-5 years.
|| So with this method I can very easily put the errors into my log tables and make them accessible to the user. I can also get a lot more info very easily through powershell, which from tsql I have to get at through cmdshell. ||
This statement is convoluted, at best. This is where you lose me, Sean…you like PowerShell, but yet you insist on running it through T-SQL. You argument about versioning and enablement is contradictory to your entire configuration. The whole thing makes no sense. It’s like you bought into xp_cmdshellm then figured out you needed a better scripting interface, and came up with this bright idea to call PowerShell through xp_cmdshell. Talk about misguided misdirection. Why didn’t you just walk across the wet paint to get out of the corner and buy some new shoes? PowerShell + SMO and you were done picking your app stack.
|| In 2016 they gave us a way to run R scripts from tsql, but still no way to run PS scripts.||
And why do you think that is? Because R is a language that extends the capabilities of the data engine and PowerShell is a scripting language meant to make administration easier. Why would they bake that into a database engine? Oh that’s right, because there aren’t any better options, which as you pointed out there weren’t any when xp_cmdhsell was introduced. Now there are. Please join us.
|| And writing commercial software I’m limited in what I can do. I can’t put it in CLR because I can’t require that everyone turn on CLR just to take backups or do index maint. ||
So, let me get this straight, you will not compel your customers to enable SQLCLR but you will compel them to enable xp_cmdshell, an antiquated security red flag with limited functionality and an archaic interface. I am sorry but that is backwards thinking, Sean.
|| I can’t run PS scripts because that would not only make the install more complicated, it would make the routine more complicated because I can’t guarantee they’ll be in the same place on every server because some companies don’t allow anything on C: and I don’t know what other drives will be around. I can get around that, but it’s just one more thing. ||
PowerShell supports dynamically determining the directory where the script is being run. It also supports relative pathing from there. So, no excuses. If you will compel your customers to enable xp_cmdshell and deploy a bunch of database objects you can compel them to deploy some PowerShell. Heck, you could even have implemented a switch on the main PowerShell script that would initialize the database with tables and objects required for the solution to work, making the whole thing turnkey with a single PowerShell script deployment.
|| And they’d still have to copy the scripts to their servers, and I don’t like putting scripts out on servers.||
You only need them to install it on one server. Make it so they can deploy the script to a single application server that simply talks to all other database servers in the environment. Done.
|| And that’s another issue… PS. Some companies don’t have PS turned on and don’t want it turned on. Some have different versions of PS and even different versions of windows so I can’t count on the same version of PS everywhere, or even the ability to even run it. Let’s not forget also that these are maint tools for DBAs. DBAs like their maint in tsql. I don’t like PS for maint because I’m already in SSMS. And there are still tons of DBAs who don’t know PS and who aren’t comfortable with it.||
Make it turnkey.
|| Simply put, there are pros and cons to each one and I chose tsql because cmdshell isn’t really that bad.||
Oh but it is., Sean. You are in denial my friend.
|| You keep harping on the fact that cmdshell is evil instead of just using it correctly. ||
I have been using xp_cmdshell, to varying degrees, since day one of my beginning to use SQL Server which is a lot of years now. I know very well what it is, what it is not, and why people should avoid using it in any context. Do not doubt that. I have unwound some nasty implementations that utilize it. I bet I could unwind it from use within Minion.
|| The fact that admins can do whatever they want with it IS the point. That’s the point entirely. Anyone with sysadmin can do whatever they want. So let’s get rid of sysadmin. It’s not good enough to limit who has access. Sysadmin is a security risk because you can do some horrendous damage with it. So get rid of it. If you get rid of everything that can be misused you wouldn’t have any features left. So yeah, the fact that sysadmins can do anything with cmdshell IS the entire point. Open it up to them and let only them use it. And until you offer me a way that turning off cmdshell will keep a regular user from promoting themselves to sysadmin and taking advantage of whatever they like, then you have no argument. Because the point is to use SQL security to lock things down. If you’re telling me that SQL security doesn’t work for cmdshell then show me the repro code. Because if that were the case then I’d stop trying to lockdown anything in SQL.
But what you’re telling me is that SQL security procedures that we put into place simply don’t work. There’s no point in trying to lockdown DBs, or tables, or SPs, or anything because someone can just come in and do whatever they like. Yeah, someone could be given perms they don’t need by accident, but that’s true with ANY feature, good or bad. So where is cmdshell so incredibly evil in this scenario.||
You are absolutely right. We should get rid of sysadmin [members], where possible. That is part of the equation to maintaining a secure environment. Enforcing that xp_cmdshell remains disabled and unused through policy, in all senses of that word, is another part of the equation.
|| I fear you’re really being way too hyperbolic on this issue. Either you trust that security works everywhere or you think it doesn’t and needs to be regulated by removing features left and right. So if anyone gets access they don’t need, that features gets yanked from the product because it’s impossible for companies to regulate who gets what. Really?||
Yes, really. Either you are not paying attention or you are uninformed. Either way there is not much more that I can say other than, please join us.
I’m going to agree with Jen on this one. I didn’t even read your entire last comment because you called me stupid like 7 times and outright stated that I know practically nothing about SQL.
So either educate me and show me what a moron I am or get out. But you’ve devolved into basic name calling and I don’t have time for these little internet wars.
Like we’ve said many times now. Give us a real example of how an end user could misuse cmdshell in the way it’s implemented as I’ve discussed. Since you could write a very large book on the topic that shouldn’t be too hard.
Stop calling me stupid and show me. Because if a regular user can gain access to sysadmin that easily and take over the system then why is cmdshell the biggest concern? And doesn’t that mean you’ve got bigger problems in SQL security than whether cmdshell is enabled?
So show us a real example of an end user taking over sysadmin where having cmdshell enabled is what enabled him to do it.
First things first, just a brief reminder to keep things civil. It’s getting a little tetchy in here, and I have no compunctions whatsoever about auditing or editing comments if I feel they’ve gone over the line.
Second, on point: “…it is clear you have no clue what damage people can do with it under certain circumstances or what damage it does to create invalid inroads into a systems security hierarchy when it is a central piece of an application’s design.”
I’m still waiting to hear an actual example, as Sean has asked for several times, of a situation where SQL Security is in hand, xp_cmdshell is enabled, and the system is still in danger. You don’t have to write a book, or make claims on anyone else’s ability to do so. Just give me a real world example of potential damage in a shop with, say, a reasonable number of sysadmins, and xp_cmdshell enabled in the way discussed in the article.
Sean, if you cannot take a few flippant remarks then you should stop dishing them.
The use of xp_cmdshell, especially in a newly developed solution where better options existed, points to a flawed approach with respect to security and design. It’s not about how “user A cannot somehow magically elevate their permissions to a sysadmin level” and if that is your defense then you;re not looking at the whole picture. If you choose not to see the downfalls of employing xp_cmdshell and the road it leads teams down I cannot do more to help you see it. I am sure your product will do fine since the community seems to be split down the middle on this topic so that leaves a good chunk of folks still willing to go down the path with you. However, over time, the option will continue to disappear despite what folks think about the tool as the environments capable of running it migrate to the cloud or more tech managers and architects come to their senses and move towards more scalable, robust solutions.
So, you don’t have any specific examples at all.
I’m just looking at the original argument around the xp_cmdshell proxy being problematic as it allows for unregulated usage of the command line from SQL.
So xp_cmdshell is locked down to sysadmins (unless you decide to change that, but why would you?). The proxy account that you define only has permissions at the OS layer that you define, so unless you go making it an admin somehwere there are some well structured limits to what it will be able to do.
As such, provided that you properly secure both SQL and the proxy account I fail to see the significant concern that is being presented.
It is the unqualified offering of the tool as “secure”, or rather “not evil”, that is the problem. What you described is a smart approach, maybe the best we can do, but it does not address the obfuscation of a user’s identity without compelling them to provide a password for the credential they are impersonating. It also does not address a related issue which is that all users authenticate to the OS as the same user, the proxy user, and therefore traceability of actions is lost.
Yes, but as we are talking about enabling xp_cmdshell for sysadmins only, and it’s easy as hell as a sysadmin to obfuscate your actions WITHOUT xp_cmdshell, it really doesn’t matter that it CAN be done with it. Cmdshell offers no additional danger of obfuscation – it’s not really any easier tan the methods I can think of – so it’s a completely moot point.
Just for fun, what other methods were you thinking of?
It simply baffles me that folks still advocate *for* the use of xp_cmdshell, in any context. Employing xp_cmdshell means employing a feature that was essentially deprecated with the release of SQL 2005 when SQLCLR replaced Extended Stored Procedures as the preferred means to extend the platform, outside the confines of the engine, initiated from within a T-SQL context.
To those folks that have not or will not pickup PowerShell, how much more evidence do you need that it is a strategic technology for Microsoft? And a follow-on, how much more time do you need to pick it up? PowerShell has only been around since 2006, so what, maybe another 10 years?
Let me reframe it for you…do you have any reservations about allowing someone to obfuscate their identity without providing a password for the credential they can impersonate? And before you answer consider that in many cases due to a lack of understanding about how combinations of attack vectors can be exploited, the credential has more permissions than are necessary to carry out needed functions, many times local admin on the Windows box plus tons of permissions on the network. Now imagine all the servers running sql server on the network are running under the same account. If you want more search for forum posts on this topic and you’ll get more in depth discussion about it.
I really don’t want to make you feel like everyone is jumping on your Orlando but I do have a few points & questions.
You said:
So, let me get this straight, you will not compel your customers to enable SQLCLR but you will compel them to enable xp_cmdshell, an antiquated security red flag with limited functionality and an archaic interface.
If xp_cmdshell is antiquated with limited functionality it would seem to be that it is less of a threat than more modern tools with greater functionality correct?
You said:
If you will compel your customers to enable xp_cmdshell and deploy a bunch of database objects you can compel them to deploy some PowerShell.
And while that’s nice, I work at a large company with several hundreds of SQL installs. I can write a simple piece of code to turn on xp_cmdshell across all of them. Getting PS on those boxes .. and getting security to work using PS would be an act of congress.
When discussing the problems of getting PS installed on every machine and scripting when there are multiple versions of PS with different functionality you said “Make it turnkey”. Honestly I find that a somewhat flip answer. If you have a way I can get my company to agree to put a uniform version of PS on every server (let alone security implications, which for PS are far more vast than the limited xp_cmdshell) then I would love to hear it. And before you say something about living in the modern world I would like to point out that large companies are frequently behind the times. We just finished our upgrade to 2008 R2 last year. In spite of my frequent arguments of going up to 2014 or even 2012. It’s just the way bureaucracy works.
Now I will say you have one valid argument (and you may have more but you haven’t mentioned them in any concrete way yet). If I do something using xp_cmdshell then there is no way to tell if it was me or one of the other DBAs who have sysadmin access. I can’t argue with this. Of course the number of things I can do with xp_cmdshell that are actually logged is pretty limited. Writing to a file? Running a directory? Let’s remember that xp_cmdshell is limited to the permissions of the service account for SQL Server, which if you are that worried about security should be pretty limited anyway.
Last but certainly not least if you are worried about a sysadmin doing something malicious and trying to hide their tracks then shouldn’t you be more worried about SQL Agent? Where I can create jobs that run under a proxy? Jobs that can run PoSH (a much more modern tool with much further reaching implications) as well as command shell scripts?
But here is the trick. In my office only the sysadmins have access to xp_cmdshell. And in my office we trust our sysadmins. They have too much power to not trust them.
|| I really don’t want to make you feel like everyone is jumping on your Orlando but I do have a few points & questions. ||
No worries, at all. It’s a good discussion.
|| If xp_cmdshell is antiquated with limited functionality it would seem to be that it is less of a threat than more modern tools with greater functionality correct? ||
At face value you might think so, but no. SQLCLR is far and away a better option in the area of security than xp_cmdshell. It would be like comparing a hammer with a fork and a high-end steak knife. Could you eat a filet mignon by pulverizing it with a hammer into bits small enough to eat, sure, but would you rather use a fork and a sharp knife in delicious meat-morsels? Please forgive the analogy if you have a problem with meat eaters.
I would urge you, and anyone, to take a deeper look at the security model SQLCLR brings with it and compare and contrast it with what xp_cmdshell offers.
|| And while that’s nice, I work at a large company with several hundreds of SQL installs. I can write a simple piece of code to turn on xp_cmdshell across all of them. Getting PS on those boxes .. and getting security to work using PS would be an act of congress. ||
You’re missing my point. With a proper solution you only need PowerShell and SMO installed and configured on *one* machine, the machine that will handle backups for your entire environment. And here is the nice part, SMO abstracts all the SQL Server version differences so you only need to write the code once and it can run against every version of SQL Server in your environment (including SQL 2000).
|| Last but certainly not least if you are worried about a sysadmin doing something malicious and trying to hide their tracks then shouldn’t you be more worried about SQL Agent? Where I can create jobs that run under a proxy? Jobs that can run PoSH (a much more modern tool with much further reaching implications) as well as command shell scripts? ||
It’s not a common configuration but SQL Agent is not technically necessary. Many shops employ enterprise job schedulers and do not run Agent. I know it blows some people’s minds to think of a world without Agent but guess what, it’s an option and is part of the puzzle.
|| But here is the trick. In my office only the sysadmins have access to xp_cmdshell. And in my office we trust our sysadmins. They have too much power to not trust them.||
Trusting your DBAs and limiting attack vectors, whether internal and external, are not mutually exclusive concepts.
I’m going to circle back to the beginning here. xp_cmdshell is a tool. It’s a powerful tool (even if PS & CLR can do more) particularly if you are old enough to have worked with DOS for any number of time. But it’s a TOOL. If only your sysadmins can use it, and you trust your sysadmins then where is the real risk? As a sysadmin I can cause more problems with Agent or PS than I can with xp_cmdshell. And I can obscure myself while doing it. So why is xp_cmdshell so much worse than Agent? I don’t see you arguing that Agent should go away? Or PS? Or CLR?
The thing is they are all tools. Any tool can be used to break or build. Yes there are security implications to xp_cmdshell. However, as long as only the sysadmins have access to it, what can they truly do with it that they can’t otherwise? Including obscurity.
And please, I’d love to hear it if you have a specific example of where xp_cmdshell can do something that PS or CLR can’t. Something malicious.
Just a side note I work for a company where we try to limit SQL Agent in favor of an enterprise scheduling tool. That tool has FAR more security holes than Agent (and that’s saying something).
|| As a sysadmin I can cause more problems with Agent or PS than I can with xp_cmdshell.||
Intentionally? I suspect all are on equal footing with jntentionally respect to causing trouble.
|| And I can obscure myself while doing it. ||
As mentioned, Agent’s primary function is to act as an unattended job scheduler complete with granular credential management amd impersonation capabilities. You cannot evenly compare that to xp_cmdshell which is in a different class.
||So why is xp_cmdshell so much worse than Agent? I don’t see you arguing that Agent should go away? Or PS? Or CLR?||
You may not have seen it yet, but I have the same position on UNSAFE and EXTERNAL_ACCESS CLR assemblies…we should have the option to leave it out of the engine per the installation process.
We already have the option to leave Agent disabled. And PowerShell is not part of the engine but we have the option to reatrict its use to the point where a member of sysadmin could not access it.
||The thing is they are all tools. Any tool can be used to break or build. Yes there are security implications to xp_cmdshell. However, as long as only the sysadmins have access to it, what can they truly do with it that they can’t otherwise? Including obscurity.||
Again, you’re only looking at the tool in and of itself.
||And please, I’d love to hear it if you have a specific example of where xp_cmdshell can do something that PS or CLR can’t. Something malicious.||
PowerShell is not in scope within the engine. You cannot impersonate an account from PowerShell without providing a password for the account whereas you can with xp_cmdshell.
Qualifying CLR use, UNSAFE and EXTERNAL_ACCESS assemblies have security baggage too but we have more options than with xp_cmdshell. I try sticking with SAFE assemblies and would like to have the option prevent other types of assemblies via install options.
||Just a side note I work for a company where we try to limit SQL Agent in favor of an enterprise scheduling tool. That tool has FAR more security holes than Agent (and that’s saying something).||
Sounds like either a poor piece of vendor software, a poorly controlled inplementation or a combination of both.
It’s almost a year later, yet after rereading these comments I’m still amazed that Orlando failed to provide a single example where a non-sysadmin can cause any sort of damage to anything at all if cmdshell is enabled.
Lots of rhetoric, to be sure. But not a single, reproducible example.
Agreed!
You added the “non sysadmin” qualifier and that’s you. I disagree with the use of xp_cmdshell because of the tone it sets. SA or not, having it in play adds an attack vector we simply don’t need. There are better safer ways to leverage SQL Server. Feel free to disagree. I do.
Earlier you were asked, “Give us a real example of how an end user could misuse cmdshell in the way it’s implemented as I’ve discussed.”
Maybe I’m mistaken but to me an end user is a non-sysadmin. So, no, I didn’t add the qualifier, just used the identity:
end user = non-sysadmin
“because of the tone it sets.”
Seriously? That’s about as vague as it gets.
I’m all for better and safer, but I believe that excludes both PoSh and CLR. Both are potentially far more dangerous than cmdshell.
OTOH I wish T-SQL had built-in ways to manipulate the file system. Enumerate directories, Copy/Move, write CSVs etc, etc. Kinda like Postgresql
Well I think you should heavily consider ditching SQL Server and devoting more of your time to learning Postgres.
SQLCLR is a more secure vehicle for reaching outside the engine than xp_cmdshell anyway. Go look for post exchanges between Jonathan Kehayias and Jeff Moden on the topic. I couldn’t say it better than JK did.
As for file system access from a Transact-SQL context good luck ever migrating your code to the cloud (private or public).
At the end of the day we all have ways we know work and ways we prefer to work. I’ve done plenty of sweeping up (I.e. eradicating) after xp_cmdshell lovers hence my viewpoint. Allowing xp_cmdshell to be used opens you up to more security risks than an environment where it is not used. SA abuse is to be considered too. Non-SA abuse comes from the proliferation of its use and the lack of traceability around who did what and when. Malicious use by a non-SA must be done by combining multiple attack vectors. My point is xp_cmdshell is one possible vector so not having it in place is better than having it.
so…still no examples!
FWIW I’m still agnostic on the question. An exploit example would certainly swing me to your POV. As mentioned earlier though, any sysadmin can enable it anyway. The best you can do then is audit that change and any uses. You can lock it out from users, which any sane DBA would do I think.
As for SQLCLR, since basically you’re exposing the full power of .NET, that’s a lot of power! Also note that both run under the sqlserver account, (IIUC) and are thus restricted to whatever permissions are granted to that account.
I’m afraid ditching SQL Server for Postgresql is not an option where I work (30,000 servers in a heavily-regulated industry). For similar reasons, the only cloud we’ll ever see is an on-premises cloud.
On a side note Gerald, now I’m DYING to get you on a Minion Enterprise trial. 30,000 servers is a lot of servers to manage.
Hey Jen, If I had enough influence, I’d push for such a trial. Alas, I’m just a cog in a 160-year-old machine
…. this blog outlines exactly how it’s not a security risk. And you’re on an SQL Server blog, directing people to Postgres. I don’t imagine you’re going to get a lot of traction here.
Orlando, You’ve got such a bias you can’t even see past your own failed logic.
1. Switching to Postgres isn’t a solution to a SQL Server problem so it shouldn’t even be brought up. Playing high and mighty with a much lesser DB isn’t going to get you anywhere.
2. You still haven’t given us any way to actually prevent cmdshell from being used. Admins can turn it on at will. So what do you suggest?
3. You still haven’t presented a way that non-admins can circumvent the security. Any actual technique has only been hinted at and not provided. I don’t believe in vague “this could happen somehow” arguments. Give an example or let it go.
4. Any attack vector you’ve discussed always boils down to the rest of the server not being secured properly… like the service acct having too many rights, etc. These are base-level security holes that can be attacked a number of ways. And yes, cmdshell is one way you could attack a vector like that, but only if you’re an admin, and an admin could do those things anyway… so where’s the security hole? The point is to fix the security and be more secure. Not pick one single place where your bad security can be exploited and call and end to the use of that one single element. It’s a ridiculous argument that ignores the underlying security issue. There are many other ways you could exploit those base-level security holes, so don’t just pick on one because you’ve been told for years, by people who don’t understand it, that it should never be used.
5. Using cmdshell sets a bad tone? That doesn’t even mean anything. Again, there’s no security risk if your server security is what it should be, and it can’t be compromised by non-admins. And only admins, who can do anything in there anyway, can use cmdshell and they can turn it on/off at will. So what tone are you talking about?
The bottom line is that if you really understand SQL security, and stop comparing it to a much lesser product like Postgres, then you’ll see that your argument is based on longterm fear and not actual fact.
And finally, again… provide specific attack code that we can verify or stop bringing it up. Because honestly, you’re just arguing yourself into a corner now. Stop making vague accusations and give testable code. Or at least give an actual attack that we could code ourselves. I don’t care for the fear mongering or the security theater. I don’t want the appearance of security and nothing you’ve said does anything for the actual security of the system. It only makes you feel better that you’ve done something.
You are too much 🙂 Gerald brought up Postgres. I simply riffed off him.
Sean, I’m mobile so I’ll address your concerns in total later although I think I just addressed one. To fire back on one other point, you say I can’t see past a bias. Bias maybe, but rather well informed bias. I’d argue it’s bothering you in as many or more ways that I would question such a fundamental choice of your software that you are having a hard time accepting the point I am making. At any rate, we are spending lots of energy here. I don’t doubt you know what I’m getting at. You’re actually alluding to it in your #4 and #5 points so I know you get it. I don’t have to show specifics techniques to display the logic in reducing surface area for attacks, of which xp_cmdshell is one.
As for SQLCLR needing to run under the service account you’re wrong about that. It can pass the identity through the engine which is what makes it a more secure option than xp_cmdshell. I doubt that knowing this will sway your overall view but thought you should know.
“It can pass the identity through the engine” good to know! I suppose I would use that to *increase* the permissions for that CLR from those granted to the service account. But then, I can achieve the same effect using cmdshell and runas, I think.
Though Sean is surely correct about configuring the service account properly in the first place.
Orlando, the problem with the point you’re making is that it’s all all boots no cattle. Without a specific exploit example that can be executed by an end-user (=non-sysadmin), it’s the same as claiming that New Hampshire bused in millions of illegal voters, or that climate change is a hoax, or that the world is really just 6000 years old.
You’ve had almost a year! Surely something must have come to mind by now?
About the private cloud: For some of our cloud vms, we do allow network connections to other on-prem boxes, both real, and virtual. So migration hasn’t been an issue for us.
since our primary use-case for cmdshell is file system manipulation
> “It can pass the identity through the engine” good to know! I suppose I would use that to *increase* the permissions for that CLR from those granted to the service account. But then, I can achieve the same effect using cmdshell and runas, I think.
One pickup on terminology, maybe sematics not sure, but you do not grant permissions to the CLR. The thing running the session would pass through the call to a SQLCLR object and be used for manipulating the file system the same way say a principal running a batch script would appear to be the one manipulating the file system should that batch file contain file system manipulation commands in it. With xp_cmdshell the chain of who did what is broken. Yes you can initiate a new chain using runas.exe however the password on the command line, and therefore in an Trace, Audit or Extended Events session, would be in plain-text which I would look to opt out of.
It actually doesn’t bother me at all that you’re questioning a fundamental of how my software works. That’s what these discussions are for.
As for an attack vector, I’ll wait until you get more stationary to tell me how an end user is going to jump into sysadmin to gain access to cmdshell. Barring that, I’m waiting to hear exctly how it’s a risk to turn it off when any sysadmin can turn it back on.
You’re missing the fundamental point of it…where is the exposure? A sysadmin can turn it on, and non-admins can’t become admins by themselves. So code up an example of how that’s going to happen. If you can’t, then again, where’s the exposure coming from?
To Sean and Gerald, it’s not about the straight-line attack so there won’t be a code example forthcoming. I think you know me well enough to know that I know that’s not an option so you can do away the strawman arguments. I also know you’re just being dodgy on this point, i.e. that you know it’s not as simple as coding up an example to prove how a non-SA can magically become an SA without the help of something like xp_cmdshell itself or some other vehicle. Consider poor code review practices, poor deployment practices, lack of division of responsibility, service accounts with too many permissions, environments with Linked Servers, designs where SQL-Server-to-SQL-Server file system communication is necessary, etc. etc. Point being, I could construct hundreds of examples of how xp_cmdshell could be exploited in an environment where a multitude of other poor security choices were also made. If you will not concede that xp_cmdshell is a valid attack vector then what is the point? I am an confident you can construct some of these scenarios on your own. Further to that, if you will concede it, which you should and if you don’t you’re willfully ignoring the threat, then finally we will be getting somewhere. Either way the energy spent has arguably gone too far, but it’s OK, I enjoy these discussions. Talking about the problem at least puts some light on it and if others are not weighing in at least they see the points being made and can make an informed decision in their specific environment.
We never once said that cmdshell couldn’t be abused in the ways you just outlined. What we said, many many many MANY times is that disallowing it doesn’t increase security since the only ones who can run it can just turn it back on again. Surely you can concede that point. And if you can, then you have simply to answer the question we’ve been asking again and again and AGAIN… and that is… if the only people who can run it, can also turn it on and off, how does keeping it off increase security?
Answer that question. If the next words out of your mouth are not a direct answer to that question then you’re dodging. I don’t want to hear about attack vectors or bad vibes, or lack of security elsewhere. I don’t want to hear about CLR or improper tone, or separation of duties, or linked servers, or anything else. I want to hear the answer to 1 question and 1 question only. And I’ll repeat it so you don’t have to go hunting for it.
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
Because that was the crux of the article, and that’s our ENTIRE point. So answer that question before we can move on.
And BTW, that’s what we wanted repro code for. We wanted you to repro a case where turning off cmdshell actually increased security by not allowing the only people who can use it to simply turn it back on. So since you can’t repro that case, then simply answer the very simple question I’ve asked above or we’re done here.
Could I construct a vulnerable server where cmdshell could be used as an attack vector? Undoubtedly. Could I construct such a server where cmdshell was the only possible such vector? I’m not so sure I could. I’m rather of the opinion that I would have opened up so many other holes the defense would resemble Swiss cheese.
The point is simple. cmdshell is no more a threat than SQLCLR or SQL injection or PoSH or SSIS with script tasks and components, etc. In fact I think it is less, probably much less. As long as it is never opened up for end users, that is. DBAs with sysadmin privs, as has been said, can use it any time they want to anyway and there is currently no way to stop that.
Since you’ve provided no POC code that would penetrate a sensibly-set-up server that also has xp_cmdshell enabled, I can’t think of any, and smarter folks than me haven’t either, I’m going to assume that such an exploit does not and cannot exist.
The discussion has been useful for me. I’m now quite confident that I can enable the option and use it appropriately without losing any sleep over it. So, for that matter, can other sysadmins. In our large organization we trust them but audit any suspicious activity. That fully satisfies our paranoid auditors as well as government regulators. We probably won’t ever catch every malicious or accidental breach. But then, who can?
Note the SQLCLR bit was for Gerald.
Also for Gerald, when I say private cloud and you say on-premises cloud I think we’re saying similar things.
Sean, not sure how else to lay it out for you so we’ll just have to let things stand where they are. Bottom line is that I believe that allowing the use of xp_cmdshell in an environment can decrease overall security. I have made the case and you are unwilling to accept the state of affairs, and that’s OK. I am not trying to win you over. I don’t have to. I have no stake in what you do personally but I do want people to be informed of the options and this article, in my opinion, is irresponsible advice to the community. If you do not fully qualify the ramifications of compelling your customers to enable a dangerous tool like xp_cmdshell you’re doing them and yourself a disservice.
Orlando,
Again, you’ve answered a direct question with “I feel”. I didn’t ask you how you felt. I didn’t ask you anything except the very simply question that I repeated more than once.
It’s quite easy to convince me of your point if you just answer the question. Here it is AGAIN. Answer it and I’ll come over to your side.
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
That’s the only question on the table. You keep telling us how well-informed you are on this topic and how much experience you have with it, so this should be an easy question to answer. And if you can’t answer it, then you either don’t have the experience you say you do, or your argument is based solely on your feelings, and not on actual fact.
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
So give a real fact-based answer to that question and you’ll convert me right away. I suspect Gerald will come along too.
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
Show us why we’re flawed by answering that simple question. Again, if you know what you’re talking about so much then you should have no trouble telling us why. I mean, I can tell you why SELECT * is a bad idea. I can tell you why SPs are a great idea. I can tell you why varchar(max) is better than text. And I can tell you why my stance on cmdshell is what it is. But you give me feelings, and conjecture, and vague security scenarios. When it all really hinges on a single question. Do you know what that question is?
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
So teach us. What big piece of logic are we missing? Where did I go wrong?
If the only people who can run it, can also turn it on and off, how does keeping it off increase security?
Gerald, you said “Since you’ve provided no POC code that would penetrate a sensibly-set-up server that also has xp_cmdshell enabled, I can’t think of any, and smarter folks than me haven’t either, I’m going to assume that such an exploit does not and cannot exist.”
When you provide every last detail of the environment you described as “sensibly-set-up” including all aspects of the production server environment, the SDLC leading up to deploying code to that environment, the release process that gets code into the environment and the support model that keeps the environment healthy I’ll be happy to provide such a POC. Don’t forget to provide details about all the various review and approval processes as well.
Can you construct an environment where xp_cmdshell can be enabled that will pass muster with auditors, of course, I have lived in those environments. Can you quantify how much work that is and how to avoid abuses of xp_cmdshell? Maybe, but there are much better tools out there to get the job done.
One other misconception I think I picked up, you can run SSIS Packages on a stand-alone machine, i.e. one not hosting a SQL Server instance. Does that allay some of your concerns around Script Tasks accessing the file system? SSIS just offers us another programming environment outside the engine like PoSH or C# and therefore a more secureable solution than xp_cmdshell.
Simple example:
Sensible: SQL Server Service account limited to only what’s strictly necessary (e.g. file system access, required system services etc.). xp_cmdshell limited to sysadmins (who can of course, turn it on and off at will.)
FWIW we *do* run SSIS on stand-alone machines. And I never did have concerns about that for the same reasons I’m not concerned about cmdshell. The service accounts adhere to the principle of least privilege.
Is there a development instance in the environment?