June 21, 2011 at 7:04 am
my only imperative reason: just compatibility, i have 7 sql 2000 servers and i need to...
but im open to use other methods, just when i start migrating all of it in a few months... i hope to not encounter compatibility issues with software legacy...
June 21, 2011 at 7:14 am
Craig Farrell (6/20/2011)
I know I'm personally horrified at the idea of having my devs write up their own xp_cmdshell with EXECUTE AS permissions as a proc for usage later by a lower-allowance login
Heh... Lordy no. I'd NEVER do it that way. EXECUTE AS is a part of the problem! It does all the horrific things that people worry about and it hides who ran it. Like I said, I'd NEVER do it that way. 🙂
--Jeff Moden
June 21, 2011 at 7:15 am
mvillar (6/21/2011)
my only imperative reason: just compatibility, i have 7 sql 2000 servers and i need to...
So what did you use it for there?
--Jeff Moden
June 21, 2011 at 8:15 am
GSquared (6/21/2011)
The fact that xp_CmdShell has been enabled means that any attack vector that bypasses your stored procedure [font="Arial Black"]under an elevated rights situation [/font]has essentially been granted full access to anything on at least your database server, possibly anything in your domain.
I absolutely agree. You're definitely preaching to the choir on that. The key is preventing the elevated rights and that's easy to do.
Let's take a look at what an attack vector (normally SQL Injection) would look like. To greatly over-simplify, the App is running using "SA" privs (which I NEVER condone... I usually don't even allow it to run as DBO and neither do the folks in SysOps. ;-)) in one form or another and the sole attack method is to use the privs of the Application to "get in".
But, what if the Application and all it's various logins only had "Public" privs? What could the attacker do with xp_CmdShell once (s)he bypassed the App?
Nothing...
...they can't use xp_CmdShell because absolutely none of the logins have the privs to run xp_CmdShell directly IF xp_CmdShell is correctly setup for use. And properly setup, the method is "Gumby Proof" even if I do go on the very rare vacation because even if they write their own stored procedure with a call to xp_CmdShell, they won't be able to make it work because they don't have the privs to make a stored procedure that will run xp_CmdShell and they can't use an EXECUTE AS because they don't have the privs for that, either.
If another attack vector other than the App were used and the attacker somehow got in as "SA", having xp_CmdShell enabled is the least of your worries because, even if xp_CmdShell is deleted, someone can still use CMD through (most people don't know this) a very simple trick with OPENROWSET which an "SA" attack could certainly enable and use. They could also write a Job to use scripting at the OS level because they're in as "SA". Like I said, if someone gets in as "SA", the least of your worries may be what someone does with a call to xp_CmdShell because there are other simple methods to get to CMD once someone gets in as "SA".
Inside-attacks are impossible to prevent if the developers are given "SA" privs on production. Preaching to the choir, that's why people shouldn't give developers, users, or Apps "SA" privs on production boxes.
The problem that I'm becoming more and more aware of is that people really don't know how to run procs that contain xp_CmdShell without giving the proverbial keys to the city to a low prived user. In those cases and in the face of such a lack of knowledge, I agree that they shouldn't allow xp_CmdShell to be enabled.
--Jeff Moden
June 21, 2011 at 8:21 am
Oliiii (6/21/2011)
I can see why xp_cmdshell is useful for a DBA and some maintenance task.But i don't see why any dev would be allowed to use it?
Do you have an example of why it's used?
Yes... to design and build ETL methods and "progress dashboards" for necessarily manually controlled ETL systems. Of course, they would have no elevated privs in production.
--Jeff Moden
June 21, 2011 at 8:34 am
Jeff Moden (6/21/2011)
Oliiii (6/21/2011)
I can see why xp_cmdshell is useful for a DBA and some maintenance task.But i don't see why any dev would be allowed to use it?
Do you have an example of why it's used?
Yes... to design and build ETL methods and "progress dashboards" for necessarily manually controlled ETL systems. Of course, they would have no elevated privs in production.
Couldn't that kind of process be controlled completely outside of SQL Server?
Wouldn't it be easier to do it in .net?
I'm just asking because we don't have any example here, in this company SQL Server cannot be used as an application server, so anything that remotely looks like a business process that could be run from outside the server is not allowed (that include SSIS package as well).
Does that seems too harsh or unnecessary?
June 21, 2011 at 8:43 am
Jeff Moden (6/21/2011)
GSquared (6/21/2011)
The fact that xp_CmdShell has been enabled means that any attack vector that bypasses your stored procedure [font="Arial Black"]under an elevated rights situation [/font]has essentially been granted full access to anything on at least your database server, possibly anything in your domain.I absolutely agree. You're definitely preaching to the choir on that. The key is preventing the elevated rights and that's easy to do.
Let's take a look at what an attack vector (normally SQL Injection) would look like. To greatly over-simplify, the App is running using "SA" privs (which I NEVER condone... I usually don't even allow it to run as DBO and neither do the folks in SysOps. ;-)) in one form or another and the sole attack method is to use the privs of the Application to "get in".
But, what if the Application and all it's various logins only had "Public" privs? What could the attacker do with xp_CmdShell once (s)he bypassed the App?
Nothing...
...they can't use xp_CmdShell because absolutely none of the logins have the privs to run xp_CmdShell directly IF xp_CmdShell is correctly setup for use. And properly setup, the method is "Gumby Proof" even if I do go on the very rare vacation because even if they write their own stored procedure with a call to xp_CmdShell, they won't be able to make it work because they don't have the privs to make a stored procedure that will run xp_CmdShell and they can't use an EXECUTE AS because they don't have the privs for that, either.
If another attack vector other than the App were used and the attacker somehow got in as "SA", having xp_CmdShell enabled is the least of your worries because, even if xp_CmdShell is deleted, someone can still use CMD through (most people don't know this) a very simple trick with OPENROWSET which an "SA" attack could certainly enable and use. They could also write a Job to use scripting at the OS level because they're in as "SA". Like I said, if someone gets in as "SA", the least of your worries may be what someone does with a call to xp_CmdShell because there are other simple methods to get to CMD once someone gets in as "SA".
Inside-attacks are impossible to prevent if the developers are given "SA" privs on production. Preaching to the choir, that's why people shouldn't give developers, users, or Apps "SA" privs on production boxes.
The problem that I'm becoming more and more aware of is that people really don't know how to run procs that contain xp_CmdShell without giving the proverbial keys to the city to a low prived user. In those cases and in the face of such a lack of knowledge, I agree that they shouldn't allow xp_CmdShell to be enabled.
Yep.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 21, 2011 at 9:27 am
Couldn't that kind of process be controlled completely outside of SQL Server?
Wouldn't it be easier to do it in .net?
well, it have a lot of uses, to do some kind of activity in all servers at the same time
in my case, i use a procedure to connect via net use, backup and compress with winrar in dos, sometimes xcopy files etc.
i need to deal with all kind of S.Operative, SQL versions etc. sometimes in other regions, so i need to have my procedure standard for all
install some program would be nice, but a lot of things happend (delete, issue compability, corrupting), SQL Server and DOS is a must.
when i upgrade all of the remaining pcs i start to use something else, but today this process serves a lot to me
June 21, 2011 at 10:28 am
Jeff Moden (6/21/2011)
The problem that I'm becoming more and more aware of is that people really don't know how to run procs that contain xp_CmdShell without giving the proverbial keys to the city to a low prived user. In those cases and in the face of such a lack of knowledge, I agree that they shouldn't allow xp_CmdShell to be enabled.
I'm most definately falling into that list then, with your previous comment regarding EXECUTE AS. This might be why I shot xp_cmdshell. 🙂
I await your next article. :hehe:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2011 at 1:59 pm
Above is now reported as a marketing post.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2011 at 2:08 pm
Craig Farrell (6/20/2011)
I may need to, however, learn more about this before I go any further in this debate:opc.three (6/20/2011)
xp_cmdshell can be used by everyone on the instance but that means everyone will assume the identity of the xp_cmdshell proxy account credentials and you only get one of those per instance.I have some reading to do tomorrow.
Yes, I'm quoting myself for reference.
For anyone else who got sideswiped by this:
xp_cmdshell in msdn: http://msdn.microsoft.com/en-us/library/ms175046.aspx. See remarks.
Activating the proxy account: http://msdn.microsoft.com/en-us/library/ms190359.aspx
In particular:
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.
This in 2k was a bit different. It functioned under the SQLAgent proxy.
http://msdn.microsoft.com/en-us/library/aa260689(v=SQL.80).aspx
When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000.
In earlier versions, which is where 'kill it, Kill It, KILL IT' apparently was drummed into our heads:
Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account.
My most recent argument falls dead. By using a designated proxy account in 2k5 or higher, you can still allow your local agent the power it needs for DBA maintenance and restrict what your xp_cmdshell can do... at least as well as you can via a SQLAgent login controlling SSIS.
My final argument is non-logical, non-sensical, and just simply my own internal input after having too many beer lollipops: 😛
Good show, Jeff, learned something quite important from this thread. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2011 at 3:50 pm
Jeff Moden (6/21/2011)
Craig Farrell (6/20/2011)
I know I'm personally horrified at the idea of having my devs write up their own xp_cmdshell with EXECUTE AS permissions as a proc for usage later by a lower-allowance loginHeh... Lordy no. I'd NEVER do it that way. EXECUTE AS is a part of the problem! It does all the horrific things that people worry about and it hides who ran it. Like I said, I'd NEVER do it that way. 🙂
My curiosity was piqued by this comment...
Are you making a distinction between "EXECUTE AS" and "WITH EXECUTE AS"?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 4:30 pm
opc.three (6/21/2011)
Jeff Moden (6/21/2011)
Craig Farrell (6/20/2011)
I know I'm personally horrified at the idea of having my devs write up their own xp_cmdshell with EXECUTE AS permissions as a proc for usage later by a lower-allowance loginHeh... Lordy no. I'd NEVER do it that way. EXECUTE AS is a part of the problem! It does all the horrific things that people worry about and it hides who ran it. Like I said, I'd NEVER do it that way. 🙂
My curiosity was piqued by this comment...
Are you making a distinction between "EXECUTE AS" and "WITH EXECUTE AS"?
No... They both suck. 😛 I don't do either one for xp_CmdShell usage (or anything else for that matter). It's just not needed and it hides who was really running the proc.
--Jeff Moden
June 21, 2011 at 4:36 pm
mvillar (6/21/2011)
Couldn't that kind of process be controlled completely outside of SQL Server?
Wouldn't it be easier to do it in .net?
well, it have a lot of uses, to do some kind of activity in all servers at the same time
in my case, i use a procedure to connect via net use, backup and compress with winrar in dos, sometimes xcopy files etc.
i need to deal with all kind of S.Operative, SQL versions etc. sometimes in other regions, so i need to have my procedure standard for all
install some program would be nice, but a lot of things happend (delete, issue compability, corrupting), SQL Server and DOS is a must.
when i upgrade all of the remaining pcs i start to use something else, but today this process serves a lot to me
Another post that has made my day! Thank you VERY much for the explanation as to WHY you use it. Very well done!
--Jeff Moden
June 21, 2011 at 5:02 pm
Jeff Moden (6/21/2011)
opc.three (6/21/2011)
Jeff Moden (6/21/2011)
Craig Farrell (6/20/2011)
I know I'm personally horrified at the idea of having my devs write up their own xp_cmdshell with EXECUTE AS permissions as a proc for usage later by a lower-allowance loginHeh... Lordy no. I'd NEVER do it that way. EXECUTE AS is a part of the problem! It does all the horrific things that people worry about and it hides who ran it. Like I said, I'd NEVER do it that way. 🙂
My curiosity was piqued by this comment...
Are you making a distinction between "EXECUTE AS" and "WITH EXECUTE AS"?
No... They both suck. 😛 I don't do either one for xp_CmdShell usage (or anything else for that matter). It's just not needed and it hides who was really running the proc.
This thread is the gift that keeps on giving :satisfied:
Re: the hiding, I have to ask because I know I'm missing something...ORIGINAL_LOGIN() shows us "who really ran the proc" in T-SQL and SessionLoginName shows us "who" inside a trace. Where else do we lose visibility?
I won't let the cat out of the bag as to how I think you're planning to create the misdirection necessary to accomplish a secure xp_cmdshell environments without any security misqueues...will there be an article or was Craig's reference to "next article" about something else?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 61 through 75 (of 107 total)
You must be logged in to reply to this topic. Login to reply