How do I protect private information using synchronization between SQL Server CE 3.5 and SQL Server Express 2008 R2?
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(EncryptedLast
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.
Convert( NVarchar(30), DecryptByKey(EncryptedLast
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You wouldn't be able to use bound controls.
ASKER
aarontomosky,
I agree that the computed values cannot be bound. Here is what I'm going to try:
1. Build the server database using the encrypted values without the plain text columns;
2. Create the local cache from the server db;
3. Create a dataset programmatically and add the two computed columns;
4. Try to bind the controls to this dataset.
5. Updates and Inserts involving the name would be encrypted and inserted into the local cache;
6. Use synchronization to update the server db.
I expect there are many pitfalls in this approach. Right now my biggest obstacle seems to be getting the database master key and symmetric key used in SQL Server in a form that is usable in .Net to give the same encryption/decryption results. I have searched this forum as well as other sites to find anything that links the two encryptions (SQL Server and .Net) together but have not found anything.
I would appreciate comments or suggestions on the approach and any information you may have about .Net encryption and SQL Server encryption and how they relate, if at all, to each other. If I have to, I'll build the db using only .Net encryption and give up being able to decrypt using the SQL Server functions.
I agree that the computed values cannot be bound. Here is what I'm going to try:
1. Build the server database using the encrypted values without the plain text columns;
2. Create the local cache from the server db;
3. Create a dataset programmatically and add the two computed columns;
4. Try to bind the controls to this dataset.
5. Updates and Inserts involving the name would be encrypted and inserted into the local cache;
6. Use synchronization to update the server db.
I expect there are many pitfalls in this approach. Right now my biggest obstacle seems to be getting the database master key and symmetric key used in SQL Server in a form that is usable in .Net to give the same encryption/decryption results. I have searched this forum as well as other sites to find anything that links the two encryptions (SQL Server and .Net) together but have not found anything.
I would appreciate comments or suggestions on the approach and any information you may have about .Net encryption and SQL Server encryption and how they relate, if at all, to each other. If I have to, I'll build the db using only .Net encryption and give up being able to decrypt using the SQL Server functions.
>>Right now my biggest obstacle seems to be getting the database master key and symmetric key used in SQL Server in a form that is usable in .Net to give the same encryption/decryption results. <<
It cannot be done. What you do not seem to understand is that if you use SQL Server's built in encryption than the same value encrypted in two different columns has a different encrypted value. This is also another reason why it is pointless to index columns encryted by SQL Server.
It cannot be done. What you do not seem to understand is that if you use SQL Server's built in encryption than the same value encrypted in two different columns has a different encrypted value. This is also another reason why it is pointless to index columns encryted by SQL Server.
ASKER
I appreciate the heads up. This is my first experience with SQL Server encryption. I'll just have to do it outside of SQL Server.
ASKER
Thanks for hanging in there with my ignorance.
>>I appreciate the heads up. This is my first experience with SQL Server encryption.<<
It is a not a problem. Don't take my word for it, test it out for yourself. It will take you all of 10 minutes and you will have a better understanding of how SQL Server encryption operates.
This:
"the same value encrypted in two different columns has a different encrypted value."
Should obviously have read:
than the same value encrypted in two different rows has a different encrypted value.
So for example, you have an encrypted column containing SS #s. The same SS# in two rows will have two different values.
It is a not a problem. Don't take my word for it, test it out for yourself. It will take you all of 10 minutes and you will have a better understanding of how SQL Server encryption operates.
This:
"the same value encrypted in two different columns has a different encrypted value."
Should obviously have read:
than the same value encrypted in two different rows has a different encrypted value.
So for example, you have an encrypted column containing SS #s. The same SS# in two rows will have two different values.
ASKER
Thanks for your quick response. That is what I was trying to do in the SQL statement. Is there a simple way to do this within the dataset? I am using bound text box and datagridview controls in Visual Studio 2010 and would prefer not to program them separately. This also requires that I use the same Database Master Key and Symmetric Key in the app that I used in the encryption in SQL Server. I'll have to do some research on how to do that.