Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The Command Shell Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 6:38 AM
Points: 3, Visits: 35
I have XP_CMDSHELL enabled on my staging server only. It enables me to dynamically load a large number source files with various naming conventions and file structures. Like a knife, a useful tool, but should be handled with caution.
Post #1436995
Posted Friday, March 29, 2013 9:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:47 AM
Points: 371, Visits: 964
That is a reason I wont simply dismiss the use of cmd shell. My PS function is called from a job and it all worked out for me but there are other projects where I've had to write a report for a client and they want to get the results in to Excel with little to no work involved on their part. These were Crystal reports and they had to look pretty and be able to export the results in to Excel (in a down and across format) which is near impossible. My solution is to use BCP and cmd shell to export the data with headers to a file and call send mail to shoot it off to the recipients provided in a user populated parameter. Works great and the clients think it is slicker than snot.

Your prior response to a post articulated well what I was trying to say in the closing of my initial post in this thread. I don't want to be or include .NET developer(s) when all I want to do is something simple. I don't want to activate CLR or add all kinds of other components just because I can and I don't want developers doing it, either. cmd shell is mature, included and known. All others should be treated with a high level of suspicion just as some treat cmd shell.


Cheers
Post #1436996
Posted Friday, March 29, 2013 9:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
Steve Jones - SSC Editor (3/29/2013)
jfogel (3/29/2013)
I agree that if you are vigilant with security then xp_cmdshell is just fine and I've used it for years. However, over the last year or so I've started to turn away from it and not for security reasons. I've been trying to challenge myself to avoid it and find another way to accomplish a task that I would typically use it for. As an example, I setup an agent job recently that compresses some files and copies them to a DR Site and normally I'd use cmd shell to compress the files using PKZip or something but this time I used a powershell function. I didn't want to install any compression program on the production database server and I wanted to avoid a call to cmd shell and I was able to avoid both.

There is a limit to how much effort I'll go through to avoid it, though. I can always use the powershell compression function in other tasks but if I were to find myself having to create all sorts of things to replace the functionality of cmd shell then to me it means making a mess and reinventing the wheel.


I would say Powershell is better if you can call things from a job, but you can't necessarily do that from T-SQL. That's a bit of an issue. I wish we had a ExecPS('') function that would allow calling cmdlets. Course, XP_CMDSHELL does it just fine.


BWAAA-HAAAA!!!! I CALL PowerShell using xp_CmdShell!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1436998
Posted Friday, March 29, 2013 10:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
TravisDBA (3/29/2013)
Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Tim Burleson wrote a very good article with a real fine example of this that is well worth reading.

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm


I did mention "DOS Injection" that in my first post on this thread but it's absolutely worth mentioning again. Thanks, Travis.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1437000
Posted Friday, March 29, 2013 10:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
TravisDBA (3/29/2013)
Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Don Burleson wrote a very good article with a real fine example of this that is well worth reading.

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm


That's a very real hole, and it's one you should beware of. So administrators running things through XP_CMDSHELL shouldn't run anything they do not completely understand, including the parameters.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1437009
Posted Friday, March 29, 2013 10:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
Steve Jones - SSC Editor (3/29/2013)
TravisDBA (3/29/2013)
Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Don Burleson wrote a very good article with a real fine example of this that is well worth reading.

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm


That's a very real hole, and it's one you should beware of. So administrators running things through XP_CMDSHELL shouldn't run anything they do not completely understand, including the parameters.


I agree Steve, it is, and I would add that if Mr. Burleson says the command is dangerous, then I would definitely listen.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1437018
Posted Friday, March 29, 2013 10:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 1,651, Visits: 4,709
I understand why xp_cmdshell would would be disabled in a non-dedicated environment hosted by a 3rd party. In that case there is a clear distinction between the database owner and the sysadmin.

I personally use xm_cmdshell to do things like get free disk space, copy files, manage windows accounts, etc. That comes in handy when I don't have Remote Console access to the server. This is in the development and QA environment though. I'm not a production DBA in my current job.
Post #1437019
Posted Friday, March 29, 2013 4:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 7,098, Visits: 12,604
Thanks for the post, Steve. I'll chime in since I have a feeling I know where the idea for this Editorial came from

