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 «««678910»»»

How to call a batch file to execute from an SP Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 7:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
mister.magoo (3/26/2013)
How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?
(I know the user running the job will have been configured to have minimal permissions, but it still may have access to resources the attacker wouldn't normally have access to)

And SSIS packages that can FTP / email / perform file operations / run ad-hoc .net code - are they ok ?

Don't they also provide the opportunity for an "attacker" known or unknown to perform tasks with permissions other than their own?

Or how about someone gaining access to your workstation or the server and using SQLCMD mode in SSMS to run operating system commands? (assuming you have already locked down the dos prompt and the windows Run command and the "Run..." command on the windows task manager and the File...Open dialogs in Office)...

Oh hold on, while I was typing this, someone stole my server...damn it!


All good points and all things that need to be considered when securing an environment.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435735
Posted Tuesday, March 26, 2013 7:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
mister.magoo (3/26/2013)
How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?


I personally feel very well.
Especially about those ones which delete themselves (together with the history) after successful execution.
So those who rely on logging too much would be left empty-handed.
Post #1435736
Posted Tuesday, March 26, 2013 7:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Sergiy (3/26/2013)
Michael L John (3/26/2013)
How about an article about securing SQL server?


You mean - a book?


Securing SQL Server by Denny Cherry:
- page 153 recommends to "disable xp_cmdshell"
- page 161 recommends "removing the extended stored proc xp_cmdshell" but goes on to say that (paraphrased) "you may need to add them back before doing system upgrades and they can be re-added by a crafty attacker with the right level of permissions and knowledge of the system"


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435737
Posted Tuesday, March 26, 2013 7:53 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
opc.three (3/26/2013)

Securing SQL Server by Denny Cherry:
- page 153 recommends to "disable xp_cmdshell"
- page 161 recommends "removing the extended stored proc xp_cmdshell" but goes on to say that (paraphrased) "you may need to add them back before doing system upgrades and they can be re-added by a crafty attacker with the right level of permissions and knowledge of the system"


OK, another one fallen into the same misconception.
Not really surprising.
Jeff pointed out that it's a very common one.

Denny left the back door open for him to escape though.
Still not sure that knowing how to use "sp_configure" makes you some kind of crafty one. 1 minute on BOL - and you've got everything you need.
Or 30 seconds on Google, if you're short of time.
And "right level of permissions" is exactly the same one which required to use xp_cmdshell. If it's not given - there is no problem at all.


Another example of a widely published error - BOL for several years was referring to a wrong concept regarding @table and #table.
So what?
Thay had to fix it eventually.

What about this one?
http://www.galileowaswrong.com/galileowaswrong/
Post #1435739
Posted Tuesday, March 26, 2013 8:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Sergiy (3/26/2013)
opc.three (3/26/2013)

Securing SQL Server by Denny Cherry:
- page 153 recommends to "disable xp_cmdshell"
- page 161 recommends "removing the extended stored proc xp_cmdshell" but goes on to say that (paraphrased) "you may need to add them back before doing system upgrades and they can be re-added by a crafty attacker with the right level of permissions and knowledge of the system"


OK, another one fallen into the same misconception.
Not really surprising.
Jeff pointed out that it's a very common one.

Denny left the back door open for him to escape though.
Still not sure that knowing how to use "sp_configure" makes you some kind of crafty one.

He was not referring to sp_configure at all.

http://www.galileowaswrong.com/galileowaswrong

You're a clown, that's funny


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435742
Posted Tuesday, March 26, 2013 8:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
See, now here I thought xp_cmdshell was a virus. I learned something today:

http://sqlanddotnetdevelopment.blogspot.com/2011/06/sql-server-blocked-access-to-procedure.html?m=1


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435744
Posted Tuesday, March 26, 2013 8:49 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
opc.three (3/26/2013)
[quote]Sergiy (3/26/2013)
He was not referring to sp_configure at all.

Oh, really?
What was that about then?

http://www.galileowaswrong.com/galileowaswrong
You're a clown, that's funny

It's sad you don't even realise how pathetic you are...


Post #1435746
Posted Tuesday, March 26, 2013 9:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Here is Erland Sommarskog saying to Microsoft "The first thought at hand is to use xp_cmdshell to run a DEL command, but xp_cmdshell is best disabled."

https://connect.microsoft.com/SQLServer/feedback/details/470000/make-it-possible-to-copy-certificiates-between-databases-without-the-file-system

If you have any doubts about whether Erland Sommarskog understands how xp_cmdshell works, hit up Bing.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435747
Posted Tuesday, March 26, 2013 9:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
opc.three (3/26/2013)
Jeff Moden (3/26/2013)
opc.three (3/25/2013)
Jeff Moden (3/25/2013)
opc.three (3/24/2013)
You're still hung up on 'external attackers.' The point is, xp_cmdshell is a blunt tool that cannot be audited and allows people to run commands as someone else, possibly with more permissions than their own, without the possibility of being detected or tracked. That is not something to be taken lightly and is certainly something most people making decisions about the security of their environment and data would object too if it was fully explained.


You need to read the question I posed again. I said nothing about 'external attackers'. In fact, I specifically stated that "None of those 'individuals' are actually externally outside SQL server". Here's my post, again.

Fine. Support your words as I have supported mine. If only few (let's say, 2 DBAs) very trusted individuals have "SA" privs and none of those "individuals" are actually externally outside SQL Server) facing apps (an important point that you've left out that I've emphasized time and again), what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off?



So tell us all, "what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off"? If the answer is only "logging", please drive through because an "SA" can do just about anything without it being logged and where it is logged, (s)he can actually delete.

Maybe so, but all of that leaves an audit trail, and holes in the audit trail are an audit trail of their own, and can be grounds for termination. I do not need to make my point any clearer. Like I said to Sergiy, if you want to be in denial about the risks and exposure that leaving xp_cmdshell enabled creates that's your prerogative. But peddling it on these forums as if it is "as safe as a SELECT statement" is simply irresponsible, and I won't let it stand if I run into it.


Ok. I'll be more specific and simplify my question. You are the DBA for a company. Being a good DBA, you've given no one and no thing "SA" privs except yourself and maintenance tasks in the form of jobs running on SQL Server. You've ensured that the "SA" login password is very long and you don't use it in your daily duties. You only login as yourself.

xp_CmdShell is turned on because you have stored procedures that have been enabled to use it for your maintenance tasks.

Whether it be an internal or external user, name all of the users that can use xp_CmdShell.

Now explain how having xp_CmdShell turned on causes a security hole.

It doesn't.

It does if having access to the cmd prompt on the server as the SQL Server service account affords them access to anything they would not normally have access too. Are you sure that is not the case?

The funny thing your example above is that it is not even close to an honest representation of how we got here, on this thread or on the others where you tout its use and how safe it is. If it were just DBAs doing admin work with it I doubt there would ever be a dust-up. The people I am mostly trying to steer away from using xp_cmdshell are developers. This is where the floodgates open in terms of file share access and poor design patterns that effectively pain people into corners and cost tons of money to refactor later, the definition of an anti-pattern.


Now we're getting somewhere, ol' friend.

First, the scenario I gave wasn't meant to be an real life scenario for most. In my case, however, it is. Only DBAs have "SA" privs at work. But that means nothing because I absolutely agree that it's not real life for most. Your answer of...

If it were just DBAs doing admin work with it I doubt there would ever be a dust-up.


... depicts a more real life scenario. So does your other observation...

It does if having access to the cmd prompt on the server as the SQL Server service account affords them access to anything they would not normally have access too. Are you sure that is not the case?


Once again, you're absolutely correct.

But, we're also talking about proper security again, which is what I've been harping on since the git. The SQL Server service login and the SQL Agent login should be setup to only have privs to those things that people should be allowed to see becausre anyone with "SA" privs can setup a self deleting job that will execute an "Operating System (CmdExec)" job even with xp_CmdShell turned off and it won't show up in either the Job log or the default trace. Having xp_CmdShell turned off did nothing to protect against a malicious user that has "SA" privs.

I'll certainly agree that having xp_CmdShell turned off will catch the unwary user that has "SA" privs if they turn it on but, chances are really good that if a user knows that xp_CmdShell is turned off, they also know that turning it on will be logged with their login and machine in the default trace (the SQL Server log only logs the event and is basically useless for determining who caused the event). The method in the previous paragraph is only 1 of a dozen simple workarounds. Having it turned off provides no deterent to such maliciousness and, in the previous example, a command line execution isn't even logged.

Turning xp_CmdShell off did nothing to prevent a user with "SA" privs from easily using a command line level command in a totally undetected fashion. The user could be a DBA or any user with "SA". (The same problem exists with SSIS).

Shifting gears to your next statement of...
The people I am mostly trying to steer away from using xp_cmdshell are developers. This is where the floodgates open in terms of file share access and poor design patterns that effectively pain people into corners and cost tons of money to refactor later, the definition of an anti-pattern.


You use file shares all the time in things like SSIS. There's no difference in having SQL Server have the same kind of access. Notice that I did NOT say letting the Developers have the file access. You've previously witnessed first hand that a stored procedure that uses xp_CmdShell can be used by people with only PUBLIC privs but they can't use xp_CmdShell themselves. How is having a well formed stored procedure executing against a file share a "poor design pattern" or and "anti-pattern"? Some of the best ETL systems I've ever seen have been written in T-SQL using xp_CmdShell to get to things like Robo-Copy and PowerShell.

But my personal success with the command line, whether through a Job or directly through xp_CmdShell has nothing to do with me saying to just turn it on and leave it on. If you want, turn it off and leave it off. Either way, anyone with "SA" privs will be able to get to the command line and they can get there in a totally undectable fashion. Having it turned off does nothing for security and having it turned on does NOT create a security hole. People and apps having "SA" privs is the ONLY security hole because only people or apps with "SA" privs can use xp_CmdShell or do one of the totally undetectable work arounds to using xp_CmdShell.

THAT's my only point. Most people follow the mantra that xp_CmdShell should be turned of and there are MS and other authors of huge numbers of white papers that agree with that mantra and a whole lot of them are missing the point. The point is...

... having xp_CmdShell turned on does not create a security hole that's already there. People and apps with "SA" privs are the only security hole. Anyone with those privs can get to the command line in a totally undectable manner even if xp_CmdShell is turned off.

Start warning people about THAT instead of wasting your breath about turning off xp_CmdShell. It does NOTHING for 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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1435748
Posted Tuesday, March 26, 2013 9:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
opc.three (3/26/2013)
Here is Erland Sommarskog saying to Microsoft "The first thought at hand is to use xp_cmdshell to run a DEL command, but xp_cmdshell is best disabled."

https://connect.microsoft.com/SQLServer/feedback/details/470000/make-it-possible-to-copy-certificiates-between-databases-without-the-file-system

If you have any doubts about whether Erland Sommarskog understands how xp_cmdshell works, hit up Bing.


I have a huge amount of respect for Erland but if he thinks that having xp_CmdShell disabled provides anything more than a highly permeable and easily lifted veil of security, then he's also wrong. The problem is real security, not xp_CmdShell. Improper use of xp_CmdShell is nothing more than a symptom of otherwise bad 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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1435749
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse