The Command Shell

Question

Post reply

The Command Shell

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

TravisDBA SSCoach Points: 15780 More actions · Answer 1

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%5B/quote%5D
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. ...:-D"

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 6

opc.three (3/30/2013)
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?

Since we are, in fact, discussing the use of xp_CmdShell from SQL Server, I'm not sure why you think the discussion isn't on target. But my response would be that, considering that most apps don't pass the actual user that's logged in, the same would be true of an app that kicked off an SSIS job or if it kicked of an SQL Server job or even if it did some deletes. There's no difference there. Once again, we're talking about bad security.

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?

And, just like auditing in SQL Server, they have to be enabled and maintained. They do nothing by themselves. Once again, we're talking about bad security.

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

Heh... you should have a chat with Adam Machanic about SQLCLR. Also, someone with even the smallest bit of knowledge can easily make a self deleting job in SQL Server to call a file system related SQLCLR using the server login and there will be no trace of malious use of the SQLCLRs. Once again, we talking about bad security.

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.
..{snip}...
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.

Yes, I see the same problem with any programming language. Someone doesn't know how to use it correctly and they blow stuff up. For example, a large accidental cross join (usually a Many-to-Many join because someone doesn't know the data they're working with and left out some criteria) in SQL Server can take hours and, sometimes, days to rollback and it will have a much larger effect on SQL Server than someone who incorrectly ran a ping that never ends. This isn't a failure of xp_CmdShell. It's because someone who didn't understand how to use it did something wrong with it... just like in any other language.

In this case, we're talking about training. Someone using Powershell or SSIS could do things just as wrong and there wouldn't necessarily be any logging substantial enough to identify who did it there, either.

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.

To be honest, although this is a highly controversial and emotional subject, I am trying to keep it professional and on the subject. I consider your calling things that I've said "empty rhetoric" as personal attack. I've very much supported my position and even though I am dead-set against the postion you've take on this, I respect your position and I've not stooped to the level you just have. Knock it off, Orlando. You know better especially with me.

Getting back to the subject, yes, I agree. My statement could be applied to anything but, since this discussion is about SQL Server security, you should take it as "Bad SQL Server security".

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.

Ok. Agreed. But let me ask, where is the bar when someone is allowed to run Powershell (for example) starting from either the command line, SSIS, or an SQL Server job? Once again, it doesn't take much skill on the part of an administrator to hide their actions by creating diversion logins and self-deleting jobs/packages. Once again, we're talking about bad security and trust in admins. It has nothing to do with what you can or cannot do with xp_CmdShell or Powershell or any of the other methods for malicious activities.

You did bring up a great point, though. Along with good security, there must also be some skill and knowledge of what will happen when you do something in code. I'll also add that those points are absolutely true with any code or programming vehicle you chose to use and not just with 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Jeff Moden (3/30/2013)
opc.three (3/30/2013)
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 abhor SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it?
Since we are, in fact, discussing the use of xp_CmdShell from SQL Server, I'm not sure why you think the discussion isn't on target. But my response would be that, considering that most apps don't pass the actual user that's logged in, the same would be true of an app that kicked off an SSIS job or if it kicked of an SQL Server job or even if it did some deletes. There's no difference there. Once again, we're talking about bad security.

I am saying you are not considering multiple other aspects of "good security" and are instead only focusing on the way you may have personally, successfully leveraged xp_cmdshell in your organization to produce a solution, or at least it seems that way to me. If you have a core set of DBAs and developers and all are trusted and no one has ever breached that trust that you know of, great. What I am saying is that you are exposed security and auditing-wise much more than if you went a different route, and that you are far more limited in terms of auditing, troubleshooting as well as in programming options than if you went a different route.

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?
And, just like auditing in SQL Server, they have to be enabled and maintained. They do nothing by themselves. Once again, we're talking about bad security.

My point is that using xp_cmdshell as a mainstream development and admin tool compromises the effectiveness of those types of tools. It has a negative net effect on security.

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...
Heh... you should have a chat with Adam Machanic about SQLCLR. Also, someone with even the smallest bit of knowledge can easily make a self deleting job in SQL Server to call a file system related SQLCLR using the server login and there will be no trace of malicious use of the SQLCLRs. Once again, we talking about bad security.

