SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File Handling A Different Way


File Handling A Different Way

Author
Message
shashank-666535
shashank-666535
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 93
Comments posted to this topic are about the item File Handling A Different Way
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5239 Visits: 3889
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
Knut Boehnert
Knut Boehnert
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 382
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.
will.swift
will.swift
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.
chisholmd
chisholmd
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 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)
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4812 Visits: 3031
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
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5239 Visits: 3889
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
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4812 Visits: 3031
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
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5239 Visits: 3889
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
Samuel Clough
Samuel Clough
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search