Re: FlatFile Encryption using FILESTREAM

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

If I can widen the scope of this a bit... (note that my largest file is under
a half a meg so it appears that my flat file solution is still the better of
the mix disallowing for the maintenance - also looks like it may require
VS2010 which is still in beta - short of the fact that it must still be read
first using filestream and then the decryption applied in this scenario.)

If a folder on a domain contains confidential flat files (not my case), the
files can be added to a database filegroup and read using FILESTREAM. Do I
understand this correctly?
As in:
http://msdn.microsoft.com/en-us/library/cc716724.aspx
"Performance matches the streaming performance of the file system.
The size of the BLOB is limited only by the file system volume size.
FILESTREAM columns do not need to be managed separately. They are fully
integrated with maintenance operations, such as backup and restore.
SQL Server ensures data-level consistency between the relational data in the
database and the unstructured data that is physically stored on the file
system by providing full transactional support.
FILESTREAM data is completely integrated with the SQL Server security model"
-----------------------------

In your blog, "Note that this encryption is made by default when you create
the DbMK, but it may be intentionally dropped..."

I am looking at CLR possibilities for applying the SMK against the database.
And maybe this falls outside of the realm of your expertise, but I am
wondering. Does this mean the database can be distributed with the
application in its encrypted form provided there is a methodology available
for retrieving the SMK, then using another server, mounting the distributed
..mdf, after the SMK is applied and circumventing encryption? I am hoping the
SMK can be applied only when the user makes a query.

Or put in layman's terms, if the user registers the software and receives a
registration key to programmatically unlock the database [application learns
the SMK password], does the possibility exist for the user to be able to
access the data from another server because the DbMK requires the SMK be
permanently applied against the data or (hopefully) only the CLR can access
the database because it holds the key privately?

Is this making sense? It is a difficult question to ask. I have seen
situations where a database was modified to be secure (not necessarily
encrypted) and when it was remounted on another server, the (security)
modifications disappear. It sounds like this is not possible but I am
worried that if the key is applied via the application, there may also be a
means for reading that .mdf by mounting it elsewhere. Seems to me that
logically this process could be put in place to use the database to control
security on files. I am wondering if it can control the files on a
distributed application.

In my scenario, an application distribution, I can overlook the worry of
backups because the database is read only and can be restored with a
reinstall. The data on my local server is backed up without encryption as it
is not publically accessible.
--
Regards,
Jamie


"John Bell" wrote:


"thejamie" <thejamie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7EE858F5-3E30-467A-8428-C4B241D9117C@xxxxxxxxxxxxxxxx
I have a legacy database composed of distributed flat files that require an
encryption algorithm (GUI) to be read. The data is sensitive and
available
publically via the application which, when requrested by the user, is
parsed
using the algorithm. When the files are updated, they are rehashed using
an
internal application and redistributed. (A winform application - not a
webform application)

FILESTREAM (2008) works only at the server or level, or does it?

Can .NET deploy a small .mdf (CLR) that contains the files as a blob or
blogs thus avoiding the rehash requirement whenever changes are made and
distributed?

Instead, the database would be updated directly using SQL (where the
source
files are located to populate the flat files).

I envision the ability to distribute the flat files as blobs on the local
machine, distributed by the application, while at the same time leaving
the
encryption to the SQL Server database. FILESTREAM can read the data
securely
from the blob.

Is this more of a fantasy or is it a possible scenario (easier mind you
than
rehashing the files for each change/distribution)?

Some history:
Some time ago, in MSAccess 2.0, these files were created in Access by
adding
them to the database using an encryption algorithm. The files were read
from
the Access database and decrypted on the fly. When the advantage of speed
seemed lost (Access 2.0 was not upgraded), they were converted to binary
flat
files [space requirement]. I am banking that the space and speed are no
longer issues.
--
Regards,
Jamie

Hi Jamie

Filestream data can be backed up with the database, but if you are using TDE
you will need to restore the certificate or key that encrypted the database
first see http://msdn.microsoft.com/en-us/library/ms186858.aspx. TDE is only
supported by Enterprise Edition. If you are using other encryption features
then you will need to password for the database master key see
http://blogs.msdn.com/lcris/archive/2007/11/16/sql-server-2005-restoring-the-backup-of-a-database-that-uses-encryption.aspx.
This would be the same if you are using varbinary or Filestream. If you
stored the encrypted files, then the application will need to decrypt them
and it will just be binary in SQL Server and not use the encryption feature.

There are pro and cons for using either data types and file size is an
important factor see
http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-FILESTREAM-performance.aspx
http://msdn.microsoft.com/en-us/library/cc716724.aspx
http://msdn.microsoft.com/en-us/library/cc949109.aspx and
http://msdn.microsoft.com/en-us/library/dd206979.aspx

Encrypting the data will mean that your backups will not compress very much.

HTH

John

.

Relevant Pages

  • Re: Proposal for Lite Encryption for Login Form without SSL
    ... This way the password is not sent to the server ... simply copy the server-side salt used to hash the pw in the ... password in the database, it does make public the server-side salt ... This would be the equivalent to a public key in public key encryption ...
    (comp.lang.php)
  • Re: Writing data to disk?!
    ... your customer what is needed. ... Maybe flat files would be better for this. ... standard format (database, xml, etc). ... - Database servers ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: How to protect database password in a interpreted program (specially Tcl)
    ... consider is to have a server somewhere safe (even on the database ... your clients put on it a little. ... and employ the encryption and authentification mechanism ... and return a chunk of random data that was sent by the server. ...
    (comp.lang.tcl)
  • RE: Encrypt data - SQL Server 2000
    ... > is to do the encryption at the application level. ... > keeps the performance hit for en/decryption at the client (or web server ... > own EFS to encrypt the whole volume where the Database is. ... Business users have specific security ...
    (Focus-Microsoft)
  • Re: Bulk inserts into SQL Server 2000 from Flat Files
    ... I'm writing a Window's Service in VB.NET to take information from flat ... and then subsequently into the database. ... Either upgrade to ADO.NET 2.0 / SQL Server 2005 and use SQLBulkCopy ...
    (microsoft.public.dotnet.framework.adonet)