Disabling xp_CmdShell … Is it Really a “Best Practice”?

1. Disabling xp_CmdShell…Is it Really a “Best Practice”? by Jeff Moden SQL PASS in Detroit, June 2013

2. Disabling xp_CmdShell…Is it Really a “Best Practice”? Or Are You Just Missing Out on One of the Most Powerful Tools There is in SQL Server? by Jeff Moden SQL PASS in Detroit, June 2013

3. Your Speaker - Jeff Moden • 17 years experience working with SQL Server • Mostly Self Taught • One of Leading Posters on SQLServerCentral.com • More than 32,000 posts (heh… some are even useful) • 30+ articles on the “Black Arts” of T-SQL • http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/ • Member since 2003 • SQL Server MVP 2008 thru 2013 • Winner of the Red Gate “Exceptional DBA” award for 2011 • Sr. System/Application DBA, Data Architect, and SQL Mentor for Proctor Financial, Inc. • SQL Server is both my profession and my hobby (Yeah, I know… I need to get a life ;-) Disabling xp_CmdShell... Is it Really a "Best Practice"?

4. Introduction New Doubts

5. Teaser XP_CmdShell is a powerful tool that can greatly simplify the life of DBAs. You can quickly and easily write full ETL systems, up/down-load FTP files, call PowerShell scripts to interrogate the status of hard-disks across the enterprise with nary a 3rd party tool in sight, and much more. And it can all be done under the control a scheduled job system that you already know and that keeps its own logs. Yet, you'll find millions of people that agree that disabling xp_CmdShell is a best practice. Some even say that it should never be used. Why? Disabling it supposedly decreases the system "surface area" of possible attack and never using it supposedly solves the problem of someone elevating their privileges with it. Well, surprise! Disabling xp_CmdShell doesn't actually solve any of those problems. In fact, disabling it may actually hurt security. Come to this session to find out why turning off xp_CmdShell may be a "worst practice" and how to allow individuals/applications to run it safely. Disabling xp_CmdShell... Is it Really a "Best Practice"?

6. Start of a Doubt • Began search for White Papers to show why having xp_CmdShell enabled was a bad idea. • Found WPs but none of the “official” documentation ever said “Don’t use it”. Disabling xp_CmdShell... Is it Really a "Best Practice"?

7. An Example of What I Found • Example White Paper • http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx Disabling xp_CmdShell... Is it Really a "Best Practice"?

8. Never Said “Don’t Use It”. • “xp_CmdShell” found in 4 places in that reference… • “xp_cmdshell availability” • “xp_cmdshell- executes a command in the underlying operating system” • “Disable xp_cmdshellunless it is absolutely needed.” That’s not the definitive answer I was looking for. Disabling xp_CmdShell... Is it Really a "Best Practice"?

9. The 4thPlaceNever Explained “Why” a “Risk” “Because some system procedures interact with the operating system or execute code outside of the normal SQL Server permissions, they can constitute a security risk. System stored procedures such asxp_cmdshell or sp_send_dbmail are off by default and should remain disabled unless there is a reason to use them. In SQL Server 2005 and above, you no longer need to use stored procedures that access the underlying operating system or network outside of the SQL Server permission space. SQLCLR procedures executing in EXTERNAL_ACCESS mode are subject to SQL Server permissions, and SQLCLR procedures executing in UNSAFE mode are subject to some, but not all, security checks. For example, to catalog a SQLCLR assembly categorized as EXTERNAL_ACCESS or UNSAFE, either the database must be marked as TRUSTWORTHY (see Database Ownership and Trust) or the assembly must be signed with a certificate or asymmetric key that is cataloged to the master database. SQLCLR procedures should replace user-written extended stored procedures in the future.” Set a database to TRUSTWORTHY? You CAN’T be SERIOUS!!! Disabling xp_CmdShell... Is it Really a "Best Practice"?

10. More New Doubts • “Use only if needed”? • If disabled, who can turn it on? • Can an attacker turn it on? • Am I depriving myself, as a DBA, of a great tool? • Considering the “useonlyif needed” recommendation, what does it buy me to turn it off? • Is there any reason why it couldn’t be turned on and left on? • WHAT IS THE REAL RISK??? Disabling xp_CmdShell... Is it Really a "Best Practice"?

11. Agenda • Define the Problem • Analysis • What if xp_CmdShell is Disabled • What if xp_CmdShell is Enabled • Other Concerns • Conclusion • Enable Stored Procedures to Safely Use xp_CmdShell • Setup Procs to Work with xp_CmdShell • Avoid “DOS Injection” • Q’n’A Disabling xp_CmdShell... Is it Really a "Best Practice"?

