How to call a batch file to execute from an SP

Question

How to call a batch file to execute from an SP

Viewing 15 posts - 16 through 30 (of 96 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Michael L John (3/21/2013)
I stand corrected.
BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.
I will amend the statement to be:
"xp_cmdshell CAN be a security risk"

Nope, you had it right the first time!

Leaving xp_cmdshell enabled exposes the system to the option for people in the sysadmin Role to access the server's file system using someone else's credential, namely the SQL Server service account. That leaves a gaping hole in the auditability of a system, which for me constitutes a security exposure and a threat to the system.

I would leave xp_cmdshell disabled and put up every roadblock and auditing option (e.g. Policy Based Management) to keep it disabled, and log attempts to enable it. It's just not worth it. There are so many better options out there than to allow cmd-shell and file system access through your database engine.

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

opc.three (3/24/2013)
Michael L John (3/21/2013)
I stand corrected.
BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.
I will amend the statement to be:
"xp_cmdshell CAN be a security risk"
Nope, you had it right the first time!
Leaving xp_cmdshell enabled exposes the system to the option for people in the sysadmin Role to access the server's file system using someone else's credential, namely the SQL Server service account. That leaves a gaping hole in the auditability of a system, which for me constitutes a security exposure and a threat to the system.
I would leave xp_cmdshell disabled and put up every roadblock and auditing option (e.g. Policy Based Management) to keep it disabled, and log attempts to enable it. It's just not worth it. There are so many better options out there than to allow cmd-shell and file system access through your database engine.

He didn't have it right the first time and you know it. If someone get's in as "SA", then it won't matter or even slow down an attacker if it's disabled. Only people with "SA" privs can execute xp_CmdShell directly. People should not be given permissions to execute it directly for any reason. They should only be given privs to execute procs that may contain it.

Turning it off does not increase security in any way, shape, or form. Yeah... having it turned off will slow down an attacker... for about 3 ms.

xp_CmdShell isn't a security risk. Bad security is the only security risk. To think you're safe just because you have xp_CmdShell turned off is like the proverbial ostrich hiding his head. You must have proper security or you will be hacked. Turning off xp_CmdShell is not what proper security is about.

--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: 182276 More actions · Answer 3

Jeff Moden (3/24/2013)
opc.three (3/24/2013)
Michael L John (3/21/2013)
I stand corrected.
BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.
I will amend the statement to be:
"xp_cmdshell CAN be a security risk"
Nope, you had it right the first time!
Leaving xp_cmdshell enabled exposes the system to the option for people in the sysadmin Role to access the server's file system using someone else's credential, namely the SQL Server service account. That leaves a gaping hole in the auditability of a system, which for me constitutes a security exposure and a threat to the system.
I would leave xp_cmdshell disabled and put up every roadblock and auditing option (e.g. Policy Based Management) to keep it disabled, and log attempts to enable it. It's just not worth it. There are so many better options out there than to allow cmd-shell and file system access through your database engine.
He didn't have it right the first time and you know it.

Not even for a second. I disagree, wholeheartedly, and you must know that by now we have reached an impasse on this topic.

Turning off xp_CmdShell is not what proper security is about.

Layering is key when securing a system and leaving xp_cmdshell enabled is one less layer of protection, regardless of how feckless you think it may be. You have to consider that sometimes (some would argue most of the time) you are not protecting from some faceless external hacker you are protecting your data and system from people who have access to it every day. I guess you are blinded by the idea that since 'any sa can bypass any roadblock' that we should not put up any roadblocks or layer our protections and auditing in any way, and I think that is a dangerous mindset when it comes to securing a system.

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

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

It takes 3ms for an attacker that get's in as "SA" to blow through so called "layering" to execute something using xp_CmdShell because their code is expecting it to be turned off and will turn it on.

And, yes, I whole heartedly agree that the attack. malicious or by accident, frequently comes from within. I'm not blind to that fact. I think, however, you're blinded by the fact that you think disabling xp_CmdShell is a roadblock of any kind. A roadblock is effective only if there's no way around it. It takes no time for someone with "SA" privs to turn it on. Disabling xp_CmdShell lulls people into a false sense of security into thinking that no one can use it. And saying that turning it on is logged is simply saying there will be a documented testimony to bad security.

Stop wasting time ad lulling people into a false sense of security by telling them to turn off xp_CmdShell. It's like telling people that someone could damage the database by using SSIS or Powershell. That's nothing but a veil over rotting meat. Let's get to the real problem. Anything and everything, including a turned off instance of xp_CmdShell, will be used against the systems if someone malicious gains or has access to the server as "SA".

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

Michael L John (3/21/2013)
I stand corrected.
BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.
I will amend the statement to be:
"xp_cmdshell CAN be a security risk"

But it's not. The only people that can use it are people with "SA" privs or if someone was dumb enough to grant a proxy to a user. Anyone with "SA" privs can turn it on even if it's off. It's like saying that using Powershell is a risk. It's not unless a malicious person gets in with the right privs to use it. If a malicious user with the right privs gets into your server, it's not going to matter if you use xp_Cmdshell or not and it sure won't matter if it's turned off or not. The malicious user will simply turn it on.

If the DBAs are too busy to mind their primary job, that of security, then it might be time to have a "come to Jesus" meeting with them. No application and no user should have "SA" privs and THAT's the only way you're going to keep xp_CmdShell from being used maliciously. It's not a risk. It's a symptom of really bad security. You should be able to have stored procedures that use xp_CmdShell up and running just fine and be perfectly secure. If you can't, then the system is at risk even if xp_CmdShell is turned off.

--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: 182276 More actions · Answer 6

Jeff Moden (3/24/2013)
It takes 3ms for an attacker that get's in as "SA" to blow through so called "layering" to execute something using xp_CmdShell because their code is expecting it to be turned off and will turn it on.
And, yes, I whole heartedly agree that the attack. malicious or by accident, frequently comes from within. I'm not blind to that fact. I think, however, you're blinded by the fact that you think disabling xp_CmdShell is a roadblock of any kind. A roadblock is effective only if there's no way around it. It takes no time for someone with "SA" privs to turn it on. Disabling xp_CmdShell lulls people into a false sense of security into thinking that no one can use it. And saying that turning it on is logged is simply saying there will be a documented testimony to bad security.
Stop wasting time ad lulling people into a false sense of security by telling them to turn off xp_CmdShell. It's like telling people that someone could damage the database by using SSIS or Powershell. That's nothing but a veil over rotting meat. Let's get to the real problem. Anything and everything, including a turned off instance of xp_CmdShell, will be used against the systems if someone malicious gains or has access to the server as "SA".

I am sorry, but I feel that you are looking at the exposure through to narrow of a lens, Jeff. It's not just about "blowing through" the layering. Your argument assumes that an attackers only reason to access an instance is to destroy it, which is rarely the case when it comes to internal attacks. It is usually to steal data or intellectual property and do it in such a way as to remain undetected. Enabling xp_cmdshell is an operation that is not so easily done in an undetectable manner, nor is using it. I am not sure how you can think that leaving an open conduit between the database engine and the server's file system, as well as a cmd shell prompt, while running under a different set of credentials than the person running it can be a safe thing to leave lying around.

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

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

Jeff Moden (3/24/2013)
Michael L John (3/21/2013)
I stand corrected.
BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.
I will amend the statement to be:
"xp_cmdshell CAN be a security risk"
But it's not. The only people that can use it are people with "SA" privs or if someone was dumb enough to grant a proxy to a user. Anyone with "SA" privs can turn it on even if it's off. It's like saying that using Powershell is a risk. It's not unless a malicious person gets in with the right privs to use it. If a malicious user with the right privs gets into your server, it's not going to matter if you use xp_Cmdshell or not and it sure won't matter if it's turned off or not. The malicious user will simply turn it on.
If the DBAs are too busy to mind their primary job, that of security, then it might be time to have a "come to Jesus" meeting with them. No application and no user should have "SA" privs and THAT's the only way you're going to keep xp_CmdShell from being used maliciously. It's not a risk. It's a symptom of really bad security. You should be able to have stored procedures that use xp_CmdShell up and running just fine and be perfectly secure. If you can't, then the system is at risk even if xp_CmdShell is turned off.

To me, your reasoning is analog to:

Why would I bother locking the doors when I leave my house when a malicious contractor installing a sprinkler system at my home could simply drive the Bobcat Tractor they're using to dig trenches through my front door and take anything they wanted.

No one I know would subscribe to that line of thinking so why apply it to securing a database?

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

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

The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.

--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: 182276 More actions · Answer 9

Jeff Moden (3/24/2013)
The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.

Who in their right mind would have a key-hook on the outside if their house 😛

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

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

opc.three (3/24/2013)
Jeff Moden (3/24/2013)
The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.
Who in their right mind would have a key-hook on the outside if their house 😛

BWAAA-HAAA! Maybe the cousins of the ones that lock the door and leave the key in it?

But that does exemplify the thoughts I have on the xp_CmdShell subject. "Who in their right mind" would think that turning xp_CmdShell provides any kind of improved security at all? Too many people think that turning off xp_CmdShell provides a locked door. While it While it may physically lock the door and require someone with a key to unlock it, too many people either leave the keys close to the door or actually leave the key in the door.

I'm not so much interested in compelling people to use xp_CmdShell. That's up to them. I just don't want anyone to believe for even a micro second that turning of xp_CmdShell does anything substantial to improve security. That's all.

--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: 182276 More actions · Answer 11

It is their choice ultimately, but to paraphrase a comment you have made in the past, characterizing xp_cmdshell as "safe as a SELECT statement" is just plain inaccurate. In the spirit of full disclosure, and especially on a public forum, I'll call out the problems with xp_cmdshell every single time and steer people towards more secure, more extensible and more auditable solutions. The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled. I feel like it is irresponsible to portray xp_cmdshell in any other way. To push the idea that as long as only a few people are in the sysadmin Role and there is no Proxy setup that your instance is secure and auditable is simply not true, speaking of lulling people into a false sense of security.

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 12

opc.three (3/24/2013)
It is their choice ultimately, but to paraphrase a comment you have made in the past, characterizing xp_cmdshell as "safe as a SELECT statement" is just plain inaccurate. In the spirit of full disclosure, and especially on a public forum, I'll call out the problems with xp_cmdshell every single time and steer people towards more secure, more extensible and more auditable solutions. The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled. I feel like it is irresponsible to portray xp_cmdshell in any other way.

No, it's not inaccurate. What is inaccurate is you saying that that turning off xp_CmdShell provides any kind of addditional security in the face of bad security. It just doesn't. Whether it's turned on or off, if someone get's in with "SA" privs, it's going to be a career changing moment for you. People need to realize that there's no benefit to having it turned off because the first thing any attacker, internal or external, is going to do is turn it on.

To push the idea that as long as only a few people are in the sysadmin Role and there is no Proxy setup that your instance is secure and auditable is simply not true, speaking of lulling people into a false sense of security.

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?

--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: 182276 More actions · Answer 13

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.

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

Sergiy SSC Guru Points: 110206 More actions · Answer 14

opc.three (3/24/2013)
The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled.

OK.

I'm an intruder on your system.

If I'm connected using non-systemadmin credentials I cannot execute any call to xp_cmdshell anyway, and I cannot get privileges associated with it.

So, it does not really matter if it's disabled or enabled - I won't be able even to figure out that.

Now, if I'm connected as a systemadmin. First thing I will do is

EXEC sp_configure 'xp_cmdshell', 1

Immediately followed by

RECONFIGURE WITH OVERRIDE

Voilà!

xp_cmdshell is enabled, no matter what state it was 3 ms ago.

So, where those promissed "less security exposures, has less vulnerabilities"?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 15

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

Would it be wiser to limit the privileges associated with the account running sqlserver service to its jon related tasks?

Read from there, write there, check on that location, execute that task.

That's it. The list is closed.

If you need to do something else - talk to your system administrator, as they say in MS messages.

This layer would be definitely harder to pass than to enable xp_cmdshell, don't you think?

_____________
Code for TallyGenerator