Replacement for xp_cmdshell in SQL2k

ISSUE (in SQL Server 2000): Some Client applicationsâ' stored procedures are
using â'xp_cmdshellâ'.
xp_cmdshell is an extended stored procedure in Master database. This command
takes a string of dos commands and executes them in the DOS command shell.
Now the Client wants a replacement for this xp_cmdshell . i.e., I need some
other function or procedure or something which I can place instead of this
xp_cmdshell in the respective stored procedures using this xp_cmdshell.
Note: The Client doesnâ't want to use this xp_cmdshell at all.The client probably heard that having xp_cmdShell enabled adds to your
security risk, and wants a safe way to do the same things they have been
doing.
What have they been doing with xp_cmdshell? There may be ways to replace
that functionality, there may not.
You could create jobs that execute cmdexec steps, and have the stored
procedures activate the jobs.
Please provide more info about what they do and why they want to discontinue
using xp_cmdshell.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||The solution depends on what you are executing via xp_cmdshell. One
approach is to create a new extended stored procedure to encapsulate the
needed functionality.
Another method is to move the processing out of SQL Server and into
application code, either on the client or on a server. It is likely that
this is the better approach if your client is concerned about security.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||Thank You for your response.
It is because of some security reasons the Client does not want any non-DBA
to use "xp_cmdshell" command.
This command takes a string as input and executes the same in the DOS
command shell. The string is nothing but a valid DOS command.
Example: Exec xp_cmdshell 'dir *.*'
Similarly as above my requirement has FTP, move, jview, copy, del, bcp etc.,
DOS commands as inputs to this extended stored procedure in the form of a
string.
I hope I explained it more clearly to you now.
-Aizaz
"David Lundell" wrote:
> The client probably heard that having xp_cmdShell enabled adds to your
> security risk, and wants a safe way to do the same things they have been
> doing.
> What have they been doing with xp_cmdshell? There may be ways to replace
> that functionality, there may not.
> You could create jobs that execute cmdexec steps, and have the stored
> procedures activate the jobs.
> Please provide more info about what they do and why they want to discontinue
> using xp_cmdshell.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> > are
> > using "xp_cmdshell".
> >
> > xp_cmdshell is an extended stored procedure in Master database. This
> > command
> > takes a string of dos commands and executes them in the DOS command shell.
> >
> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> > some
> > other function or procedure or something which I can place instead of this
> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >
> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >
>
>|||Thank You for your response.
A string of DOS commands is the input "xp_cmdshell".
Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
Which means we need some way in which we can take a string (which is a valid
DOS command) and execute it in DOS command shell (which is the functionality
of xp_cmdshell)
"Dan Guzman" wrote:
> The solution depends on what you are executing via xp_cmdshell. One
> approach is to create a new extended stored procedure to encapsulate the
> needed functionality.
> Another method is to move the processing out of SQL Server and into
> application code, either on the client or on a server. It is likely that
> this is the better approach if your client is concerned about security.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> > are
> > using "xp_cmdshell".
> >
> > xp_cmdshell is an extended stored procedure in Master database. This
> > command
> > takes a string of dos commands and executes them in the DOS command shell.
> >
> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> > some
> > other function or procedure or something which I can place instead of this
> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >
> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >
>
>|||> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
So why are you using xp_cmdshell for this task? Is there some reason you
cannot perform file manipulation like this directly from you application
code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
> Thank You for your response.
> A string of DOS commands is the input "xp_cmdshell".
> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> Which means we need some way in which we can take a string (which is a
> valid
> DOS command) and execute it in DOS command shell (which is the
> functionality
> of xp_cmdshell)
> "Dan Guzman" wrote:
>> The solution depends on what you are executing via xp_cmdshell. One
>> approach is to create a new extended stored procedure to encapsulate the
>> needed functionality.
>> Another method is to move the processing out of SQL Server and into
>> application code, either on the client or on a server. It is likely that
>> this is the better approach if your client is concerned about security.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
>> > are
>> > using "xp_cmdshell".
>> >
>> > xp_cmdshell is an extended stored procedure in Master database. This
>> > command
>> > takes a string of dos commands and executes them in the DOS command
>> > shell.
>> >
>> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
>> > some
>> > other function or procedure or something which I can place instead of
>> > this
>> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
>> >
>> > Note: The Client doesn't want to use this xp_cmdshell at all.
>> >
>>|||There is some processing done in a stored procedure and this extended stored
procedure is called after that. Once the xp completes its job, some more
processing done in the original stored procedure.
Also there are many stored procedures which internally call xp for different
tasks like bulk copy etc. Most of these stored procedures are scheduled SQL
tasks and are not called from any application. So replacing this
functionality in application code can be ruled out.
Thanks for your inputs/suggestions...
"Dan Guzman" wrote:
> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> So why are you using xp_cmdshell for this task? Is there some reason you
> cannot perform file manipulation like this directly from you application
> code?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
> > Thank You for your response.
> >
> > A string of DOS commands is the input "xp_cmdshell".
> >
> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> >
> > Which means we need some way in which we can take a string (which is a
> > valid
> > DOS command) and execute it in DOS command shell (which is the
> > functionality
> > of xp_cmdshell)
> >
> > "Dan Guzman" wrote:
> >
> >> The solution depends on what you are executing via xp_cmdshell. One
> >> approach is to create a new extended stored procedure to encapsulate the
> >> needed functionality.
> >>
> >> Another method is to move the processing out of SQL Server and into
> >> application code, either on the client or on a server. It is likely that
> >> this is the better approach if your client is concerned about security.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> >> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> >> > ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> >> > are
> >> > using "xp_cmdshell".
> >> >
> >> > xp_cmdshell is an extended stored procedure in Master database. This
> >> > command
> >> > takes a string of dos commands and executes them in the DOS command
> >> > shell.
> >> >
> >> > Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> >> > some
> >> > other function or procedure or something which I can place instead of
> >> > this
> >> > xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> >> >
> >> > Note: The Client doesn't want to use this xp_cmdshell at all.
> >> >
> >>
> >>
> >>
>
>|||You might consider splitting the stored procedures that currently execute
xp_cmdshell into separate procs. This would allow you to replace
xp_cmdshell functionality with a CmdExec job step. For example:
1) Transact-SQL step: EXEC proc 1
2) CmdExec step: BCP
1) Transact-SQL step: EXEC proc 2
Your client might be more receptive to this method since the command runs
outside the SQL Server service process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:0299E709-4A85-4158-B9A6-E62B1722765A@.microsoft.com...
>
> There is some processing done in a stored procedure and this extended
> stored
> procedure is called after that. Once the xp completes its job, some more
> processing done in the original stored procedure.
> Also there are many stored procedures which internally call xp for
> different
> tasks like bulk copy etc. Most of these stored procedures are scheduled
> SQL
> tasks and are not called from any application. So replacing this
> functionality in application code can be ruled out.
> Thanks for your inputs/suggestions...
> "Dan Guzman" wrote:
>> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
>> So why are you using xp_cmdshell for this task? Is there some reason you
>> cannot perform file manipulation like this directly from you application
>> code?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
>> > Thank You for your response.
>> >
>> > A string of DOS commands is the input "xp_cmdshell".
>> >
>> > Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
>> >
>> > Which means we need some way in which we can take a string (which is a
>> > valid
>> > DOS command) and execute it in DOS command shell (which is the
>> > functionality
>> > of xp_cmdshell)
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> The solution depends on what you are executing via xp_cmdshell. One
>> >> approach is to create a new extended stored procedure to encapsulate
>> >> the
>> >> needed functionality.
>> >>
>> >> Another method is to move the processing out of SQL Server and into
>> >> application code, either on the client or on a server. It is likely
>> >> that
>> >> this is the better approach if your client is concerned about
>> >> security.
>> >>
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
>> >> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>> >> > ISSUE (in SQL Server 2000): Some Client applications' stored
>> >> > procedures
>> >> > are
>> >> > using "xp_cmdshell".
>> >> >
>> >> > xp_cmdshell is an extended stored procedure in Master database. This
>> >> > command
>> >> > takes a string of dos commands and executes them in the DOS command
>> >> > shell.
>> >> >
>> >> > Now the Client wants a replacement for this xp_cmdshell . i.e., I
>> >> > need
>> >> > some
>> >> > other function or procedure or something which I can place instead
>> >> > of
>> >> > this
>> >> > xp_cmdshell in the respective stored procedures using this
>> >> > xp_cmdshell.
>> >> >
>> >> > Note: The Client doesn't want to use this xp_cmdshell at all.
>> >> >
>> >>
>> >>
>> >>
>>