• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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(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.
0
rkulp
Asked:
rkulp
  • 4
  • 2
  • 2
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
Leave it encrypted at the db level. Decrypt it in the application.
0
 
rkulpAuthor Commented:
aarontomosky,
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.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
You wouldn't be able to use bound controls.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
rkulpAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
rkulpAuthor Commented:
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.
0
 
rkulpAuthor Commented:
Thanks for hanging in there with my ignorance.
0
 
Anthony PerkinsCommented:
>>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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now