January 13, 2013 at 3:21 am
Dear Experts,
I am kinda stuck into a situation where i am planning to automate one manual process.
On the daily basis we get almost 7 files from another system and to process them we have to change there name to one specific name so that system can pick it up automatically.
For example :- Daily files name is
Sql_112233.ipm
Sql_445566.ipm
Sql_778899.ipm
And to process these files in our application we have to change the file name to Sql.ipm :crazy:only and in particular location because our application is hard coded with this behavior.:angry:
My idea was to copy these files out side target folder and move these file one by one and rename them as they get moved in target folder so that they can be processed as per application requirements.
Here i am looking for a way to copy these file one by one and rename them as well once they are in target folder. Can we achieve this using the combination of sql and cmd.
Please do let me know please and Many Thanks in Advance for all you help.
Regards
NU
January 13, 2013 at 7:10 am
Yes you can achieve what you want all from SQL, look up xp_cmdshell, you can use this to execute DOS commands to move and rename your files
***The first step is always the hardest *******
January 13, 2013 at 8:27 am
I would not recommend using xp_cmdshell for this. I recommend keeping the option disabled on your instance if at all possible. Truthfully I would not do this work in T-SQL at all but if you must then I would urge you to use the SQLCLR instead.
With the introduction of SQL 2005 Microsoft began deprecating some of its own Extended Stored Procedures that it provides within SQL Server and designated the creation of new User-defined Extended Stored Procedures as a deprecated activity in favor of using the SQLCLR.
There are some SQLCLR libraries available on CodePlex that will help you interact with the file system to achieve what you want. No .NET coding will be required. Here is one that will do what you are looking to do:
http://filesystemhelper.codeplex.com/[/url]
> Utility.DirectoryList
> Utility.FileMove
> Utility.FileRename
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 9:55 am
Heh.... absolutely incredible. Someone (actually, I know/respect the fellow and I'm going to have to ask him what he's thinking) is writing CLRs to replace DOS functionality... and it's just as dangerous when you have things like "Utility.DirectoryDelete" and "Utility.DirectoryDeleteContents". I applaud the attempt at increasing security but allowing such a thing and calling it "more secure" is just insane.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2013 at 10:14 am
One of the many advantages of using the SQLCLR over xp_cmdshell for interacting with the file system is that if the user connected to SQL Server under Windows Authentication you can use their credentials to access the file system instead of a proxy account, or worse, the SQL Server service account. This would prevent a user from altering anything on the file system (local or network) for which they were not expressly granted permissions too and that is something you cannot necessarily achieve with xp_cmdshell. This is not to mention the fact that when used this way the SQLCLR allows us to audit a specific user's actions meaning the action will not show up as having been carried out by the proxy account or the SQL Server service account.
I am speaking in general though, in disagreement with the above statement that implies that using the SQLCLR offers no advantages over using xp_cmdshell when interacting with the file system, which is flatly incorrect.
That said, I am not sure if the library I linked to on CodePlex uses the connected user's credentials or not, but if that were a requirement the code could be easily modified to achieve it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 10:23 am
opc.three (1/13/2013)
One of the many advantages of using the SQLCLR over xp_cmdshell for interacting with the file system is that if the user connected to SQL Server under Windows Authentication you can use their credentials to access the file system instead of a proxy account, or worse, the SQL Server service account. This would prevent a user from altering anything on the file system (local or network) for which they were not expressly granted permissions too and that is something you cannot necessarily achieve with xp_cmdshell. This is not to mention the fact that when used this way the SQLCLR allows us to audit a specific user's actions meaning the action will not show up as having been carried out by the proxy account or the SQL Server service account.I am speaking in general though, in disagreement with the above statement that implies that using the SQLCLR offers no advantages over using xp_cmdshell when interacting with the file system, which is flatly incorrect.
That said, I am not sure if the library I linked to on CodePlex uses the connected user's credentials or not, but if that were a requirement the code could be easily modified to achieve it.
Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.
January 13, 2013 at 10:33 am
SQLCLR is worth trying..Utility.FileSearchInternal and Utility.FileRename will do the trick... lemme try this...
many thanks for the suggestions...
January 13, 2013 at 10:36 am
Lynn Pettis (1/13/2013)
Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.
Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.
EXEC sys.sp_configure
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 10:39 am
opc.three (1/13/2013)
Lynn Pettis (1/13/2013)
Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.
EXEC sys.sp_configure@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;
I know how to enable CLR. The problem with your solution comes in when you AREN"T allowed to use CLR. That is all I was saying.
January 13, 2013 at 10:57 am
Lynn Pettis (1/13/2013)
I know how to enable CLR. The problem with your solution comes in when you AREN"T allowed to use CLR. That is all I was saying.
No offense meant. That was for the OP and future onlookers just to show how to enable it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 12:38 pm
I think having the ability to audit is definitely the definitive reason to use CLR over xp_CmdShell. I think it's great that the user the hacker broke in as will be audited and be the one to get all the blame. 😉
Auditing an attack is a bit like discovering the brand of matches that burned down the barn. Using CLR instead of xp_CmdShell will not prevent any attack. Even taking the extreme measure of deleting the xp_Star DLL (deletes xp_CmdShell) won't prevent, lessen the ferocity of, or reduce the damage done in any attack. Any reasonably skilled attacker that can get in as "SA" in SQL Server can get to a command prompt even with xp_CmdShell deleted.
Thinking that using CLR instead of xp_CmdShell will somehow protect you from attack is a false sense of security that will cost you dearly. Attackers just won't use your bloody CLRs. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2013 at 1:00 pm
Jeff Moden (1/13/2013)
I think having the ability to audit is definitely the definitive reason to use CLR over xp_CmdShell. I think it's great that the user the hacker broke in as will be audited and be the one to get all the blame. 😉Auditing an attack is a bit like discovering the brand of matches that burned down the barn. Using CLR instead of xp_CmdShell will not prevent any attack. Even taking the extreme measure of deleting the xp_Star DLL (deletes xp_CmdShell) won't prevent, lessen the ferocity of, or reduce the damage done in any attack. Any reasonably skilled attacker that can get in as "SA" in SQL Server can get to a command prompt even with xp_CmdShell deleted.
Thinking that using CLR instead of xp_CmdShell will somehow protect you from attack is a false sense of security that will cost you dearly. Attackers just won't use your bloody CLRs. 😉
I never mentioned that a comparable file system manipulation routine using the SQLCLR was more secure than one using xp_cmdshell. You mentioned security. It's two paths to the same destination in terms of manipulating the file system itself. I lead with saying that I would not do this type of work in T-SQL at all and I stand by that 100%. However, some folks either want to or are being forced to do things in T-SQL against their will so as a more robust, managed code alternative, I merely suggested that there are options available when using the SQLCLR that simply are not available when using xp_cmdshell making it a better overall option. Microsoft seems to subscribe to the same thought process as well given their deprecation announcement regarding User-defined Extended Stored Procedures as of SQL Server 2005. It may just be a matter of time before they deprecate xp_cmdshell and rollout a SQLCLR object that exposes access to a PowerShell prompt to take its place, which I wouldn't bother using either by the way.
The ancillary benefit for enabling and using the SQLCLR is that xp_cmdshell remains disabled on the system, and in lieu of moving file system manipulation activities out of the SQL Server altogether, and thereby successfully resisting the urge to extend SQL Server as an application server by exposing a conduit to an operating system shell, that is a trade I would take 100% of the time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 1:41 pm
That's precisely what I'm afraid of, Orlando. People saying they'd take that kind of a trade 100% of the time. I know you know better (the larger picture) but there are people that don't. They'll believe that just because they're using a CLR and that that they've got xp_CmdShell disabled, that their system is secure and that an attacker can't use xp_CmdShell or any other method to get to a command prompt with extraordinary privs intact. You and I both know better than that.
To wit, if you have a properly secured system, it isn't going to matter if you have xp_CmdShell turned on or not. In fact, you could have stored procedures that use it and your system would still be properly locked down. xp_CmdShell has nothing to do with security risks and does not enable or prevent any risk. Bad security does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2013 at 2:22 pm
Security and risk mitigation have to be layered and the risk-to-benefit ratio for xp_cmdshell simply makes it a non-choice for me. I put SQLCLR objects with EXTERNAL_ACCESS and UNSAFE in the same category by default and there are ways to have the SQLCLR enabled and only allow SAFE assemblies.
As I said before, my stance is that none of these types of operations should be initiated from within SQL Server. Having xp_cmdshell disabled means there is one less attack vector available. And I agree, there is little defense against a malicious sysadmin that knows how to work some magic (such as yourself and a handful of others we know that frequent this site) to re-instate the dll (if removed) and enable xp_cmdshell (if disabled) to get to a cmd shell prompt but that is no reason to ignore that the roadblock will often suffice, or at least hinder enough to where another layer of auditing or controls are tripped and the exposure can be mitigated before any real loss of assets occur.
I see the extension of your argument as: if you have sysadmin, because there are ways to circumvent the system to get to a cmd shell prompt even if xp_cmdshell is disabled or the underlying dll removed from the system then we should not bother enacting these additional roadblocks and I wholeheartedly disagree. I think it is worth steering operations that involve the file system to processes initiated outside the SQL Server, e.g. PowerShell, and if they must stay in SQL Server then use a managed code option, e.g. the SQLCLR.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 7:44 pm
Hmmm. I can see your point. I mean about not doing file handling in SQL Server and that those types of things should be done well outside of SQL Server. Of course, then you have an additional system to secure and privs to control and learn and document and audit and maintain.
And, yes, you're correct. Then extension of my argument is and always has been that if someone can get in with SA privs, then there is no way to stop the attacker before the attacker can do extremely grave damage at any level even if xp_CmdShell has been deleted. It won't even slow an attacker down long enough for anyone to realize they're being attacked before they've done what they setout to do. It'll simply be too late and your auditing will simply show that someone got in as "little Bobby" and threw a world of hurt on the server. Disabling xp_CmdShell as "another roadblock" is a total waste of time. Someone attacking as an "SA" can turn it back on in the first few miliseconds they're "in". All having it turned off does is give a very dangerous false sense of security. It's a bit like holding a dancing veil up to shield against a nuclear blast. Looks real pretty and is associated with some great moves but does no real good.
I'll also say that unless someone can get in as "SA", they won't be able to use xp_CmdShell even if it's turned on and all greased up with a proxy. I will absolutely agree that allowing any non-SA prived user to directly use xp_CmdShell through a proxy is one of the most incredibly stupid things that anyone could do. I can't actually believe that MS allowed for such a thing especially since it's just not needed.
To be absolutely clear, I also believe that only the DBAs themselves should have any privs other than PUBLIC and exec privs on a handful of stored procedures. Users/logins/apps shouldn't even need even db_datareader or db_datawriter.
Also, what is to prevent the external systems you speak of from being an attack point? Nothing, really. It's no different than doing file handling via any tool whether it be SSIS, xp_CmdShell, some third party tool, PowerShell, or the DOS prompt, itself. As a DBA, I can much more closely guard what goes on from SQL Server than I can when some 3rd party software comes into play or some "qualified" user is allowed in at the DOS Prompt to run PowerShell. I can also get my job done much more quickly by automating jobs in the environment I know best. Shoot... I even use xp_CmdShell to call PowerShell.
Just to be clear, I never give individuals or apps the privs to run xp_CmdShell directly. There's just no need. A user with only PUBLIC privs can be given the privs to execute a stored procedure that does the job correctly. The truth is, users have no business running even those. The system should take care of such things auto-magically.
And, no... I'm not saying that you shouldn't put obstacles up in front of would-be attackers. I'm just saying that turning off xp_CmdShell is no obstacle to an attacker what so ever because they're expecting it to be turned off and have already included different attack vectors in their code to easily get around it.
The bottom line is that you and I agree to disagree on this subject. That's a very good thing, actually. That means that when you and I have such a conversation (does seem to be a lot), people are really going to benefit because they get to see both sides of the story to make an intelligent decision based on their particular needs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply