File Handling A Different Way

Question

File Handling A Different Way

shashank-666535

Old Hand

Points: 348
More actions
April 21, 2008 at 8:54 pm

#380086

Comments posted to this topic are about the item File Handling A Different Way

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 1

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 SSCrazy Points: 2951 More actions · Answer 2

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 Valued Member Points: 63 More actions · Answer 3

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 SSCrazy Points: 2490 More actions · Answer 4

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 SSC-Insane Points: 20244 More actions · Answer 5

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

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 SSC-Insane Points: 20244 More actions · Answer 7

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 8

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 Old Hand Points: 319 More actions · Answer 9

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.

rsconnolly SSC Journeyman Points: 90 More actions · Answer 10

I cannot read the sample code on the form. All I see is a horizontal scroll bar.

Anipaul SSC-Insane Points: 24681 More actions · Answer 11

Hi Shashank

I felt something missing in the article. Overall the article seems incomplete. The process may not the best but you tried something different. 🙂

JMI SSChampion Points: 11831 More actions · Answer 12

Does it work for everybodey?

When I run it form QA it keeps creating an empty file.

exec master..xp_cmdshell 'echo "XXX-XXX" >> c:\code\QueryResults.txt'

Thanks,

Rob

Phil Factor SSC-Insane Points: 20244 More actions · Answer 13

There are a lot of uses for this technique. Here is an example from Nigel Rivett for doing FTP in a stored procedure

http://www.nigelrivett.net/FTP/s_ftp_GetFile.html

he also has a clever way of writing format files for BCP in the same way. Naturally, one wouldn't give the normal application user any access to this!.

Best wishes,
Phil Factor

Your Name Here SSCrazy Points: 2344 More actions · Answer 14

I have to agree with Phil. "New" is kind of misleading since any DBAs worth their paycheck knew about using xp_cmdshell about 15 minutes after it was introduced back around SQL 4.21 .

Given the very narrow constraint of reserving this file creation to the handful of people blessed with membership in the sysadmins role, used for specialty troubleshooting sessions and subsequent mandatory file cleanup I'd say the article's suggestion has merit. In my environments that contain HIPAA data, this would be enabled only after that Ghost Whisperer lady put me at peace by shoving me into "the light" because saying "over my dead body" wouldn't be enough.

Also, it's pretty shallow-minded to overlook that fact that while one execution of this command may seem harmless and convenient, I look at it from the fact that I have thousands of concurrent connections 7x24. Simply "appending to a file" can run my server out of space in a heartbeat if poor choices are made. In my humble opinion, [absolutely nothing] should consume space on my server's hard drives that I don't have control over.

Sound harsh? I'm the "nice guy." My security counterparts would have a field day. Shashank would get hit harder than a bag of jellybeans at a daycare.