Solved

Copying encrypted data to another server

Posted on 2010-11-29
7
317 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
2017 Webroot Threat Report

MSPs: Get the facts you need to protect your clients.
The 2017 Webroot Threat Report provides a uniquely insightful global view into the analysis and discoveries made by the Webroot® Threat Intelligence Platform to provide insights on key trends and risks as seen by our users.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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