October 13, 2011 at 10:33 am
In most of my environments, I was able to talk people into using methods like jobs, service broker, SSIS, etc. But in most troublesome environment - the one that people were just too scared of having to learn something new - I implemented the CLR procedures listed below. The hilarity in this case, for me, is that people were too scared to use other things, yet, they were perfectly OK with using CLR, even though none of those people had any idea of what it is, go figure.
- clrFileCreateDelimitedFromQuery - extremely similar to the code in another post/article in this site
- clrFileExist
- clrFileMove
- clrFolderCreate
- clrFolderListFiles - used instead of clrFileExist in those cases where the file name is not known at execution time
- clrFtpDeleteFile
- clrFtpGet
- clrFtpListFiles
- clrFtpPutFromQuery
This code is signed and runs with an account with only the necessary privs outside of the SQL server.
xp_cmdshell code to BCP data into the database was replaced with BULK INSERT.
October 13, 2011 at 11:02 am
For file functions, which was one of the reason I used to use xp_cmdshell I wrote a SQLCLR object. IT is freely available on codeplex at: http://nclsqlclrfile.codeplex.com/[/url]
Give it a try, let me know if I missed anything..
Unlike the previous poster though I didn't implement FTP functionality in mine..
CEWII
October 13, 2011 at 11:06 am
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 13, 2011 at 11:14 am
opc.three (10/13/2011)
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
Agreed. But in that environment, there was a lot of resistance from the higher ups because the business rely so much on this one system. In exchange, they agreed to write no new code that perform any of those operations in SQL, as they have the easy capability to do it from the app itself.
As for the bulk export, I really cannot come up with a reason as to why they would not have done that at the same time they implemented bulk insert.
October 13, 2011 at 12:06 pm
opc.three (10/13/2011)
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
Generally agreed, as far as misguided I do not.
Given a particular system it may make as much if not more sense to handle the file operations, whether they be local or on a file share that it is to handle them externally.
You can certainly argue the general point that we probably shouldn't be moving files about in SQL, and in many if not most cases I would agree. But like many things it depends.
The particular SQLCLR assembly I wrote has only been used a few times, where it made sense. But for that use it negated completely my need for xp_cmdshell and gave me MUCH more control.
YMMV...
CEWII
October 13, 2011 at 5:06 pm
Jeff Moden (10/13/2011)
...because of those dangerous recommendations, xp_CmdShell continues to receive a black-eye that it really doesn't deserve. ...
I'll walk elbow-to-elbow with you on this idea any day. The results of a poor implementation does not a bad tool make. Even when equipped with knowledge of one way to secure xp_CmdShell you showed me the error of my ways in the past (long before this thread) in terms of blindly bashing xp_CmdShell, and then proceeded to further educate me as to additional ways to secure xp_CmdShell. The result is that I now offer a balanced appraisal of xp_CmdShell, at least closer to balanced than before. That said, while my tone is more objective, the end result of steering folks away from it in favor of a managed language solution remains the same for me. The effort required to properly educate folks, plus the upside of moving to a managed language solution lands me on "do not enable xp_CmdShell, it's not worth it and you'll eventually hit a glass ceiling, here, look at this".
Now that's an argument that I agree with 100%. Well done. In fact, that would make for a good introduction to the pair of articles we previously discussed and I'm going to include something to the effect of there being a "lot of better ways to accomplish the same thing but, if you're somehow backed into a corner with no choice but to use it, here's one of the correct ways to use it."
Indeed it would! I have certainly tempered my demeanor towards folks choosing to implement xp_CmdShell. I now reserve the heat for those recommending it with no eye towards security. These conversations are invaluable to my growth and development as a professional and a person and I appreciate your input Jeff, most would not take the time.
Shift gears slightly, how did that one project where you were compelled to use xp_CmdShell because of large amounts of legacy code go? Where you able to lock the system down as we discussed or where there caveats that we didn't know about when we first discussed the problem?
Effecting a system change not requested by "the business" at my current station is like fill in any idiom similar to "pulling teeth" here ...even for serious security exposures such as this. It's concerning, really. I completed the proof-of-concept (POC) in a representative environment shortly after you shared your very elegant road map to arrive there (thanks! and on your vacation no less!) however the manager that stewards the system in question deferred the issue for weeks and is now away on sabbatical until the end of this month. Upon returning I will have their ear to demo the POC and outline the refactoring steps necessary to "swap in" a secure xp_CmdShell implementation into an existing non-secured environment. I still have plans to write the one of two articles we discussed but it is on the shelf for now. I want to "live through" the implementation so I can speak from that place in the article. My outline could not even be considered informal yet. This thread has lit a fire once again, and I carved out some time this weekend to work on it. If all goes well I will have something fit for sharing by Monday.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 13, 2011 at 5:18 pm
Elliott Whitlow (10/13/2011)
opc.three (10/13/2011)
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
Generally agreed, as far as misguided I do not.
Given a particular system it may make as much if not more sense to handle the file operations, whether they be local or on a file share that it is to handle them externally.
Can you cite a specific example of when it has made as much or more sense?
You can certainly argue the general point that we probably shouldn't be moving files about in SQL, and in many if not most cases I would agree. But like many things it depends.
That is precisely my point. It's a preference, or choice by another name. If you access the file system from within T-SQL, then you explicitly chose to do that. How far you're willing to go to avoid a design that includes that (or what is 'reasonable to expect' to borrow a phrase from Craig) is debatable. The point is, there are alternatives, and I will only implement code that accesses the file system (xp_CmdShell, CLR or otherwise) if forced to, e.g. by a manager, client, lead architect on a project, etc.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 13, 2011 at 7:10 pm
Needless to say, I prefer the "all in one place" aspect. MS took the time to write a "suite" of CLR's to implement the HierarchyID... I'd love it if they built a "suite" of file handling CLR's not named "SSIS". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2011 at 9:00 pm
opc.three (10/13/2011)
Elliott Whitlow (10/13/2011)
Generally agreed, as far as misguided I do not.Given a particular system it may make as much if not more sense to handle the file operations, whether they be local or on a file share that it is to handle them externally.
Can you cite a specific example of when it has made as much or more sense?
Without violating any of the privacy agreements I am under.. A web app made file data available to a database server, that data was extracted in real-time and validated, if accepted the file was renamed and stored in a local archive in a separate directory with a different name, these files could be retrieved by the front-end, but often were not again after a point they were deleted. Data was extracted to files and made available to the front-end. The choice was made to retain all the data on one server. Later on the files were stored as BLOB columns.
Given all the dynamics of the application and environment it made sense and continues to make sense.
There is a reason why "It Depends" is used so often.
CEWII
October 13, 2011 at 10:23 pm
Elliott Whitlow (10/13/2011)
opc.three (10/13/2011)
Elliott Whitlow (10/13/2011)
Generally agreed, as far as misguided I do not.Given a particular system it may make as much if not more sense to handle the file operations, whether they be local or on a file share that it is to handle them externally.
Can you cite a specific example of when it has made as much or more sense?
Without violating any of the privacy agreements I am under.. A web app made file data available to a database server, that data was extracted in real-time and validated, if accepted the file was renamed and stored in a local archive in a separate directory with a different name, these files could be retrieved by the front-end, but often were not again after a point they were deleted. Data was extracted to files and made available to the front-end. The choice was made to retain all the data on one server. Later on the files were stored as BLOB columns.
Given all the dynamics of the application and environment it made sense and continues to make sense.
There is a reason why "It Depends" is used so often.
CEWII
Meh...ok
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 13, 2011 at 10:35 pm
Jeff Moden (10/13/2011)
Needless to say, I prefer the "all in one place" aspect.
There is merit in your argument for sure. I would press you for details initially, but as previously stated if you were lead architect of a team I was on I would pipe down and get on board with the approach.
MS took the time to write a "suite" of CLR's to implement the HierarchyID...
Now there's a great example of how to leverage the SQLCLR! The Geography types are another worth mentioning.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 14, 2011 at 12:49 am
A web app made file data available to a database server, that data was extracted in real-time and validated, if accepted the file was renamed and stored in a local archive in a separate directory with a different name
This is the perfect case where we should use FILESTREAM. Else we should have dedicated file storage (outside database server) for actual file storage and should be managed by business logic layer (application server)
October 14, 2011 at 12:55 am
@opc.three,
What's funny about all of this is that xp_DirTree, xp_CmdShell, xp_FileExists, etc, etc, etc all live in a single DLL called XPSTAR.DLL. Why they didn't take it just one step further and make a viable and secure "File Handler" suite is totally beyond me. As you pointed out, at least a "BULK EXPORT" would be handy.
BTW, I did revisit that particular CONNECT item and zinged them a bit.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2011 at 9:01 am
opc.three (10/13/2011)
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
Well, call me "misguided" then. I have many needs to access file information from within SQL, whether for Agent jobs, system maintenance, or ease of T-SQL coding to run SELECT statements based on file information. But maybe you were differentiating between accessing file metadata and file contents?
I wasn't crazy about learning what to me is a very convoluted syntax to create a TVF in CLR, but the result is secure, quick, robust, and reusable. Use of the CLR TVF is controlled the same way any other TVF is, via SQL GRANTs.
Jeff, to answer your request to hear from those of us who voted "Yes, but I can give you a good example of when you HAVE to use it.": I'm one of those heretics. I have xp_cmdshell disabled here, and I don't think enabling it is a good idea. But if I've learned anything from the discussions on this site, it's that "it depends" is usually the right answer. So, I gave an answer that I thought would crack open the conversation some, even though I myself don't have a specific reason to enable xp_cmdshell.
In fact, when I searched on my machines for code or files containing "xp_cmdshell" I only turned up some demo scripts and this item: "8.Calls to xp_CmdShell will not be used without prior approval of the DBA’s". Source? Jeff Moden's Documentation Standards for MS-SQL.doc.
Rich
October 14, 2011 at 9:24 am
rmechaber (10/14/2011)
opc.three (10/13/2011)
Personally I think using the CLR functionality to access files from with a T-SQL context, while preferable over using xp_CmdShell, is still a misguided application of the CLR. It is my position that we should not be doing file system access of any kind from within a T-SQL context, save for OPENXML and BULK INSERT. And yes, T-SQL does need a BULK EXPORT command. Vote for one here 😉http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026
Well, call me "misguided" then. I have many needs to access file information from within SQL, whether for Agent jobs, system maintenance, or ease of T-SQL coding to run SELECT statements based on file information. But maybe you were differentiating between accessing file metadata and file contents?
Hi Rich, no, I was not differentiating. Misguided in terms of the effort spent. Maybe "misplaced effort" is a better way to sum up my position. Why not spend that time coding a PowerShell script where you can move across the boundaries of the file system domain and the database T-SQL domain quite elegantly. There is so much more available in PowerShell for future needs, and more becoming available with each release of SQL Server. Why box yourself into T-SQL and the SQLCLR for Admin tasks?
Resist the urge to turn your SQL Server into an application server, Windows does that far better than SQL Server. And system admin tasks inside SQL Server...no thank you.
I wasn't crazy about learning what to me is a very convoluted syntax to create a TVF in CLR, but the result is secure, quick, robust, and reusable. Use of the CLR TVF is controlled the same way any other TVF is, via SQL GRANTs.
Yes, except that with your use of the SQLCLR you have to set the permission set for the assembly to EXTERNAL_ACCESS or UNSAFE. That is pretty much my guide on this topic. If the assembly cannot be marked SAFE then I need to take a step back and re-evaluate what I am doing. If I find there is an alternative, and am not being compelled to forge ahead by forces beyond my control then I move in a different direction.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 31 through 45 (of 76 total)
You must be logged in to reply to this topic. Login to reply