August 25, 2008 at 11:11 am
We have a need to write a proc to take a local file, rename it and move it to another location on the server's file system.
Is this possible?
August 25, 2008 at 11:23 am
It's possible using xp_cmdshell. You'll have to configure your server using the surface area configuration tool to allow that sproc to be called.
I say possible, of course. Not always advisable to have a SQL process messing with the OS... which is why it's now disabled by default.
--SJT--
August 25, 2008 at 12:06 pm
In addition to xp_CmdShell, you have a few other options. Enabling xp_Cmdshell can be a security problem - these other options will be a bit more secure.
1) Create a CLR Stored Procedure. Your procedure will be able to interact with the file system and this is risky, but you have not enabled anything else to be able to, so if it is written well, it will not be able to cause major havoc in the wrong hands.
2) Use the job agent. If you have a set file and and location, you can create a job that will move your file. Then your procedure just needs to call sp_start_job. This can be more secure and limits the ability to do anything else, but it would first mean your file name and location cannot really vary and you cannot allow more than one user to run the process at once.
I would typically recommend using a CLR stored procedure for something like this.
August 25, 2008 at 12:29 pm
The filename will change depending on a record that needs to be inserted into a DB.
I will look further into writing a CLR SP. We are using 2005 so it shouldn't be a problem.
August 25, 2008 at 2:43 pm
Hello,
Another way to do it is to use a .bat command file and use bcp to out the data and bcp in to load the data to the other server.
Renaming can be handled by loading the bcp in data feed to a different table name easily.
Some thing to think about.
Regards,
Terry
August 25, 2008 at 2:50 pm
Michael Earl (8/25/2008)
In addition to xp_CmdShell, you have a few other options. Enabling xp_Cmdshell can be a security problem - these other options will be a bit more secure.1) Create a CLR Stored Procedure. Your procedure will be able to interact with the file system and this is risky, but you have not enabled anything else to be able to, so if it is written well, it will not be able to cause major havoc in the wrong hands.
2) Use the job agent. If you have a set file and and location, you can create a job that will move your file. Then your procedure just needs to call sp_start_job. This can be more secure and limits the ability to do anything else, but it would first mean your file name and location cannot really vary and you cannot allow more than one user to run the process at once.
I would typically recommend using a CLR stored procedure for something like this.
Sorry, Micheal, I cannot see how either of these is more secure that than a well written proc calling xp_CmdShell? And SQL CLR is certainly a whole lot more trouble, and arguably not as easily auditable by the DBA (since they cannot inspect the code in situ).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2008 at 5:01 am
Once you enable xp_Cmdshell, it can be used for a lot of things. If the CLR procedure can only move a file from one location to another, it ends up more secure simply by restricting what it is able to do. Anyone with sa permissions will still be able to run the proc, but at least they will not be able to execute batch files or run system commands.
August 26, 2008 at 6:23 am
Michael Earl (8/26/2008)
Once you enable xp_Cmdshell, it can be used for a lot of things. If the CLR procedure can only move a file from one location to another, it ends up more secure simply by restricting what it is able to do. Anyone with sa permissions will still be able to run the proc, but at least they will not be able to execute batch files or run system commands.
But you're comparing apples and oranges here. You are comparing an entire security option and facility (xp_CmdShell) to a specific implementation of a single instance (your proc) of a security option and facility (SQLCLR).
Turning xp_CmdShell on or off does not enable anyone on your server to do whatever they want, they have to be SysAdmin's. On the other hand, I believe that mere DBO's can use SQLCLR if you enable that option.
If an SA uses xp_CmdShell, it is still limited to whatever system/domain rights you have restricted the SQL Server Service, just like any DBO who uses SQLCLR (the code/assembly security of SQLCLR is not really security at all, but rather a reliability feature for SQL Server).
If the CLR procedure can only move a file from one location to another, it ends up more secure simply by restricting what it is able to do. Anyone with sa permissions will still be able to run the proc, but at least they will not be able to execute batch files or run system commands.
And likewise, if you write a procedure using xp_CmdShell appropriately, it will only be able to do what you want it to do.
Sorry, I am still not seeing much difference there. Except that xp_CmdShell is easier and that the DBA cannot readily inspect the running code for SQLCLR.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply