T
Tom
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
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