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

How to call a batch file to execute from an SP Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 3:36 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 998, Visits: 3,130
Glad to help!
Good luck!!!


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1433511
Posted Wednesday, March 20, 2013 5:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
Or alternative method:

1. Create a procedure which returns a list of the jobs which "are running longer than defined SLA"
2. Add a bcp command to your .BAT file which runs that procedure and saves the output into a file.
3. Check the size of the file. If it is not empty - execute the rest of the .BAT (option - CALL ???.BAT ) to raise tickets for each line in the file.
4. Drop the file.

You may turn it into a Windows task running every 5 minutes or make it a service - your choice.

But you better stay away from calling .BAT files from SQL Server.
If something goes wrong during execution ("overwrite the file Y/N", "No such folder, do you want to create it?", "Delete read-only file, are you sure?", lack of disk space or anything else) - SQL does not have means to handle errors raised in cmd shell. You job will be stuck, will be sitting quietly wayting for command shell to complete it task which is waiting for console input in a session with hidden console, no long running jobs will be discovered, no tickets raised, everybody's happy, until customers start to complain about the system being down for half of the day.

From my experiense - this scenario materialises every time when somebody creates an SQL task calling a .BAT file.
No exclusions so far.
If they kill the job when it's not finished within, say, a minute or so, they end up killing the Windows server with thousands of command shells started on the background and waiting for a human input.
Post #1433544
Posted Wednesday, March 20, 2013 5:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
Michael L John (3/20/2013)
1. Take the call to xp_cmdshell out of the SP.
2. In the SP, test for the condition that would trigger the call to the batch file.
a. Return success if the condition is FALSE
b. Return failure if the condition is TRUE
3. Create a SQL Job.
a. Call the procedure in the first step.
b. In the advanced properties of the step,
1. Set the "on success action" to "Quit the job reporting success"
2. Set the "On Failure Action" to "Go to the next step"
c. In the second step, set the "type" to operating system and call the batch file.


Nicely done and probably the way I'd do it, as well.


By default xp_cmdshell is disabled in SQL 2005 and up. It's a big security issue. Batch files and T-SQL are not meant to play together. They are for two different uses.


I'll have to say that's mostly old wive's tales, myths, and huge misunderstandings. xp_CmdShell is NOT an issue on a properly locked down system. The real problem is that few take the time to actually and properly lock down their system.

Consider this. Who can run xp_CmdShell? Most people will say only those with "SA" privs and they'd be partially correct. If someone is foolish enough to give a low prived user proxy privs to run it, that's another way. Most people very incorrectlyresort to that method when they want a user to be able to run a proc with xp_CmdShell in it and that's probably the worst way to do it.

But, I digress. If you give no one proxy privs and only DBAs have "SA" privs, who can run xp_CmdShell? The answer is only DBAs and an attacker that breaks in as one of those DBAs.

Now, knowing that, you have "wisely" (notice the quotes) turned off xp_CmdShell because it's a "security risk" (notice the quotes again). If an attacker does break into your system as a DBA, then he HAS "SA" privs and can turn it on. In fact, since he's actually expecting it to be off, his attack code is setup to automatically turn it on and he raises holy hell in you system after that.

So what did having xp_CmdShell turned off accomplish? NOTHING. It only slowed the attacker down for about 1 ms.

No, let's turn the table. You've very wisely (notice, no quotes) locked down your system so that no apps and no users have anything more than DBO privs (I usually try to restrict to just "PUPLIC" privs but DBO will do for this example). xp_CmdShell is turn on!

Remember, there are no proxied users that can run it. Who, besides, the DBAs, can use xp_CmdShell? Will an attacker that breaks in as a non-DBA user or app be able to run it EVEN IF IT'S TURNED ON?

NO!

Now, let's just say that you have an app (or user) that must be allowed to run a stored procedure on an ad hoc, on demand basis that has a call to xp_CmdShell in it. What do you do? Give the user "SA" privs? We both know that's stupid. Setup a proxy and give just that user privs to run xp_CmdShell directly? That's even more stupid because it also gives the DBAs a false sense of security.

So what to do?

There are multiple methods (Google them... not trying to write an article here) to setup the stored procedure itself to have the privs to run xp_CmdShell and only in the manner prescribed in the proc. A user with only "PUBLIC" privs and "exec" privs on that proc can run the stored procedure just fine yet they cannot run xp_CmdShell directy. Shoot... they can't even see what's inside the proc. THAT's the right way to use xp_CmdShell!

Like I said, xp_CmdShell is NOT a security risk. Only poor security and a poor understanding of security is a security risk.


--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."

(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 #1433551
Posted Wednesday, March 20, 2013 6:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 422, Visits: 2,647
Thank you so much for all of your feedbacks.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1433554
Posted Thursday, March 21, 2013 3:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 998, Visits: 3,130
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"





Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1433668
Posted Sunday, March 24, 2013 11:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
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
Post #1434689
Posted Sunday, March 24, 2013 1:36 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(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 #1434696
Posted Sunday, March 24, 2013 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
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
Post #1434697
Posted Sunday, March 24, 2013 2:59 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(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 #1434700
Posted Sunday, March 24, 2013 3:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(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 #1434701
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse