Thor, et al
Have you looked at DFS under Windows 2003 R2? It's much improved over the
early versions and might solve your problem. I believe the inter-site
traffic is also encrypted and even if not, you could IPSec tunnel it. I have
looked into IPSec tunnelling Active Directory replication to provide secure
AD replication over public networks. It looks excellent.
If not...
Ok, so you have a good enough reason for a new wheel, and doubtless a little
frustrated at the lack of lateral thought from your contemporaries (if it
looks hard then don't do it!). I can't help you with the specific technicals
on this having not been a DB developer since SQL7 days.
How about using SQL to keep your references to the filesystem as Matthew
Patton suggested earlier? Then using DFS - or your current technology to
keep the replication points up to date. Once the files are encrypted using a
file by file method like PGP or filesystem method like EFS, you have covered
the security angle.
Kevin E. Casey is spot on with the filesystem limitations compared to SQL.
However, I have managed, migrated and secured a few SANS with multiple
millions of files in directory structures numbering in the hundreds of
thousands of folder objects. NTFS can handle seriously big filestructures,
but as Kevin points out, you have to be very organised and it is way easier
to backup a big database than a big filesystem.
What I like about DFS here is the fact that it uses BITS II transfer
methodology. It only copies the changed portions of the files at byte level,
rather than the whole file. This makes for a huge bandwidth saving to your
remote locations.
Finally, before I bow out in technical defeat :) , You might also consider
Active Directory/ADAM. I'm not sure if it will be any better than SQL for
solving your problem, but it's internal structure is much closer to an
NTFS-type filesystem than SQL and is probably far easier to secure at a
"row" (read "leaf") level.
This is a goodun, and I would dearly love the time to research it in more
detail. Good luck with it, and keep us posted ;)
Cheers
James
James D. Stallard, MIoD
Microsoft and Networks Infrastructure Technical Architect.
Leafgrove Limited
Web: www.leafgrove.com
Email: j a m e s @ l e a f g r o v e . c o m (remove the spaces)
Mobile: +44 (0) 7979 49 8880
Skype: JamesDStallard
-----Original Message-----
From: Thor (Hammer of God) [mailto:thor@hammerofgod.com]
Sent: 20 September 2006 22:45
To: James D. Stallard; Focus-MS
Subject: Re: Storing Images in SQL Server (2005)
Inline:
On 9/20/06 1:23 PM, "James D. Stallard" <james@leafgrove.com> spoketh to
all:
Thor
I have to ask the obvious question:
. Why are you re-inventing the (perfectly god) wheel?
The filesystem is easy to manipulate from within SQL Stored
Procedures, or directly from the app, with VB or WSH - or you're
preferred development environment. We already know how to secure a
filesystem and it's credentials database is already available to you
as part of the OS. Lastly, the filesystem is much faster than SQL at
returning file objects, due to the way in which they are stored.
Well, a couple of reasons. Like I said in my post to Matthew,
replication/portability to secure destinations is the most pressing reason.
If this were a static app that only lived in the DMZ, it would be a no
brainer-I'd leave things as they are now. But it's not-- I need to securely
move files around to various locations from the internal to the DMZ and to
my off-site location. Doing this from a file-system standpoint brings in
issues of transport (CIFS,FTP, HTTP, etc), authorization infrastructures,
trusts, and also file replication integrity. To do it securely is tricky...
I'm doing it now at the file-system level, but it just seems like there is a
better way to do it.
It's quite likely that the only thing SQL can do faster/better in your
scenario is return the "Select *" query faster than the filesystem
could return a "DIR" command. Everything else will be ponderous by
comparison.
It strikes me that this may have something to do with why WinFS hasn't
made it into Longhorn as databases simply aren't designed for this
type of data.>
Valid points all... But to me, binary data is binary data - I don't know
that I would say "dbs are not designed for this type of data," particularly
in the case of SQL 2005 - what's the real difference between a web server
delivering binary data to a browser from a file or a stream object? I've
done a good bit of research into various developer newsgroups about this,
and many people share your feelings of "why re-invent the wheel." But I
have to say, in most of the cases where people say "I want to do this- how
do I?" no one really answers the question- rather, they say "don't do it
like that."
I can totally see how performance could be an issue, but when my clients log
on to my secure site to view project status, they'll only be looking at a
few documents per project site. But I'm still not totally convinced that
performance issues will be evident to the user at all. There is something
to be said for dynamically creating needed content streamed directly to the
user's browser in these cases.
Re-inventing the wheel here I think really gives us the opportunity to take
advantage of some interesting security mechanisms, both in the areas of
replication/portability and access controls (think views).
Thanks for the thoughts... Still chewing on it all ;)
t
Hope this helps crystallise your thoughts.
Cheers
James
James D. Stallard, MIoD
Microsoft and Networks Infrastructure Technical Architect Leafgrove
Limited
Web: www.leafgrove.com
Email: j a m e s @ l e a f g r o v e . c o m (remove the spaces)
Mobile: +44 (0) 7979 49 8880
Skype: JamesDStallard
-----Original Message-----
From: listbounce@securityfocus.com
[mailto:listbounce@securityfocus.com] On Behalf Of Thor (Hammer of
God)
Sent: 19 September 2006 19:35
To: Focus-MS
Subject: Storing Images in SQL Server (2005)
Greetings security professionals:
I'm starting a new development project where I'm considering moving
image and document data into my database rather than storing the files
in the server filesystem.
I've been mulling over the security implications of this, and want to
see what others are doing in this area. The first thing that comes to
mind is row-level security, and how others are handling the
"flow-through" from table permissions to file system permissions where
you're creating the resultant files. In my environment, I have
directory structures for individual clients, with NTFS permissions
applied to the different client directories so Client A can only see their
own data, and not Client B's.
I'm concerned that a possible breach could allow Client A to see
Client B's data unless I impose row-level security on the DB or create
multiple views for each client. I'm open to thoughts on how to best
manage that. Also, are you guys "streaming" the content from DB
directly into the browser, or are you creating a temporary file first,
storing that in the file system, and then referencing that temp file?
If so, how are you handling permissions on that? Via inherited
directory permissions? And what about the context of the web user? You
give them delete permissions to "clean up"
the temporary files? The "steaming" context seems a better way to do
it...
Just seeing what issues those who have gone through the deployment
process have run into.
Thx
T
----------------------------------------------------------------------
-----
----------------------------------------------------------------------
-----
----------------------------------------------------------------------
-----
----------------------------------------------------------------------
-----
---------------------------------------------------------------------------
---------------------------------------------------------------------------