I have never met Adam Machanic but have read a lot of his contributions to the community. In fact, I credit his book Expert SQL Server 2005 Development for teaching me a few things about the SQLCLR that I ran across when researching how to best develop a SQLCLR Aggregate Function that would bring similar functionality available in MySQL to SQL Server, namely the GROUP_CONCAT string aggregate for SQL Server[/url].

I am aware of the security options available within SQLCLR, and I credit a lot of that to Jonathan Kehayias. Here is one article Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url] that has a title most relevant to this conversation. There is another article, can't find it right now, where Jonathan shows how to ensure that accessing resources outside the SQL Server is done using the callers own credentials, and that if the SQLCLR object is called by someone logged in with a SQL Server Login, and not a Windows Login, it throws an error.

The SQLCLR simply outshines what is available through xp_cmdshell and I mentioned some of those points above. Namely that if you are going to access the file system from a SQLCLR object you can program it in such a way that it will pass the callers credentials through to the OS, i.e. people maintain their identities all the way through the stack. If they do not have permissions to reach a file share directly using their own permissions then they won't gain access just because they went through T-SQL to try to access that same resource that they are restricted from seeing. This differs from xp_cmdshell where the credential used is the SQL Server service accounts. I am not condoning the use of the SQLCLR this way. I actually try to guide folks into only marking their Assemblies with the SAFE permission set, but if you're going to access resources outside the domain of the database engine then at minimum you could do it in such a way as to maintain the person's identity all the way through the stack to avoid permissions elevation and obfuscation of their identity.

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.
..{snip}...
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.
Yes, I see the same problem with any programming language. Someone doesn't know how to use it correctly and they blow stuff up. For example, a large accidental cross join (usually a Many-to-Many join because someone doesn't know the data they're working with and left out some criteria) in SQL Server can take hours and, sometimes, days to rollback and it will have a much larger effect on SQL Server than someone who incorrectly ran a ping that never ends. This isn't a failure of xp_CmdShell. It's because someone who didn't understand how to use it did something wrong with it... just like in any other language.
In this case, we're talking about training. Someone using Powershell or SSIS could do things just as wrong and there wouldn't necessarily be any logging substantial enough to identify who did it there, either.

Agreed. Any language can be abused. The bar is just too low in the case of xp_cmdshell and it is too blunt a tool for me to endorse its use. We could go around and around on this point endlessly and still never agree.

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.
To be honest, although this is a highly controversial and emotional subject, I am trying to keep it professional and on the subject. I consider your calling things that I've said "empty rhetoric" as personal attack. I've very much supported my position and even though I am dead-set against the position you've take on this, I respect your position and I've not stooped to the level you just have. Knock it off, Orlando. You know better especially with me.

The generalization is what I meant to attack, not you Jeff. My sincerest apologies that you took it personally.

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.
Ok. Agreed. But let me ask, where is the bar when someone is allowed to run Powershell (for example) starting from either the command line, SSIS, or an SQL Server job?

When running PowerShell or SSIS from the command line or from a development IDE it is running in the context of the persons own login. Those people that are standing up SQL Server Agent jobs for purposes of circumventing security and auditing and then having them delete themselves are subject to termination, whereas you're proposing a sanctioned tool be stood up that would allow them to do the same type of circumvention as part of normal operating procedure...I have a problem with that. If they follow protocol 99.9% of the time, great, but the option is right there for them to run with if they ever decide to run with it.

We know SQL Agent is another security issue that needs to be addressed. Ideally all job steps will run under a proxy account that belongs to the job being run so the account can only be granted permission to do the actions it needs and those actions are carried out under an account that can be traced back to just that specific process. We can have multiple proxy accounts servicing SQL Agent whereas we can only have one service account and one proxy for the entire instance servicing xp_cmdshell.

Once again, it doesn't take much skill on the part of an administrator to hide their actions by creating diversion logins and self-deleting jobs/packages. Once again, we're talking about bad security and trust in admins. It has nothing to do with what you can or cannot do with xp_CmdShell or Powershell or any of the other methods for malicious activities.

xp_cmdshell just makes it that much easier to go on undetected and there are superior options out there for getting the same, and more, done. The question becomes not why are you still using xp_cmdshell, it becomes why are you not using SQLCLR, SSIS or PowerShell (without the help of xp_cmdshell)? If it's the argument about not wanting to learn a new language, well what do you call Windows Batch? The stuff you throw at xp_cmdshell? Guiding newbies or other public forum goers towards xp_cmdshell seems akin to guiding them towards VB6 to build a new Windows Forms App or Classic ASP to build a new website.

You did bring up a great point, though. Along with good security, there must also be some skill and knowledge of what will happen when you do something in code. I'll also add that those points are absolutely true with any code or programming vehicle you chose to use and not just with xp_CmdShell.

No doubt. We can definitely agree on that point. xp_cmdshell is just too limiting a vehicle in my opinion when it comes to securing a SQL Server. It gives trusted admins that want to do untrustworthy things too much cover. In my opinion the bar is higher with some of the other tools mentioned, not to mention they are far more stable, robust and offer more in the way of programming flexibility when it comes to troubleshooting and maintaining auditability.

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 8

Ok. So let's ask another question. To keep it simple, you've either been given administrative privs on a box or you are the administrator. You fire up PowerShell and drop hundreds of files from a file vault. What is logged?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Jeff Moden (3/30/2013)
Ok. So let's ask another question. To keep it simple, you've either been given administrative privs on a box or you are the administrator. You fire up PowerShell and drop hundreds of files from a file vault. What is logged?

It depends on what type of audit software is in place. You have the same options for auditing deleted OS files when the delete is initiated by PowerShell as you do when it is initiated by xp_cmdshell, except in the PowerShell scenario the logged event would show the actual user that initiated the delete and the xp_cmdshell event would appear to have been done by the SQL Server service account. If you try auditing the database activity in conjunction then I can show you a quick hack using a global temp table where the contents of the command sent into xp_cmdshell are hidden from the Trace.

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 10

Then I have to admit, points in favor of PowerShell but only if such auditing software is actually inplace, active, and that, you as an administrator, can't defeat the software or modify the logs. Lord help the company if the administrator is in cahoots with the person that can.

I am curious, now. Do you personally run PowerShell from SQL Server Jobs at all? If not, where do you personally run it from?

With the understanding that I'm "stuck" in the world of SQL Server 2005 for the time being, if you run PowerShell using a PowerShell Task in an SQL Server job, what does it run as?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 11

Both of you make very good points but I'm still going to keep using cmd shell if I need to and not if I don't. The position to not sounds to me like those who think nobody should own weapons. What if... Risk vs. Reward is what is important.

Cheers

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

I will lay my cards on the table: I am in agreement with Jeff Moden.

On a properly secured server, disabling xp_cmdshell provides so little security gain (as it can be re-enabled) that it is dangerous for people to keep saying "disable xp_cmdshell for security".

I have just re-read this topic : If you could use xp_CmdShell securely, would...

It contains pretty much the same content as any other discussion on this subject, namely people just saying "I disable it for security".

There seems to be a lack of evidence to prove the point that disabling xp_cmdshell would improve security, but lots of phrases such as "layering is important" as a general justification.

I believe that no-one said that "disabling xp_cmdshell" is a bad idea, but saying "disable xp_cmdshell for security" does seem to be a bad idea,

just because it can lead to a false sense of having made the system secure, when it has made very little difference.

So, IMHO, go ahead, disable it if it makes you happy, but don't think it has had a magic effect on the security of your system.

