Encrypt entire database in SQL Server 2005 ?

Hello,
Is there any way to simply encrypt an entire database on SQL Server 2005?
Is this possible? - And if so, how can it be done..? Aside from encrypting
each individual column, of course.
Any info, suggestions, pointers to documentation, etc. would be very much
appreciated!
Thanks! :)
- Andrew
Vancouver, BC, CanadaProvide more detail about what you mean by "encrypt" and "entire database".
"Andrew" <Rheal@.community.nospam> wrote in message
news:1qqpsk0ooclgu.2lvty2qv9le4.dlg@.40tude.net...
> Hello,
> Is there any way to simply encrypt an entire database on SQL Server 2005?
> Is this possible? - And if so, how can it be done..? Aside from
> encrypting
> each individual column, of course.
> Any info, suggestions, pointers to documentation, etc. would be very much
> appreciated!
> Thanks! :)
> - Andrew
> Vancouver, BC, Canada|||On Tue, 1 Nov 2005 15:41:57 -0500, JT wrote:
> Provide more detail about what you mean by "encrypt" and "entire database"
.
> "Andrew" <Rheal@.community.nospam> wrote in message
> news:1qqpsk0ooclgu.2lvty2qv9le4.dlg@.40tude.net...
Thank you for your reply. :) I'll try to explain what I mean by encrypt
and entire database..
With "Encrypt" I mean basically to convert the data/information in the
Database from human readable form to a form that is Not human readable. And
with a sufficiently strong algorithm that it would take even a highly
trained professional with access to any of the top 10 computers on the top
500 list of Supercomputers at least ws, if not much, much longer to
decode.
By "Entire Database" I mean the data/information in every field of every
record of every table IN the database. Which, as I understand it, is the
same as saying every column of every table in the database.
I hope that is sufficiently clear, and that I'm not making any silly/false
assumptions or anything like that.. Please let me know.
Again.. any help, hints, suggestions, and/or pointers to docs, etc. would
be very much appreciated.
Thanks for your time!
- Andrew
Vancouver, BC, Canada|||> With "Encrypt" I mean basically to convert the data/information in the
> Database from human readable form to a form that is Not human readable.
> And
> with a sufficiently strong algorithm that it would take even a highly
> trained professional with access to any of the top 10 computers on the top
> 500 list of Supercomputers at least ws, if not much, much longer to
> decode.
You might find it safer to print out the data and bury it in a jar in your
backyard. And remember to do it when you're drunk so you have a better
chance of forgetting exactly where you buried it, you know, should a gun be
held to your head or something.
Seriously.
Unless the data is to be explicitly unreadable by ANY human being, including
yourself, then your protection is only going to be as strong as the security
around the tool(s) that allow you to read the data. If an application has
to read, change or display any of the data, then the encryption becomes more
of an encoding because it can only be as strong as the ability for the app
to break it and convert it back to human readable data. And in addition to
protecting access to the app, and the source code that contains the
"decryption" algorithms, you also need to protect the network - both
internally and externally - from packet monitoring as you send data back and
forth from the database...
A|||Andrew (Rheal@.community.nospam) writes:
> Is there any way to simply encrypt an entire database on SQL Server
> 2005? Is this possible? - And if so, how can it be done..? Aside from
> encrypting each individual column, of course.
> Any info, suggestions, pointers to documentation, etc. would be very much
> appreciated!
The way to do it in SQL 2005, would indeed to be to encrypt every column.
You would never do this, though, as the database would basically useless.
Let's look at a simple example. Say you want to encrypt national
registration numbers of people, because that's considered sensitive
information. But you are using this number to look up people in the
database. But if it's encrypted, you can't index it, so it will be
slow, and with any size of the database, it will not be usable.
So in practice, you only want to encrypt part of the data.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 1 Nov 2005 23:40:24 +0000 (UTC), Erland Sommarskog wrote:

