Encryption; SQL Server 2005 & Windows 2003 Server

Issue(s):
1. We *must* encrypt all data items in a Database.
2. SQL Server 2005 Encryption places a tremendous burden on the
system as a whole when many columns are encrypted, especially
columns involved in indexes. Response time(s) are unbearable.
Proposed Solution:
A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
Encrypted files thereby using the CrytoAPI at the file-level rather
than through SQL Server wrappers etc.
B. As the Key Owner/Windows User of the Encrypted files of "A."
above, attach to the files in SQL Server 2005/2000.
C. The availability of individual Column Privileges is handled by
conventional SQL Server privileges.
Observations (based on actual Testing):
I. The Database application runs fine and response times are
very good.
II. Database changes can be made as though no encryption were
done. This includes the creation of ad hoc tables etc.
III. Current an furture Applications need not be modified to OPEN
SYMMETRIC KEY(s) or access hashed values to speed Queries.
QUESTIONS:
* Has Microsoft tested this approach to encrypting the entire
Database ?
* Is there any collateral experience or other Developers who have
such a configuration in Production ?
* Are there any foreseeable issues with this approach?
Thank you (in advance)I am using SQL2000 and SQL2005 and have all database files on an encrypted
partion. Encryption is done with TrueCrypt and i never had any problem
with this solution.
But that is only for securing the data if somebody will steal the computer.
It does not help if somebody hacks the database server. For this you need
the built in encryption from SQL-Server, where you need the password in the
query, even if you logged in as admin and have all rights.
With built in encrytion it is possible, that two persons with different
passphrases see different parts of the data, even if they use the same
query on the same column.
So encryption of the filesystem and encryption of data in the database are
different things.
bye, Helmut|||Thank you Helmut.
Our primary Data Security concerns are, indeed:
* intentional theft of the System(s) or, specifically, the Hard
Drives.
* unintentional loss/natural breach of physical security
e.g. earthquake, hurricane, terrorism ...
We are unconcerned about :
* the Database Administrator (in the serveradmin role) being
able to view unencrypted data.
* having many levels of granularity of Security within a given
Database where different Users/Roles see differing sets of
Confidential Data depending on their particular need-to-know.
We prefer to use current Vendors for the solution since the Client
has a rather complex and time-consuming procurement process.
Regards ...
"Helmut Woess" wrote:

> I am using SQL2000 and SQL2005 and have all database files on an encrypted
> partion. Encryption is done with TrueCrypt and i never had any problem
> with this solution.
> But that is only for securing the data if somebody will steal the computer
.
> It does not help if somebody hacks the database server. For this you need
> the built in encryption from SQL-Server, where you need the password in th
e
> query, even if you logged in as admin and have all rights.
> With built in encrytion it is possible, that two persons with different
> passphrases see different parts of the data, even if they use the same
> query on the same column.
> So encryption of the filesystem and encryption of data in the database are
> different things.
> bye, Helmut
>|||"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Issue(s):
> 1. We *must* encrypt all data items in a Database.
> 2. SQL Server 2005 Encryption places a tremendous burden on the
> system as a whole when many columns are encrypted, especially
> columns involved in indexes. Response time(s) are unbearable.
> Proposed Solution:
> A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
> Encrypted files thereby using the CrytoAPI at the file-level rather
> than through SQL Server wrappers etc.
> B. As the Key Owner/Windows User of the Encrypted files of "A."
> above, attach to the files in SQL Server 2005/2000.
> C. The availability of individual Column Privileges is handled by
> conventional SQL Server privileges.
> Observations (based on actual Testing):
> I. The Database application runs fine and response times are
> very good.
> II. Database changes can be made as though no encryption were
> done. This includes the creation of ad hoc tables etc.
> III. Current an furture Applications need not be modified to OPEN
> SYMMETRIC KEY(s) or access hashed values to speed Queries.
> QUESTIONS:
> * Has Microsoft tested this approach to encrypting the entire
> Database ?
Yes. Putting SQL Databases on an Encrypted File System is supported.

> * Is there any collateral experience or other Developers who have
> such a configuration in Production ?
> * Are there any foreseeable issues with this approach?
>
Expect physical IO performance to be really bad. Plan around that by
providing plenty of RAM and load-testing your application.
David|||Thank you David,
I find your response incredibly interesting as I assume your corporation
has some first-hand experience as well as feedback from Microsoft Corp.
with the approach I am describing. My bosses are a big City Department
and have many Applications' Databases they would like to protect
against accidental/intentional exposure e.g. System/HDD theft or
loss or physical exposure due to natural or other unsavory causes.
At the risk of over-stepping my bounds, any further information you are
at liberty to share as:
1. Types of applications (high xactoin telecomm, DSS, low xactoin
record-keeping) 2. DB Size (our DB's will be 3Gb to 600Gb depending on the
App.)
3. Production environment (SAN, Cluster, Stand-alone)
In any event, many thanks for taking the time to respond.
========================================
===========
> "ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
> news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Yes. Putting SQL Databases on an Encrypted File System is supported.
>
> Expect physical IO performance to be really bad. Plan around that by
> providing plenty of RAM and load-testing your application.
> David