Moving table with Encrypted column to different server

Question

Moving table with Encrypted column to different server

DougGifford

SSCommitted

Points: 1815
More actions
November 3, 2010 at 4:05 pm

#147544

I have an SQL 2005 Develoment system with one table.
The table contains an encrypted column of data that was created using a master key, a certificate and a Symmetric key.
I have moved the table to a 2005 Sql Express database on a different server.
What do I need in order to read the encrypted data?
=========================
This SP works on the Dev server.
ALTER proc [dbo].[uspGetCryptedSSN]
@InString varchar(50),
@OutString varchar(50) OUTPUT
AS
SET NOCOUNT ON
Open Symmetric key TestTableKey
Decryption By Certificate EncryptTestCert
select @OutString = (Select dbo.ufngetpc(@InString))
Close Symmetric key TestTableKey
============================
Do I need to copy the certificate and/or master key and/or Symmetric Key from the original server to the new server?
If so which files need to be copied and where are they?
I will continue to search the archives and BOL on this.
Thanks in advance.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

DougGifford (11/3/2010)
I have an SQL 2005 Develoment system with one table.
The table contains an encrypted column of data that was created using a master key, a certificate and a Symmetric key.
I have moved the table to a 2005 Sql Express database on a different server.
What do I need in order to read the encrypted data?
Do I need to copy the certificate and/or master key and/or Symmetric Key from the original server to the new server?

Yes, you will need to restore all of these keys.

If so which files need to be copied and where are they?
I will continue to search the archives and BOL on this.
Thanks in advance.

Not trying to harp on you - but if anyone is thinking about using encryption at all, please thoroughly test what all will be involved to restore the database on a new server. Document this, so that you are ready to go when that aging server you put the database on finally does die. It's really not a matter of "if", but "when" you move to a different sql instance.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DougGifford SSCommitted Points: 1815 More actions · Answer 2

Thank you Wayne.

I am in the process of setting up and testing what you suggest.

My first step is to regenerate the keys so I can read the encrypted data.

Where might you suggest I find a "How to regenerate encryption keys on a new server?" explaination?

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

DougGifford SSCommitted Points: 1815 More actions · Answer 3

This link helped solve my problem!

http://www.eggheadcafe.com/software/aspnet/30238935/master-key-issue.aspx

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

DougGifford - Friday, November 5, 2010 11:39 AM
This link helped solve my problem!http://www.eggheadcafe.com/software/aspnet/30238935/master-key-issue.aspx

This is what I hate about links... that link is now dead and so is, what appears to be, the correct answer. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)