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


Command to execute Copy,Delete etc instead of XP_cmdShell


Command to execute Copy,Delete etc instead of XP_cmdShell

Author
Message
srajinigandh
srajinigandh
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 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
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15651 Visits: 25280
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213737 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
srajinigandh
srajinigandh
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 12
Hi Ron,

Tried replicaate that proc , but Its not working ,, giving error as Incorrect syntax near '!' Crying
Any other suggestions please ? I am using Sql SErver 2012
regards
Raj
srajinigandh
srajinigandh
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 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
srajinigandh
srajinigandh
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

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