Re: Encryption; SQL Server 2005 & Windows 2003 Server

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@xxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: SQL or Access DB
    ... As far as encryption goes though... ... with Sql Server you can use SQL DMO and encrypt your stored procedures ... installation - Security was absolutely critical and in most instances, ... > then we create a nice gui around this database and sell it to automotive ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cryptography in SQL Server 2000
    ... A company is vulnerable when its security ... > database encryption solution with protected key-management software ... > tested by the SQL Server Test Lab. ...
    (microsoft.public.sqlserver.security)
  • Re: Sybase to Access
    ... The Grand Master ... Have you used database encryption for SQL Server before? ... We're a fully supported platform for VB6 ADO code against SQL Server. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: database password and encryption
    ... of encrypting the entire database? ... SQL Server 2005 does have some built in data encryption support ... maybe hundreds of documents relating to protecting ...
    (microsoft.public.platformsdk.security)
  • Re: sql2005 "Invalid Owner.."
    ... In my previous response I expressed concern that the user properties did not ... login information. ... You encountered the error regarding database diagram when you attempted to ... the Compatibility level is SQL Server 2005; ...
    (microsoft.public.sqlserver.setup)