Archiving data to a read only file

Question

Archiving data to a read only file

Weegee71

SSChasing Mays

Points: 607
More actions
April 19, 2021 at 10:40 am

#3872975

Hi there
I have a table for which archiving functionality is required.
Now I have thought of writing sonmething in t-sql to use BCP to write to csv or caret delimited file
There is a requirement that this file be made tamper proof and read only.
Apart from setting the file attribute to read only, is there a way of putting a lock on the file...so that someone
couldnt read it?

Viewing 3 posts - 1 through 3 (of 3 total)

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

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 1

You could encrypt it with a password. You can use the Data Protection API (DPAPI). Here's an article on it. Another showing how to create a password protected zip file. And another set of examples on certificate encryptions. One of these approaches ought to do the trick. I can't think of any other way to ensure files, outside of a management system, are protected.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 2

If no one is allowed to read it, it might as well be compressed so it doesn't take as much room so I agree with Grant's suggestion of converting it to a password protected ZIP file.

Just make sure that the password is stored somewhere safe. The company should have a "password locker" somewhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Archiving data to a read only file

Cookies on SQLServerCentral