12. Define the Problem The Scientific Method

13. Hypothesis, Prediction, and Testing • I left out the Hypothesis and Prediction parts because I wanted to eliminate any preconceived notions that I may have had about the use of xp_CmdShell. • I also left out most of the “Testing” step because it’s all been tested and very well documented in Books Online and other documents. Disabling xp_CmdShell... Is it Really a "Best Practice"?

14. Given: • Given #1:The overwhelming recommendation accepted by more of the world than not is to disable xp_CmdShell for reasons of security. To wit, it is currently considered to be a "best practice". • Given #2:xp_CmdShellis an incredibly powerful and flexible tool that allows DBAs and master developers to write stored procedures and jobs to quickly and easily resolve many common DBA, reporting, ETL, and other tasks. Disabling xp_CmdShell... Is it Really a "Best Practice"?

15. The Master Question • Master Question:Does disabling xp_CmdShell provide any layer of extra security? If it does, then it might be a “Best Practice”. Disabling xp_CmdShell... Is it Really a "Best Practice"?

16. The “Negative” Path • Ancillary Question #1:Does enabling xp_CmdShell and leaving it enabled provide any security DISadvantage? If it doesn’t, then disabling it might not be a “Best Practice” Disabling xp_CmdShell... Is it Really a "Best Practice"?

17. Discover What’s Best • Ancillary Question #2:Which is better? • Disabling it and never using it? • Enabling it only when needed and then disabling it, again? • Enabling it and leaving it enabled? Note that I didn't identify what "better" means in this question because I didn't want to limit any discoveries. Disabling xp_CmdShell... Is it Really a "Best Practice"?

18. Analysis What If xp_CmdShell is DISABLED?

19. xp_CmdShell is DISABLED • Q1: IF xp_CmdShell is DISABLED, who can use it? • A1: No one. It must be ENABLED in order to use it. Disabling xp_CmdShell... Is it Really a "Best Practice"?

20. xp_CmdShell is DISABLED • Q2: Who can enable xp_CmdShell? • A2: Only someone with “SA” or “Control Server” privs. These may be system logins, application logins, logins that are members of groups with the priv, or individual logins. Disabling xp_CmdShell... Is it Really a "Best Practice"?

21. xp_CmdShell is DISABLED • Q3: Does disabling xp_CmdShell prevent logins with "SA" privs from enabling it? • A3:No. Any login that can gain access to the server with "SA" privs can enable xp_CmdShell including any attacker, either internal or external. Disabling xp_CmdShell... Is it Really a "Best Practice"?

22. xp_CmdShell is DISABLED • Q4: In reference to Answer #3, if an attacker does gain access to the system through a login having "SA" privs, does having xp_CmdShell disabled provide any level of extra security? • A4:Yes… but it appears to be trivial in nature. Disabling xp_CmdShell... Is it Really a "Best Practice"?

23. xp_CmdShell is DISABLEDObservation • Once an attacker has gained access to the server with "SA" privs, the "attack software" being used takes less than 3 milliseconds to test its state and enable it (witnessed during a "Hacking SQL Server" demonstration). • A "manual attack" (which I did myself) took only seconds more to enable it. There was nothing that prevented enabling it by any login that has "SA" privs. Disabling xp_CmdShell... Is it Really a "Best Practice"?

24. xp_CmdShell is DISABLED • Q5: Does having xp_CmdShell disabled provide any security advantages? • A5:Yes but, again, it's trivial in nature. Disabling xp_CmdShell... Is it Really a "Best Practice"?

25. xp_CmdShell is DISABLEDObservation • Enabling xp_CmdShell does do some very minimal logging to trace the event but an attacker will likely break in either as a system login (usually "SA"), via some application login, or as a human login registered on the server. • In any case, it's not at all likely that the attacker will break in as him/her self so there is no reliable "conviction" path to correctly identify the actual perpetrator. • To wit, the logging being done when xp_CmdShell is enabled appears to be of no use other than providing written post-mortem testament that security is bad and that the server has already been attacked. Disabling xp_CmdShell... Is it Really a "Best Practice"?

26. xp_CmdShell is DISABLED • Q6: Will deleting/renaming either the xp_CmdShell extended stored procedure or the related DLL prevent the use of xp_CmdShell? • A6:No. Disabling xp_CmdShell... Is it Really a "Best Practice"?

27. xp_CmdShell is DISABLEDObservation • I was really surprised at the answer to this one. During the same "hackers" dog'n'pony show previously mentioned, I witnessed the hacker implementing his own version of xp_CmdShell, which didn't even require the use of the DLL that xp_CmdShell uses. I won't post the code here but it was incredibly simple to do. Disabling xp_CmdShell... Is it Really a "Best Practice"?

28. xp_CmdShell is DISABLED • Q7: Can an attacker with “SA” privs get to the Command System (CMD) without using xp_CmdShell at all? • A7:Yes • Self deleting job with CmdExec task. • OPENROWSET with “/c CMD” Note that the OPENROWSET method requires a simple registry hack using xp_RegWrite that I’m not going to demonstrate for obvious reasons. Disabling xp_CmdShell... Is it Really a "Best Practice"?

29. xp_CmdShell is DISABLED • Q8: Is there a way to limit the amount of damage that someone with "SA" privs can do from SQL Server at the Command Line whether it be accidental or malicious in nature? • A8: Yes. Disabling xp_CmdShell... Is it Really a "Best Practice"?

30. xp_CmdShell is DISABLEDObservation • Limit the privs that the SQL Server Service and the SQL Agent Service logins have been assigned. • Reference: SQL Server 2012 Security Best Practices - Operational and Administrative Tasks • Author: Bob Beauchemin, SQLskills • Technical Reviewers: DarmadiKomo, Jack Richins, DevendraTiwari • Published: January 2012 • Link: • http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx • There IS another way that we’ll talk about in a minute but I haven’t tried it, yet. Disabling xp_CmdShell... Is it Really a "Best Practice"?

31. xp_CmdShell is DISABLEDObservation Summary • No one can use xp_CmdShell if it is disabled but… • “SA” prived logins can turn it on. • ONLY “SA” prived logins can use it. • ONLY the people that can use it can turn it on. • An attacker that gets in as “SA” doesn’t have to use xp_CmdShell to cause extremely grave damage, but could enable it and use it. • Logging of the xp_CmdShell enabling event is trivial and does not provide a clear conviction path. Disabling xp_CmdShell... Is it Really a "Best Practice"?

32. The Master Question • Master Question:Does disabling xp_CmdShell provide any layer of extra security? • Answer:Other than trivial logging, NO, it does not. Disabling xp_CmdShell... Is it Really a "Best Practice"?

33. Analysis What If xp_CmdShell is ENABLED?

34. xp_CmdShell is ENABLED • Q9: If xp_CmdShell is enabled, who can use it? • A9:Only logins that have been granted or implicitly have "SA" privs or “proxy” logins. Giving non-”SA” users privs to execute xp_CmdShell directly is a huge security problem because of elevated privs. Don’t do it! Disabling xp_CmdShell... Is it Really a "Best Practice"?

35. xp_CmdShell is ENABLED • Q10: Does enabling xp_CmdShell change the rules as to who can use it according to Answer #9? • A10:Yes but it’s a trivial change. If xp_CmdShell is disabled, the logins that can turn it on are the same logins that can use it (those with "SA" privs). The exception is those logins that have been granted usage by proxy. Such logins are unable to enable xp_CmdShell if it's in a disabled state because they aren't setup with "SA" privs. Disabling xp_CmdShell... Is it Really a "Best Practice"?

36. xp_CmdShell is ENABLED • Q11: Does enabling xp_CmdShell offer any substantial security DISadvantage over it being disabled? • A11:Apparently not. Anyone with "SA" privs can use xp_CmdShell whether it's enabled or not because it’s a trivial task to re-enable it. Disabling xp_CmdShell... Is it Really a "Best Practice"?

37. xp_CmdShell is ENABLED • Q12: Considering the answers for questions 1 through 11, are there any advantages to having xp_CmdShell disabled as opposed to enabling it and leaving it enabled? • A12:Apparently not. Disabling xp_CmdShell... Is it Really a "Best Practice"?

