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 ««12

Easy Error Trapping When Using xp_cmdshell Expand / Collapse
Author
Message
Posted Tuesday, February 2, 2010 2:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:50 AM
Points: 10, Visits: 80
It will challenge the religious dogma from all the jobsworths out there, but I would love to find just one REAL example of a security breach via xp_commandshell.

Yes a malicious employee or an external user of a poorly written site could potentially execute anything that’s permitted in the context of the SQL service account. But in the real world has anyone in a position to exploit this ever done anything they couldn’t have done via many other methods? Tie this loophole down by via well managed service accounts by all means, but don’t handicap database applications by accepting poor network security

Honestly! In a world where buffer overflows afflict almost every browser, most SQL servers operate as domain admin and the majority of Oracle shops still use passwords that haven’t changed for years there are better uses for everyone’s time.

Post #858261
Posted Tuesday, February 2, 2010 3:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Pete,
I have to challenge a number of things in your post.

The biggest problem with security is apathy and lazyness. FAR too many people use highly privileged accounts for app logins, OR their application is architected in such a way that requires excess rights. A little SQL injection and BOOM, I do what I want to your server. There are many examples of this in the news and on this site.

I think you'll find the number of SQL Server logins that are Domain Admins is fairly low, that is lazy in the extreme. What I think you will see a LOT of is domain accounts as Local Admins, this seems to be the middle ground. It would be best to use a domain account just as a user, but most shops don't want to deal with that, per my experience. And if the machine will NEVER (hate that word) touch ANY machine but itself you could potentially use a local account, but that is a tall order..

CEWII
Post #858274
Posted Tuesday, February 2, 2010 5:56 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Pei Zhu-415513 (2/2/2010)
It is just a story "close one door/open another door". If you use sproc to wrap xp_cmdshell, you control what the sproc can do. Keep in mind the sqlclr is not easy to debug and not everyone knows how to write it/deploy it, etc , and it could cause memory leaking.


You are quite correct. There are ways and times when xp_cmdshell can be used appropriately and correctly. You can limit who can use xp_cmdshell and how they can do it, and as you mentioned you can wrap it in a procedure and only allow access to the procedure so its use can be very limited. And by using a proxy account with limited permissions you can limit the worst case scenario.

There are certainly ways it can be abused (when I took over as DBA at one organization, I found ways that our developers effectively admin level control on the machines they had access to because they could run xp_cmdshell and it proxied out to an account with admin priveleges....I changed that quickly.) but this is true of sqlclr. Yes, there certainly ways that you can use sqlclr very securely, but the same is true of xp_cmdshell.

I tend to allow xp_cmdshell only where I see that it is truly justified and only with the lowest level of access it needs to get the job done. But then I say exactly the same thing about sqlclr, and we use both in my current organization in moderation and with care.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #858338
Posted Tuesday, February 2, 2010 6:01 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
pete callaghan (2/2/2010)
It will challenge the religious dogma from all the jobsworths out there, but I would love to find just one REAL example of a security breach via xp_commandshell.

Yes a malicious employee or an external user of a poorly written site could potentially execute anything that’s permitted in the context of the SQL service account. But in the real world has anyone in a position to exploit this ever done anything they couldn’t have done via many other methods? ...


I am quite curious about this myself. As I mentioned in another post, I have seen areas where an employee *could have* used xp_cmdshell to perform a privelege escalation or other forms of nastiness, but I have never heard of it actually being done anywhere. And I quickly closed that particularly loophole without disably xp_cmdshell. People could still have used xp_cmdshell to do horrible things, but after I changed the settings they could only have done things that they could have done in other ways.

Does anyone know of an actual real-world exploit of xp_cmdshell? What about one where the xp_cmdshell was set up intelligently and still used in an exploit?


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #858339
Posted Tuesday, February 2, 2010 6:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,193, Visits: 3,151
put it simple. there is reason why it exists just like sqlclr. use it wisely it could be very useful component. do not limit youself.
Post #858345
Posted Wednesday, February 3, 2010 7:34 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
xp_cmdshell can be a safe and useful thing to turn on.

Don't create a proxy account and don't grant anyone into SA that shouldn't be.

wow... that was easy.


and the first person that says that people can elevate priviliges to sa through sql injection or buffer overflows is going to get one of these:



Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #859243
Posted Thursday, February 4, 2010 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
From my earlier post:
"The biggest problem with security is apathy and lazyness. FAR too many people use highly privileged accounts for app logins, OR their application is architected in such a way that requires excess rights."

You are generally right in your post, but the above covers what often happens..

CEWII
Post #859736
Posted Wednesday, July 16, 2014 10:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:09 AM
Points: 284, Visits: 11
Back to the subject... which was, how to find errors when running BCP from xp_cmdshell...

You can run into trouble if you are trying to nest the INSERT...EXEC logic. Run...

CREATE PROCEDURE p_foo3
AS
CREATE TABLE #foodata3 (abc CHAR (1))

INSERT INTO #foodata3 (abc)
SELECT 'A'

SELECT *
FROM #foodata3
GO
EXEC p_foo3
GO

abc
---
A

CREATE PROCEDURE p_foo2
AS
CREATE TABLE #foodata2 (abc CHAR (1))

INSERT INTO #foodata2 (abc)
EXEC p_foo3

SELECT *
FROM #foodata2
GO
EXEC p_foo2
GO

abc
---
A

CREATE PROCEDURE p_foo1
AS
CREATE TABLE #foodata1 (abc CHAR (1))

INSERT INTO #foodata1 (abc)
EXEC p_foo2

SELECT *
FROM #foodata1
GO
EXEC p_foo1
GO

Msg 8164, Level 16, State 1, Procedure p_foo2, Line 5
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

(0 row(s) affected)

If you have a procedure that moves data via BCP and you insert the output into a table, and then you call the procedure from another procedure and try capturing the output in the same manner, it doesn't work.

I'd always considered this behavior of SQL Server's as an arbitrary limitation they would someday fix. But apparently not. Too busy adding features we don't need to fix a a shortcoming in a feature we might actually use?






Post #1593155
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse