InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:55:58 GMT20http://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]Jeff Moden (1/13/2013)[/b][hr]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.[/quote]Thank you for acknowledging there is some value in moving file system manipulation work outside of SQL Server. That might be the first time I have heard you say anything close to that. For the record I do not view the app server that would handle this work as an "additional system" to lock down. It's part of the same system and the security is layered and orchestrated across all parts of the system. File handling systems are quite simple to lock down and audit, at least compared to locking down a SQL Server that has xp_cmdshell enabled or file handling routines implemented in SQLCLR that are not allowing the callers identity to pass through to the file system.[quote]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.[/quote]I like the analogy but all of that can be blocked at the OS level. It was monitored at one shop I was in and I received phone calls about it regularly asking if we could "stop using xp_cmdshell from SQL Server." Which unfortunately we could not because the legacy application that used it was designed as if the original architects were being paid a bonus for every call they made to it in their stored procs.[quote]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.[/quote]On these two points we agree 100%. I would take it a step further and say that allowing one to get to the file system from within T-SQL at all was a major mistake.[quote]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.[/quote]I see. So if someone were to prevent you from using xp_cmdshell then it would be bad for you. Is it that having xp_cmdshell makes you feel more productive than any other set of tools you have acquainted yourself with? As a litmus test, if someone offered you a job making 50% more than you make now with the opportunity to gain a very high level of job satisfaction based on the job description but the pre-condition was that "corporate" has banned xp_cmdshell, would you not entertain the offer?[quote]Just to be clear, I never give individuals or apps the privs to run xp_CmdShell directly.[/quote]You're missing the giant risk right under your nose. A DBA [i]is[/i] an individual and the fact that they have the title of DBA makes them no less likely to want to steal data or become disgruntled and put the company at risk.[quote]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.[/quote]There are plenty of obstacles within and outside SQL Server including having something running in Windows that can intercept and block cmd shell requests emanating out of SQL Server.[quote]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.[/quote]It does seem to occur quite a lot but it's not a bad dialogue to reprise once in a while :-)Sun, 13 Jan 2013 20:50:57 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxHmmm. 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.Sun, 13 Jan 2013 19:44:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxSecurity 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.Sun, 13 Jan 2013 14:22:07 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxThat'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. ;-)Sun, 13 Jan 2013 13:41:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]Jeff Moden (1/13/2013)[/b][hr]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. ;-)[/quote]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.Sun, 13 Jan 2013 13:00:08 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxI 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. ;-)Sun, 13 Jan 2013 12:38:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]Lynn Pettis (1/13/2013)[/b][hr]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.[/quote]No offense meant. That was for the OP and future onlookers just to show how to enable it.Sun, 13 Jan 2013 10:57:41 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]opc.three (1/13/2013)[/b][hr][quote][b]Lynn Pettis (1/13/2013)[/b][hr]Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.[/quote]Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.[code="sql"]EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1;RECONFIGURE;[/code][/quote]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.Sun, 13 Jan 2013 10:39:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]Lynn Pettis (1/13/2013)[/b][hr]Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.[/quote]Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.[code="sql"]EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1;RECONFIGURE;[/code]Sun, 13 Jan 2013 10:36:24 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxSQLCLR is worth trying..Utility.FileSearchInternal and Utility.FileRename will do the trick... lemme try this...many thanks for the suggestions...Sun, 13 Jan 2013 10:33:31 GMTnitinuniyalhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspx[quote][b]opc.three (1/13/2013)[/b][hr]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.[/quote]Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.Sun, 13 Jan 2013 10:23:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxOne 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.Sun, 13 Jan 2013 10:14:09 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxHeh.... 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.Sun, 13 Jan 2013 09:55:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxI 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:[u][url=http://filesystemhelper.codeplex.com/]http://filesystemhelper.codeplex.com/[/url][/u]> Utility.DirectoryList> Utility.FileMove> Utility.FileRenameSun, 13 Jan 2013 08:27:49 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxYes 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 filesSun, 13 Jan 2013 07:10:18 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1406460-149-1.aspxDear 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.ipmSql_778899.ipmAnd to process these files in our application we have to change the file name to [b]Sql.ipm[/b] :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.RegardsNUSun, 13 Jan 2013 03:21:45 GMTnitinuniyal