38. xp_CmdShell is ENABLEDObservation Whether xp_CmdShell is enabled or not, anyone with "SA" privs and ONLY those with "SA" privs can enable xp_CmdShell and/or use it (provided the proxy mistake has not been made). Only company policy would provide "prevention" of its use by internal users and ONLY if a system were set up to check if xp_CmdShell is enabled. That would require some regular audit system to examine the SQL Server logs AND that the "SA" (person enabling xp_CmdShell) didn't delete the entry from the log, which they could very easily do in an undetectable manner. Even then and as previously identified, there are methods to get to the Command Line without it that aren't logged at all (and that's a key point). All of those alternate methods are available to every login that has "SA" privs. Disabling xp_CmdShell... Is it Really a "Best Practice"?

39. The “Negative” Path • Ancillary Question #1:Does enabling xp_CmdShell and leaving it enabled provide any security DISadvantage? • Answer:Apparently not. Having xp_CmdShell enabled offers no more of a security DISadvantage than having it disabled. It would appear that the key to security has nothing to do with whether xp_CmdShell is enabled, disabled, or even deleted. It would appear that the real key is to prevent access to the system with "SA" privs. Disabling xp_CmdShell... Is it Really a "Best Practice"?

40. Analysis Other Concerns

41. Other Concerns • Q13: Are there any DISadvantages to having xp_CmdShellDISabled? • A13: Yes, there are at least 3 disadvantages. • If company policy mandates that xp_CmdShell remain disabled, only the honest DBA (assuming that DBAs have "SA" privs) will be thwarted. • Such honest DBAs will be deprived of a powerful tool (according to Given #2 above) capable of making his/her job much easier and more effective. • Because people DO think that having xp_CmdShell disabled is a security advantage, they may become more lax in the security measures they take (or don't take, as a result) no matter what they are. Disabling xp_CmdShell... Is it Really a "Best Practice"?

42. Other Concerns • Q14: Since DBAs could use xp_CmdShell to elevate their domain privs, should DBAs be allowed to use xp_CmdShell? • A14:"Yes" with some possible provisions. For example if the DBAs are trusted at the SQL Server level but not at the Domain level, then the SQL Server login and the SQL Agent login must be throttled back by the Domain Administrators to prevent such elevation of privs. SQL Server and SQL Agent should not be allowed to see more than what you want a DBA to see. Disabling xp_CmdShell... Is it Really a "Best Practice"?

43. Other Concerns • Q15: Are there better methods to do the same things that could be done through the proper and secure use of xp_CmdShell? • A15: “It Depends” and seems to be either an emotional, preferential, or resource driven decision. For me, considering answers 1 through 14, the power of the tool, the availability of a common command language understood by every DBA, and the fact that it can all be done through T-SQL (don’t have to setup other systems), I have to say “NO”. Disabling xp_CmdShell... Is it Really a "Best Practice"?

44. Other Concerns • Q16: What happens if someone is in the middle of an xp_CmdShell run and my proc disables xp_CmdShell? • A16: A command to disable xp_CmdShell will execute immediately but all instances currently running will be allowed to complete. That’s good for the “Turn it on, use it, turn it off” method. Disabling xp_CmdShell... Is it Really a "Best Practice"?

45. Policies • Do setting policies like “Prevent access to the Command Prompt” prevent usage of xp_CmdShell? • Yes, but only for the unskilled. Hackers are setup to blow right through such things. • http://www.securitytube.net/video/653 • People with “SA” privs can easily get to the registry through sp_RegWrite. Disabling xp_CmdShell... Is it Really a "Best Practice"?

46. Which is Better?

47. Discover What’s Best • Ancillary Question #2:Which is better? • Disabling it and never using it? • Enabling it only when needed and then disabling it, again? • Enabling it and leaving it enabled? Disabling xp_CmdShell... Is it Really a "Best Practice"?

48. Which is Better? • Disabling it and never using it? • Provides only trivial logging. • Provides virtually no protection during an “SA” attack. • Anyone with “SA” privs can enable and use it. • ONLY “SA” privs can enable and use it. • People “think” it provides security when it does notand may lull them into a false sense of security. • Only keeps the honest man honest. • Deprives DBAs and Devs of powerful, flexible, easy to use tools. • Does provide the maximum “Nice Warm Fuzzies” Disabling xp_CmdShell... Is it Really a "Best Practice"?

49. Which is Better? • Enabling it only when needed and then disabling it, again? • Provides only trivial logging (along with some nearly useless “Log Clogging”). • Provides virtually no protection during an “SA” attack. • Anyone with “SA” privs can enable and use it. • ONLY “SA” privs can enable and use it. • People “think” it provides security when it does not and may lull them into a false sense of security. • Provides DBAs and Devs with powerful, flexible, easy to use tools. • Have to remember in person and in code to enable/disable it every time it’s used. This means it might need to be enabled multiple times in a script, job, or stored proc. • Does provide some “Nice Warm Fuzzies” Disabling xp_CmdShell... Is it Really a "Best Practice"?

50. Which is Better? • Enabling it and leaving it enabled? • Provides no trivial logging (but helps with “Log Clogging”). • Provides virtually no protection during an “SA” attack (just like the other 2 options). • Anyone with “SA” privs can enable and use it. • ONLY “SA” privs can enable and use it. • People “think” is it’s a gaping security hole and are more vigilant and diligent in limiting privs. • Provides DBAs and Devs with powerful, flexible, easy to use tools. • Never have to remember to enable or disable it in person, scripts or code. • Biggest disadvantage is “Visceral Fear” instead of “Nice Warm Fuzzies” Disabling xp_CmdShell... Is it Really a "Best Practice"?