Encryption; SQL Server 2005 & Windows 2003 Server

Any further input would be appreciated ...
Pro EFS:
Indexs, Primary Keys, Foreign Keys, DEFAULTS, CHECK CONSTRAINTS are preserve
d.
Databases modifications need not consider Encryption.
Patterns & Practices
http://msdn.microsoft.com/library/d...
h05.asp
http://msdn.microsoft.com/library/d...r />
MCh18.asp
http://msdn.microsoft.com/library/d.../>
SecDBSe.asp
Other Technical Articles
http://www.microsoft.com/technet/pr...0.mspx?mfr=true
http://www.microsoft.com/technet/pr...fr=
true
http://www.microsoft.com/technet/ar...n/sp3sec02.mspx
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
http://www.microsoft.com/technet/pr...y/sqlorcle.mspx
http://www.microsoft.com/technet/se...phyetc/efs.mspx
http://www.sqlservercentral.com/col...menting_efs.asp
http://www.microsoft.com/technet/pr...5/multisec.mspx
http://www.akadia.com/services/sqls...l#_Toc513865376
http://www.sans.org/top20/2002/mssql_checklist.pdf
Case Study
http://www.microsoft.com/canada/cas...worksafebc.mspx
Anti-EFS:
1. If the file is not created in an Encrypted Directory the temporary fil
e
created by EFS during encryption remains in clear-text and is
vulnerable.
a) cipher.exe /W must be used to Wipe the temporary file.
2. EFS will not function in a Clustered Environment.
3. If the Server crashes when an Encrypted File is open the pagefile.sys
will
contain vulnerable clear-text of the Encrypted File on restart.
4. The Windows Administrator(s) can "Set Password ..." of the Key Owner
and the Key Owner will not be able to access the data.
5. If the Key Owner does not specify a Data Recovery Agent (DRA) AND does
not backup the PKI the data might become inaccessible under
circumstances
such as "4." above.
6. Encrypted Files cannot be backed up to non-NTFS devices except with
Windows
Backup utilities.
7. Extra steps must be taken over-and-above conventional SQL Server Backu
p,
Recovery and Disaster Recovery procedures.
8. The Windows Administrator can access the (otherwise) encrypted data if
SQL Server "BUILTIN\Administrators" is not removed.
9. The Database *.mdf & *.ldf files cannot be moved between domains and
retain
the Encrypted Attribute.
10. Stealing a local account password is easy using common hacker tools in
standalone mode.
11. Encrypted files stored on file servers are decrypted on the server
and then
transported in clear text across the network to the user's workstation.
Because EFS needs access to the user's private key, which is held in
the
profile, the server must be "trusted for delegation" and have access to
the user's local profile.
a) Requires IPSec to secure the file transfer between file server
and
user machine.
12. "The EnCase EFS Module provides Encrypting File System (EFS) folder a
nd
file decryption capabilities, for locally authenticated users."
(http://www.digitalintelligence.com/...oftware/encase/)"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:16785415-7A45-4031-A599-86896233DC15@.microsoft.com...
> Any further input would be appreciated ...
> Pro EFS:
>
. . .
> 8. The Windows Administrator can access the (otherwise) encrypted data
> if
> SQL Server "BUILTIN\Administrators" is not removed.
Although this is true, removing the BUILTIN\Administrators account is no
protection against a Windows admin. A windows admin on the box can shut
down SQL Server, replace the Master database, restart and attach your
encrypted database. Or just restart the instance in single-user mode.
David

Encrypting confidential data including foreign keys

Hi,
I am currently working on a project for a client. The project is to create
an intranet site to maintain confidential employee data. There is a master
employee table, containing name, date of birth etc. However, the table also
links to some look-up tables, such as grades, ethnicity, citizenship etc.
I was wondering what was the best solution for encrypting the data in SQL
Server 2000, so that someone with database access cannot read the
confidential data. Encrypting the free text data is fairly simple, but I am
not sure how to encrypt the foreign keys while preserving referential
integrity.
For example, if an employee has citizenship = 5, it is quite easy to workout
the citizenship from the look-up table. Even if 5 is encrypted to ABC, it is
quite easy to see the pattern on the table.
Also, the client wants to do some reporting on the tables, so run queries
with filters (such as Citizenship=UK) on encrypted data.
We looked at xpcrypt, but it appears to create automatically a view
containing the decrypted data for the duration of the SQL session, so I am
not sure how appropriate it is for a web application, maintaining pretty
much a constant connection/session to the DB.
Any advice welcome!
Thanks,
TomOn Fri, 28 Oct 2005 21:29:31 +0100, "Tom" <Tom@.nospam.com> wrote:

> but I am
>not sure how to encrypt the foreign keys while preserving referential
>integrity.
>
Hmm, I doubt you'll find any encryption product that can encrypt a
foreign key column.
Scott
http://www.OdeToCode.com/blogs/scott/|||Hi,
well try our software for SQL server side encryption for that , visit and
refer :
http://database-encryption.com/ it will serve your purpose
Regards
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table als
o
> links to some look-up tables, such as grades, ethnicity, citizenship etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I a
m
> not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to worko
ut
> the citizenship from the look-up table. Even if 5 is encrypted to ABC, it
is
> quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>
>|||http://www.sqlservercentral.com/col...oolkitpart1.asp
You can't really encrypt foreign key columns to any useful degree, since
they'll have to be encrypted on the referenced table as well and once again
you've got an easily visible link... What you can do is encrypt the
descripition data in the referenced table to make it unreadable. So instead
of storing "NATIVE AMERICAN" in plain text on the ethnicity table, you could
encrypt that description and store it.
"Tom" <Tom@.nospam.com> wrote in message
news:raydnZJ8BOyXF__eRVnyjw@.pipex.net...
> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table
> also links to some look-up tables, such as grades, ethnicity, citizenship
> etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I
> am not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to
> workout the citizenship from the look-up table. Even if 5 is encrypted to
> ABC, it is quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>|||you can use built in database security to accomplish your goal|||What SQL 2000 function is that?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1147967032.280063.193550@.u72g2000cwu.googlegroups.com...
> you can use built in database security to accomplish your goal
>