SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Easy Error Trapping When Using xp_cmdshell


Easy Error Trapping When Using xp_cmdshell

Author
Message
pete callaghan
pete callaghan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 82
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.
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13240 Visits: 5314
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
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 920
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/
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 920
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/
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 4118
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.
SQLBOT
SQLBOT
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 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: Doze

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
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13240 Visits: 5314
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
Lee Dise
Lee Dise
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 21
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?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search