Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

File Handling A Different Way Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 8:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
Comments posted to this topic are about the item File Handling A Different Way
Post #488314
Posted Tuesday, April 22, 2008 12:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
Hello shashank,

In my opinion you should not use the SQL Server DB engine for file handling. I don't know ASP.NET in detail, but I am pretty sure that there must be a quick, easy and secure way to read and write files.
(Of course I have no objections to do file handling via DTS or SSIS packages)

But I would be interested to know the opinions of others.


Best Regards,
Chris Büttner
Post #488376
Posted Tuesday, April 22, 2008 1:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 832, Visits: 327
While it is a nice easy way I wouldn't use it in a customer facing application setup due to the identified security risk.

All it needs is cracking the security context to allow access to xp_cmdshell.

As a DBA using xp_cmdshell for internal purposes is a different matter. It really is a very fast way to get file and directory information and the nice thing is that this information can be loaded into a table variable.
Post #488390
Posted Tuesday, April 22, 2008 1:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 10:19 AM
Points: 1, Visits: 19
Much safer to impersonate. Take a look at http://msdn2.microsoft.com/en-us/library/ms998283.aspx that shows how to use the Aspnet_regiis.exe tool to encrypt sections of your configuration files.
Post #488391
Posted Tuesday, April 22, 2008 2:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 2:35 PM
Points: 194, Visits: 86
Thanks for the idea that does seem like an easy and performant way to write text files from SQL.

As you noted not very secure for public facing web apps. Perhaps its was simply a matter of assigning the ASPNET account write permissions to a single directory and then forbid scripts or executables from that directory. I imagine it would be rather harder to launch an attack from that.



Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Post #488420
Posted Tuesday, April 22, 2008 2:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 11:44 AM
Points: 561, Visits: 2,417
I always prefer to use COM scripting from within TSQL for this sort of job. It is, however, perfectly simple to arrange your database security so that the use of xp_cmdshell is not dangerous. I have to admit that my only real criticism of this article is the title. It is not file-handling, and it is not different. It is one of the oldest tricks used by grey-muzzled SQL Server/Sybase DBAs. It is a rather limited method, though, when trying to write the '>' character to a file, or writing long lines.


Best wishes,

Phil Factor
Simple Talk
Post #488422
Posted Tuesday, April 22, 2008 3:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
The problem with this is, that it is also perfectly simple to un-secure your database with xp_cmdshell.
In general you should use the right tools for the right tasks.
And to use xp_cmdshell to move file handling from ASP.NET to SQL Server is the wrong decision in my opinion.

Btw, there is some useful information for those that want to secure xp_cmdshell:
http://msdn2.microsoft.com/en-us/library/aa175398(SQL.80).aspx


Best Regards,
Chris Büttner
Post #488445
Posted Tuesday, April 22, 2008 4:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 11:44 AM
Points: 561, Visits: 2,417
Christian,

Of course, the use of OLE automation or the command shell presents a particular problem for your database security. The answer is, of course, to meet these security concerns if you need to use these facilities (well, I'd go further and say that you need to make your database secure anyway, whether you use them or not). Your conclusion that you need to do all file handling in ASP.NET, rather than make your SQL Server secure, is puzzling. You are implying that it is impossible to do file handling in SQL Server because of the security issues. I maintain that, if the security in your database is correctly designed, it is perfectly OK to do so.

I apologise for arguing the point, but I'd be the first to be worried if there really was a way to 'un-secure' the database systems I design. (other than getting the key to the server room!)





Best wishes,

Phil Factor
Simple Talk
Post #488468
Posted Tuesday, April 22, 2008 5:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
Hi Phil,

Doing file handling in ASP.NET doesn't mean you can't make your SQL Server secure. And I am not denying that it is possible to do file handling in SQL Server securely. What I am saying is that "if there is a more appropriate way of doing something, then use this way". So far I can see no benefit in moving the file handling from ASP.NET to SQL Server. I even doubt that the file handling is faster with xp_cmdshell than with ASP.NET (but as I mentioned before, I am not a ASP.NET person so these are just my assumptions). The original reason for the author for moving the file handling to SQL Server was the missing knowledge of storing credentials securely in ASP.NET.

And Phil, my intention was not to question your database design.
I am just raising my concern that your initial design can be compromised as soon as a new developer has do code a change request, but isn't aware of the security concerns. This can quickly make your calls to xp_cmdshell insecure. (Ok, you can then blame it on the boss who is not paying enough for your colleagues training


Best Regards,
Chris Büttner
Post #488500
Posted Tuesday, April 22, 2008 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:03 AM
Points: 27, Visits: 108
I'm surprised by this article. For one thing, as already stated, this stored proc should never be enabled. Secondly, why use a database to do file i/o? I wouldn't want someone writing files on my database server. It sorta smells like a hack.

To me the obvious solution here is that if you don't want to get asp.net the rights to write to a file, simply create a wcf service on an app server and let it do file i/o for you. It's much cleaner and more secure.

This article is certainly creative, but I think there are better ways to accomplish this.
Post #488557
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse