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

Command to execute Copy,Delete etc instead of XP_cmdShell Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:59 AM
Points: 5, Visits: 12
Dear All,

We are having legacy Sql server data in Sql 2000 and moving to Sql 2012.Have found usage of XP_cmdshell in many procedures trying to do the following

copy command - files to local drives
erase command - files from local drives
Move
BCP (importing to tables from file)
Ping(just for testing)
dir command
DTSRun

company standards now does not permit usage of XP_cmdshell as it has security flaws, so need to find alternative, could any one please help me on this ? Cannot use Sql agent, as we are going for Autosys . DTS is planned to move to SSI in the next phase.So other than that I found CLR integration , but seems too much of change to procedure and I am new to it. Just wanted to know if there is any command alternative to XP_cmdshell. Please shed some light.
regards
Raj
Post #1495557
Posted Tuesday, September 17, 2013 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
You might want to read this

http://www.sqlservercentral.com/Forums/Topic1173815-392-1.aspx#bm1173927

and determine if the use of !! to execute operating system commands will work in your environment.

If you do test ... please post your result (success or failure) so that others may learn.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1495581
Posted Tuesday, September 17, 2013 4:25 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 35,584, Visits: 32,174
srajinigandh (9/17/2013)

...company standards now does not permit usage of XP_cmdshell as it has security flaws, ...


Sigh....

That's like saying that DELETE has a flaw in it because it will delete all rows if you don't include a WHERE clause. XP_CmdShell isn't "the" security problem. How people misuse is the actual problem and such misuse is pretty easy to prevent.


--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 #1495718
Posted Wednesday, September 18, 2013 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:59 AM
Points: 5, Visits: 12
Hi Ron,

Tried replicaate that proc , but Its not working ,, giving error as Incorrect syntax near '!'
Any other suggestions please ? I am using Sql SErver 2012
regards
Raj
Post #1495801
Posted Wednesday, September 18, 2013 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:59 AM
Points: 5, Visits: 12
Jeff Moden (9/17/2013)
srajinigandh (9/17/2013)

...company standards now does not permit usage of XP_cmdshell as it has security flaws, ...


Sigh....

That's like saying that DELETE has a flaw in it because it will delete all rows if you don't include a WHERE clause. XP_CmdShell isn't "the" security problem. How people misuse is the actual problem and such misuse is pretty easy to prevent.

Hi Jeff,
I am not an expert, but certainly XP_CmdShell has elevated rights and people can misuse which no one can stop it.It would be great if you can suggest some ideas .
regards
Raj

Edit: Saw your query on similar thing ..And I beleive you were similar situation like mine ,so would be great to know how you sorted it
Post #1495803
Posted Wednesday, September 18, 2013 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:59 AM
Points: 5, Visits: 12
bitbucket-25253 (9/17/2013)
You might want to read this

http://www.sqlservercentral.com/Forums/Topic1173815-392-1.aspx#bm1173927

and determine if the use of !! to execute operating system commands will work in your environment.

If you do test ... please post your result (success or failure) so that others may learn.

Hi Ron,
Sorry I understood need to enable SQLCMD and it works fine. But for me the situation is have many procedures which has several OS commands and also SQL commands(including cursors) ,And think we cannot keep or it would be challenging for SQLCMD to be ON for executions of procs with OS commands and without OS commands. I am stuck now. Google unable to help too...anyone have idea? please do reply.
regards
Raj
Post #1495828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse