Solved

Copying encrypted data to another server

Posted on 2010-11-29
7
312 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

825 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