Solved

Copying encrypted data to another server

Posted on 2010-11-29
7
315 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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 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