Solved

Copying encrypted data to another server

Posted on 2010-11-29
7
308 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
Server 2008 Cluster Fail-over Errors 5 44
Help with SQL Query 23 39
SQL 2016 Setup - Connectivity Issues 4 16
Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now