[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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
8
Medium Priority
?
452 Views
Last Modified: 2012-08-13
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
Comment
Question by:rkulp
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 2000 total points
ID: 35453375
Leave it encrypted at the db level. Decrypt it in the application.
0
 
LVL 1

Author Comment

by:rkulp
ID: 35453415
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
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35453528
You wouldn't be able to use bound controls.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:rkulp
ID: 35457107
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35457203
>>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
 
LVL 1

Author Comment

by:rkulp
ID: 35457264
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
 
LVL 1

Author Closing Comment

by:rkulp
ID: 35457267
Thanks for hanging in there with my ignorance.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35457304
>>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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Will you be ready when the clock on GDPR compliance runs out? Is GDPR even something you need to worry about? Find out more about the upcoming regulation changes and download our comprehensive GDPR checklist today !
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question