How do I protect private information using synchronization between SQL Server CE 3.5 and SQL Server Express 2008 R2?
Posted on 2011-04-23
I have set up a SQL Server Express 2008 R2 database which includes LastName, FirstName, MI fields as well as other information. I need to be able to protect the name when synchronizing between occaisionally connected PCs and the SQL Server Express 2008 R2 server. My attempt is to have only encrypted last name and first name fields which I want to decrypt using
Convert( NVarchar(30), DecryptByKey(EncryptedLastName)) AS LastName
and a similar construction for the first name. This works fine on the server but it turns out that Convert is not supported in SQL Server CE. Hence the grand scheme fails. Unfortunately, it is my only scheme at this time.
I'm sure others have solved a similar requirement between SQL Server CE and SQL Server. What is the best way to achieve this result: (1) display real last name and first name but transmit encrypted (or otherwise protected) values between the PCs and the server, and (2) be able to decrypt the saved data as needed using T-SQL on the server?
I am open to solutions that do not involve encryption so long as they meet current HIPAA standards.