Solved

Copying encrypted data to another server

Posted on 2010-11-29
7
321 Views
Last Modified: 2012-06-21
I have 2 sql server 2005 databases on separate servers. One database is production and the other is a datawarehouse. The production server has encypted data columns within the tables. When that data is copied over to the data warehouse, I believe the encryption master key is no longer valid on the datawarehouse server.

How do I ensure that the encrypted data can be decrypted in the warehouse?

Do I unencrypt and re-encrypt? Or is there a better way?
0
Comment
Question by:CSUGDEN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 5

Expert Comment

by:adamsjs
ID: 34236037
Is the encrypted data in your production database encrypted using SQL Server 2005's built-in encryption feature, or is it encrypted before being passed to the SQL Server for storage?

I've not had experience with SQL Server 2005's encryption features myself.  Based on what I know of it, if this is what you are using, and you are copying the raw data from production to your data warehouse, then I believe you will have issues as the keys and certificates do differ between SQL Server instance.  I believe decrypting and re-encrypting will need to be a part of your ETL from production to your data warehouse.

If you the encryption is done outside of SQL Server, you will need to do the same, leveraging the external encrypt/decrypt mechanism.
0
 

Author Comment

by:CSUGDEN
ID: 34238862
Yes the data is being encrypted in the source database long before the transfer to the warehouse. I was thinking that I could either:

1 Backup the master key and restore it on the warehouse server
2 Write a custom .net encrytor routine/sql-function to do the encryption which would be independent of the server master key approach.

Thoughts?
0
 
LVL 5

Accepted Solution

by:
adamsjs earned 250 total points
ID: 34239096
Again, I haven't had an opportunity to work with SQL Server 2005 encryption.  But, I think option 1 could be workable.  From a cursory glance at Books Online this morning, it appears you could create a key on the new server using a file with a key pair (at least for asymmetric keys), and you could then use the builtin in decrypt functions to specify the key for decryption of the data.

As for option 2, that would probably work as well.  Again, not my area of experience.  But, if you were going to do this, would it maybe be just as easy to build your SSIS tasks, or procedures called by your ETL process, to decrypt and then re-encrypt?  I think you'd have to call your function for each of your data moves, which seems about the same as calling the decrypt/encrypt functions for SQL Server.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 5

Expert Comment

by:adamsjs
ID: 34275930
Did the information provided answer your questions?  Out of curiousity, which method did you decide on for a solution?
0
 

Author Comment

by:CSUGDEN
ID: 34283884
I wrote an encrypting routine in .net, generalized the inputs so that I could apply it to all of the tables requiring the encryption. I have yet to optimize the approach. Right now I incrypt one record at a time and write it back to the database one record at a time. Then I piggyback the executable onto the SSIS packages that get run on the data everyday. It seems to work well.
0
 
LVL 27

Expert Comment

by:Tolomir
ID: 34690243
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Encryption for Business Encryption (https://en.wikipedia.org/wiki/Encryption) ensures the safety of our data when sending emails. In most cases, to read an encrypted email you must enter a secret key that will enable you to decrypt the email. T…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 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