I think xp_cmdshell should be disabled, period, and there should be additional controls in place to block and audit attempts to enable or use it. I agree with the previous poster Antares686 wholeheartedly....layering in your security strategy is fundamental. Disabling xp_cmdshell is but one layer.

On such a controversial, deep and broad topic as "securing your SQL Server", and how daunting it can appear to be, xp_cmdshell is but one attack vector. All vectors deserve to be addressed and disabling xp_cmdshell as well as putting a few additional controls in place in an attempt to prevent it from being enabled (including Policy Based Management to prevent it from being enabled and possibly dropping the XSP itself) are well worth the effort and can be added to an "instance setup" script so it won't be much effort. I am thinking of publishing my PBMs but need to do some additional testing with dropping the XSP before going public with that.

I also like what jfogel said about challenging himself to work without xp_cmdshell. With so many great tools being released since SQL 2000, including .NET, SSIS, and especially PowerShell, we as database and system administrators really have a lot of other much more robust options avaiable besides xp_cmdshell.

It's hard to tell if the writing is in fact on the proverbial wall for xp_cmdshell because nothing official has been released from Microsoft, but Extended Stored Procedure Development has been officially deprecated in favor of SQLCLR Development and Microsoft has been slowly dropping their own Extended Stored Procedures from their product. I for one hope xp_cmdshell is one day deprecated, and eventually removed from the product. At minimum I would like for a way to force a true install of it so that it could not be simply enabled using sp_configure, and instead would require the installation media to be used to bring that feature online. That would put to rest this idea that there is no use in disabling xp_cmdshell because a sysadmin could easily re-enable it.

