Restoring Databases with Encrypted Data to Another Server
- From: DB <DB@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 08:20:00 -0800
I use the following code to create encrypted data in a table called TEST in a
SQL Server 2005 database.
-- Use the AdventureWorks database
USE AdventureWorks;
-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID INT PRIMARY KEY,
FirstName NVARCHAR(200),
MiddleName NVARCHAR(200),
LastName NVARCHAR(200),
eFirstName VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName VARBINARY(200));
-- Create a Test Certificate
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'Adventureworks Test Certificate',
EXPIRY_DATE = '10/31/2009';
-- Create a Symmetric Key
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
-- EncryptByKey demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
EncryptByKey(Key_GUID('TestSymmetricKey'), FirstName),
EncryptByKey(Key_GUID('TestSymmetricKey'), MiddleName),
EncryptByKey(Key_GUID('TestSymmetricKey'), LastName)
FROM Person.Contact
WHERE ContactID <= 100;
-- DecryptByKey demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
MiddleName = DecryptByKey(eMiddleName),
LastName = DecryptByKey(eLastName);
-- View the results
SELECT convert(nvarchar(1000),DecryptByKey(eFirstName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMiddleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLastName)) AS LName
FROM Person.#Temp
--create physical table
select * into TEST from person.#temp
--view results from physical table
SELECT convert(nvarchar(1000),DecryptByKey(eFirstName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMiddleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLastName)) AS LName
FROM TEST
----------------------------------------------------
I backup the database and restore to another server. After the restore
finishes, I run the following commands in the restored database:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
When I run the following SELECT statement to view my data in the restored
database, I only get NULLs.
SELECT convert(nvarchar(1000),DecryptByKey(eFirstName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMiddleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLastName)) AS LName
FROM TEST
From what I have researched, this is all you have to do. I must be missinga step. Can anyone tell me what I have forgotten or provide me a list of
steps to execute when I am restoring a database with encrypted data to
another server????
Thanks,
--
DB
.
- Follow-Ups:
- Re: Restoring Databases with Encrypted Data to Another Server
- From: Laurentiu Cristofor [MSFT]
- Re: Restoring Databases with Encrypted Data to Another Server
- From: Uri Dimant
- Re: Restoring Databases with Encrypted Data to Another Server
- Prev by Date: auditing access to object
- Next by Date: Re: Job owned by a non-sysadmin fails to run
- Previous by thread: auditing access to object
- Next by thread: Re: Restoring Databases with Encrypted Data to Another Server
- Index(es):
Relevant Pages
|
|