> The way to do it in SQL 2005, would indeed to be to encrypt every column.
> You would never do this, though, as the database would basically useless.
> Let's look at a simple example. Say you want to encrypt national
> registration numbers of people, because that's considered sensitive
> information. But you are using this number to look up people in the
> database. But if it's encrypted, you can't index it, so it will be
> slow, and with any size of the database, it will not be usable.
So what you are saying is that this is a shortcoming of SQL Server 2005's
built in Encryption capabilities... right..? I still don't know about how a
lot of this stuff works, and the details.. but - I do know that Sybase
Adaptive Server Anywhere v9 lets you encrypt the entire database.
You can choose between "simple" encryption (which I imagine is more to stop
the casual Admin browser and that sort of thing than anything) and "strong"
encryption, which uses AES. Sure, there's a performance hit.. but there are
always tradeoffs, right?

> So in practice, you only want to encrypt part of the data.
Actually, I really do want to encrypt the whole database, because that's
what the people above me want... but it's not just on a whim:
"...according to the security principle defense in depth, you have to
assume that attackers will penetrate all walls of defense you erect around
a valuable resource. Every layer of defense makes it less likely that an
attacker will win the prize, and data encryption is a final level of
defense against database attackers."
From:
http://www.microsoft.com/technet/te...lt.as
px
(and there are a Lot more similar references..)
However, if SQL Server 2005 is so limiting that it is impractical to do so
- I may end up not doing it, and doing only certain columns. More likely
we'd end up using Sybase, but it could go either way.
Thanks for your thoughts.
- Andrew|||On Tue, 1 Nov 2005 16:41:43 -0500, Aaron Bertrand [SQL Server MVP] wrote:

> Unless the data is to be explicitly unreadable by ANY human being, includi
ng
> yourself, then your protection is only going to be as strong as the securi
ty
> around the tool(s) that allow you to read the data.
So, for example, if everybody had access to the source code and algorithms
of PGP, then anybody could easily crack any data encrypted with PGP?
Whups..

> If an application has
> to read, change or display any of the data, then the encryption becomes mo
re
> of an encoding because it can only be as strong as the ability for the app
> to break it and convert it back to human readable data.
Well.. I may not be an "Expert" on Encryption but I'm pretty sure that the
application doesn't have to *break* the encryption on the data every time
it wants to access it. That's what keys are for.

> And in addition to
> protecting access to the app, and the source code that contains the
> "decryption" algorithms, you also need to protect the network - both
> internally and externally - from packet monitoring as you send data back a
nd
> forth from the database...
Even ignoring your mistaken assumptions about the need to protect the app
and/or the source code (You only need to protect the key(s)) - I *do* know
that SQL Server 2005 offers practically trivial network traffic encryption
with SSL. You simply need to install a certificate and turn the
ForceEncryption Database Engine option on. See:
http://msdn2.microsoft.com/en-us/library/ms189067
I appreciate that you may, in your own way, be trying to help.. but you
really should know at least the fundamentals about a subject before you go
trying to give advice like you're some kind or authority on the subject.
Sorry. Thanks for trying.
- Andrew|||> So what you are saying is that this is a shortcoming of SQL Server 2005's
> built in Encryption capabilities... right..?
Sure, I guess so.

> lot of this stuff works, and the details.. but - I do know that Sybase
> Adaptive Server Anywhere v9 lets you encrypt the entire database.
So use Sybase then. Yeesh.|||On Wed, 2 Nov 2005 00:07:06 -0500, Aaron Bertrand [SQL Server MVP] wrote:

> So use Sybase then. Yeesh.
I would prefer not to... Why else why would I be asking the question in the
first place? SQL Server 2005 has all the "pieces" in place to do a much
better job of it than Sybase. So much so that after hours of digging and
googling and reading up on it, I still find it hard to think that SQL
Server 2005 is not capable of doing it. Besides.. though there may be those
around here that prefer Sybase SQL Anywhere.. I myself vastly prefer MS SQL
Server.
I suspect that maybe MS has deliberatly left out that ability so as to not
tread on too many 3rd party vendors/partners toes or some such thing.
Hopefully someone from MS will answer my post... soon... with the
definitive answer. ;)
Cheers
- Andrew|||Hello,
SQL server 2005 support encrypt a Column of Data, you can refer to the
information in the following articles:
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/SQL/2005/...ll=/msdnmag/iss
ues/05/06/sqlserversecurity/toc.asp
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.