If anyone does have any evidence of disabling xp_cmdshell having stopped a sysadmin from doing bad things, it would be interesting reading (just so I can laugh at how puny the sysadmin's skills were 😛 )

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 13

jfogel (3/30/2013)
Both of you make very good points but I'm still going to keep using cmd shell if I need to and not if I don't. The position to not sounds to me like those who think nobody should own weapons. What if... Risk vs. Reward is what is important.

I'm of the same ilk and I definitely agree with that. To wit, since I'm still "stuck" with SQL Server 2005, I use xp_CmdShell to call PowerShell. 😀

But I also want to understand why some people are so against using xp_CmdShell. My take is that turning it off still provides no additional security (ok... maybe a thin veil but it only keeps the honest man honest, IMHO). With that thought in mind, I say "why not use it" and opc.three is trying to explain some of his reasons as well as some alternatives.

Without some form of tamper-proof OS level auditing, PowerShell doesn't look any more secure than xp_CmdShell when it comes to logging actions such as deleting files, but that may be the difference between good security and great security. I personally know of no one that's taken such a thoughtful step, though.

And that's the real nature of what I've been trying to stress. Having xp_CmdShell turned on or off isn't going to help security one bit because a first year Junior DBA or a casual user that has "SA" privs, not to mention an attacker that gets in with "SA" privs, can get to the command line from SQL Server without going anywhere near xp_CmdShell and they can do it totally anonymous fashion. I could certainly be wrong but it currently appears that unless someone has taken the extra step of OS level auditing or some other step that I don't know about the likes of PowerShell, then xp_CmdShell doesn't appear to be any more dangerous than letting someone use PowerShell.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 14

As a production DBA, I certainly have a responsibility but those who control the layers above do, too. I have layers to protect weapons while keeping them at close reach when needed. Same for all else. I'm more concerned about other methods of a breach because I've seen them first hand.

Cheers

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 15

Jeff Moden (3/30/2013)
Then I have to admit, points in favor of PowerShell but only if such auditing software is actually inplace, active, and that, you as an administrator, can't defeat the software or modify the logs. Lord help the company if the administrator is in cahoots with the person that can.

That's just the thing. The "only if" is specific to ones environment and is irrelevant to the point. With xp_cmdshell, you do not even have the option to properly audit what is happening in your environment. General advice that xp_cmdshell is safe is irresponsible in my opinion, especially on a public forum. Someone of your notoriety should expect that a SQL-mortal might just haul off and run with back to their developer meeting to justify its use.

The idea that xp_cmdshell is harmless came back to bite more than one of the shops I have contributed in. Building up processes around xp_cmdshell, anything from db admin scripts to an entire ETL framework that provided data movement options from soup to nuts all leveraging xp_cmdshell, can eventually become a very expensive refactor job because it paints you into a corner with security and auditing.

The argument "well any sysadmin can just enable it and takeover the instance so why bother disabling it" is completely missing the point.

Also consider the version of DBA that is a member of sysadmin but actually does not have the service account password, nor the ability to Remote Desktop or otherwise reach a command prompt on the host OS. You say you could reach a command line without xp_cmdshell. Mind showing how to do that because I think I know what you mean, but the loophole looks to have been closed in SQL 2008 and above.

I am curious, now. Do you personally run PowerShell from SQL Server Jobs at all? If not, where do you personally run it from?
With the understanding that I'm "stuck" in the world of SQL Server 2005 for the time being, if you run PowerShell using a PowerShell Task in an SQL Server job, what does it run as?

I personally run scripts from PowerShell ISE, in the security context of my own Windows Account.

As for automated jobs, the current shop is mostly 2008 R2, some 2005 still around, and some 2012 coming online. Many of the PowerShell and VBScript scripts are run from Windows Task Scheduler where each job can have its own account running it. They operate on the databse as a client app would and interact with file shares but never touch the file system of the OS the SQL Server is running on, and that is the preferred way to go in my opinion.

Some scripts are also run from SQL Agent on the 2008 R2 instances but not using the PowerShell Step Type, using powershell.exe in a CmdExec Step Type. I would like to use proxies for all those CmdExec step types but that's an effort that takes a lot of explaining and justifying to management on why we would should change the way things are currently done...very similar conversation to what we're having here, but I am making progress.

You could run your PowerShell from SQL Agent in your 2005 instances as well and is how I would recommend it in 2008/R2 anyway given some shortcomings in the PowerShell Step Type (reference mini-shell). I have not played around with PowerShell Step Types in SQL Agent 2012 to see if they went away from the mini-shell but think I remember thinking that it was supposed to provide a better experience now.

In a previous shop all the ETL processes were kicked off from an Enterprise Job Scheduler, i.e. SQL Agent was not used at all. Each job had its own Windows Account that it ran under and that account only had permissions to the resources it required: access to specific file shares, exec perms on procs, etc.

Ideally you should run each of your jobs under a distinct security context so they can adhere to least privilege. We tell the front-end developers their application service accounts cannot have db_owner privileges, let alone sysadmin privileges, when they ask for access to the database to run their apps yet most DBAs are content to run all the backend ETL and admin jobs under the same service account, which also by the way happens to exist in the sysadmin Role and have access to instances and file shares all over the environment. Implementing xp_cmdshell limits our ability to segment these things out so I say it is a bad option and should not be a recommended tool.

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