Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

XP_CMDSHELL - Secuirty Concerns???

Posted on 2006-07-17
10
Medium Priority
?
522 Views
Last Modified: 2009-12-16
I have a question regarding xp_cmdshell
For some reason our production database and the data managment work database is on the same server (bad maagement)

I have been given the task of improving current data management processes
I want to use the xp_cmdshell command to use simple commands for managing files (ie: rename, move, copy delete, dir, etc)

Because of security issues we do not have access to this sp xp_cmdshell on the master database.

As a work around is it possible to put the xp_cmdshell in  sp with the command that I want to use (ie: rename, move, copy delete, dir, etc) and give certain permissons on the sp to run that xp_cmdshell comand.

So the final result would be that if xp_cmdshell was run separtely we would still get permission not granted to run that sp, but the new sp ie: sp_CopyFile would have permission with the xp_cmdshell command in it that we want to run.


0
Comment
Question by:LJordanMS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17123601
No ...
but you could have a job set up to say do your file copying ...

which you where allowed to trigger...  
say by posting the filenames to a table...

alternatively  give the list of files... explain the situation to the DBA/SA and
let them set up the required jobs/processes...

the XP_cmdshell procedure is protected since its so powerfull ...
but that doesn't mean it can't be used in your batch processes
with proper safeguards...


hth
0
 

Author Comment

by:LJordanMS
ID: 17123765
So it would be possible to a create table with the values of say the old file name, new file name, file destination where the DBA could set up a schedule task say run hourly that would look for new records in that table and perform the desired task that I want? rename file or move file ?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 17124103
Yes...

keep it simple...
limit the possible exposure...
ie just files in a directory... to another directory..


then it shouldn't be a security issue..
 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17124128
it may help to think of it in terms of ...

getting them to do operation X...

rather than physically specifying that they need to move files ... a , b, and c to ..

although x is moving files..

hth
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 17124385
Or, write stored procedures to do the movement using sp_OA commands on the Scripting.FileSystemObject. Little more involved, but no xp_cmdshell needed.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17124781
SP_OA  ...

similar problem however in that there availablity is restricted to SA..  
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 17125446
Good point. Still, I'd rather expose those than xp_cmdshell. Any day.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17125871
I want to use the xp_cmdshell command to use simple commands for managing files (ie: rename, move, copy delete, dir, etc)

You have to evaluate the profit/cost of enabling xp_cmdshell. If your site is available through the web in any way then do not enable it (it is an open door to hackers to get on your network). You can use it to manage files, but there are better, secure ways (sp_OA, for example (SQL-DMO), vbscripts, etc). I have worked in places where xp_cmdshell was locked down and never used (usually enterprise-level companies) and small companies where they used it to run tasks. If you use it to schedule jobs then the Windows Task Scheduler is a better choice - it has better handling and allows you to kill a job if it has been running past a certain interval.

As a work around is it possible to put the xp_cmdshell in  sp with the command that I want to use (ie: rename, move, copy delete, dir, etc) and give certain permissons on the sp to run that xp_cmdshell comand.

This was changed with 2000, sp 3. Until then xp_cmdshell could only be used by sysadmins. It is possible to grant the rights to use xp_cmdshell to others. When you grant the rights to a login that is not sysadmin you have to set the account that is used to run the xp and any programs it invokes. SP3 changes SQL by making the non-sysadmin accounts based on the value of the SQL Server Agent Properties: Job System:  "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job" checkbox.

This link has good information about using xp_cmdshell: http://www.databasejournal.com/features/mssql/article.php/1580041
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question