In addition to the various security concerns I and others have mentioned, and thanks to TravisDBA for highlighting "DOS injection", there is also the problem of auditability. xp_cmdshell does support a proxy account, but only one for the entire instance. This means that anyone using xp_cmdshell as a non-sysadmin will appear as the same person to the host operating system which can pollute auditing and make troubleshooting very difficult. In the case of sysadmins you have the same auditing problem except the host operating system sees the cmd shell process as being invoked by the SQL Server service account. This could amount to permission elevation. Now, you may say a sysadmin could gain elevated permissions via a SQL Agent Job or a SQLCLR object and you would be right, but that's why security is an exercise in eliminating possible points of attack and layering your defenses. I look at everything and everyone as a possible security threat to the systems and data I manage and no matter how I slice it xp_cmdshell brings with it a negative net effect on security and auditability whereas disabling xp_cmdshell brings with a positive net effect, no matter how small some folks may argue that net effect may be. So for me, xp_cmdshell simply doesn't make the cut. I say it should be disabled on all instances and measures should be taken to keep it that way.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1437132
Posted Saturday, March 30, 2013 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
That's part of the misperception. For example and just like xp_CmdShell, there's no natural auditability or logging (and I'm not talking the LDF file) if someone deletes rows from a table. And while the tools mentioned are good ones (.Net, SSIS, etc) and I encourage their use if that's your nature, they add a fair bit of complexity to what should be simple database or operating system tasks. Some of the tools, like SSIS, require not only more complexity in the form of learning another "language", but they also provide yet another attack vector and, unless programmed to do so, don't do any more logging than xp_CmdShell does.

Also, one of the key points of the argument to turn off xp_CmdShell that people seem to dwell on is that turning it on is logged. Like I said before, if that happens due to an attack, whether it be from the inside or the outside, it only provides written testimony that your basic security is woefully lacking.

If your system is secure, only the right people and things will be able to use xp_CmdShell. Let's concentrate on the real problem because if your system is not secure and whether you use xp_CmdShell or not, the wrong people will turn it on and use it or some workaround (and there are a great many such workarounds) to cause great damage or "just" steal your secrets on an ongoing basis without you ever knowing it. To wit, instead of being an effective layer of security, turning off xp_CmdShell and not allowing the DBAs to use it is like putting a very thin veil over rotting meat and the flys can easily get around or even through the veil.

xp_CmdShell isn't a hole in security. Bad security is the only hole in security.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1437198
Posted Saturday, March 30, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 7,098, Visits: 12,604
Once again you're focusing in too narrow a field and only considering SQL Server auditing, and only a piece of that to boot. What about the folks that kick off an SSIS package, or a bcp command for those that abhore SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it? Consider if data were being loaded to a table with an audit column that defaults to use one of the system functions to pickup the name of the login running the process, e.g. ORIGINAL_LOGIN. We won't see any distinctions because everything appears as if the SQL Server service or proxy account did it. In a malicious scenario someone could use xp_cmdshell to cover their tracks by updating data while knowing a trigger will mark the row as having been updated by the service account's Login. Also consider that there are auditing tools outside SQL Server that can track cmd shell activity. If everything appears as if it is occurring under one service account then where is the auditability?

I am the first to admit that enabling and using SQLCLR is also a security risk, and pretty much most any feature of SQL Server to be honest. I would like for Microsoft to take the same approach with SQLCLR (and OLE Automation, and some other things too) as I mentioned with xp_cmdshell, no, not deprecate and eventually remove it from the product, but make adding it to the product an explicit installation requirement. In the case of SQLCLR I would take it a step further and force the installation of the various permission sets as well so we could choose only to install the option to promote assemblies marked SAFE, EXTERNAL_ACCESS or UNSAFE, or any combination. Now, if you must access the file system use SQLCLR for goodness sake. At least then you can have the credentials of the user making the call from T-SQL (must be a Login based on a Windows Account) pass through to the OS and eventually other external resources as well. This requires a bit more setup but then everyone is operating with their own credentials and can only access what they should be able to access, i.e. no chance of permissions elevation or obfuscation or credentials that would compromise auditing.

Now let's consider the case where a SQL session initiates the use of xp_cmdshell and that process becomes hung up in the OS. Here is a good way to test it:

1. On a test instance where xp_cmdshell is enabled. you are in the sysadmin Role and you also have Desktop access to the host OS so you can see the Windows Task Manager, open a T-SQL Query Window and execute this:

EXEC sys.xp_cmdshell 'ping 127.0.0.1 -t';

2. Now hop over to Task Manager for that machine and notice that there is now a process in Task Manager for cmd.exe running as the SQL Server service account:



3. Now go back to your Query Window and try to kill the session. On my SQL 2008 R2 instance this results in 'Cancelling Query...' in SSMS, and nothing happening...not good



4. So let's open a new Query Window and try killing the session. We are a sysadmin so that should be no problem:

KILL 53

Command(s) completed successfully.


Excellent...but when we check the session to make sure it is gone you'll see nothing was killed at all.

DBCC INPUTBUFFER(53)

Still shows we are running xp_cmdshell. Go back to your original Query Window and double check...nope, still going. Now what? Well, we will need to log into the OS and kill the cmd.exe process to get out of this one.

5. Remote Desktop to the machine if it's not your machine your local testing on, open Task Manager and let's look at the list of processes. There it is...amongst many others...so which one should we kill?



See any problems with this?

It happens, quite often at one shop where I did some work, and is yet another reason xp_cmdshell should be avoided. In the case of such a condition the typical response is to kill the SQL Server session that made the call to xp_cmdshell. Sometimes this works but other times this results in nothing happening as shown above. If the session does happen to end sometimes there is then an orphaned OS process which will be executing potentially indefinitely using CPU, I/O and occupying memory. In order to remedy that situation you would need restart the server or log into the OS to find and kill the orphaned OS process. However as we showed above that is not always the simplest or most straightforward affair. In one shop where xp_cmdshell was used extensively there could literally be dozens of cmd.exe processes running that were spawned by the SQL Server service account.

xp_CmdShell isn't a hole in security. Bad security is the only hole in security.

To be fair you could substitute anything in place of xp_cmdshell in your sentence and it would ring true, so it's just empty rhetoric. The real issue is: how well does xp_cmdshell facilitate the enacting and maintaining of good security? ...not very well. I would argue that in most cases, even when it is implemented well, the bar is so low in terms of how sysadmins and folks that know how to make themselves sysadmins can take advantage of xp_cmdshell that it is in fact a security risk.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
cmd1.jpg (212 views, 11.52 KB)
cmd2.jpg (212 views, 41.28 KB)
cmd3.jpg (213 views, 32.43 KB)
Post #1437201
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse