Restoring Databases with Encrypted Data to Another Server

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 tab
le
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(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM Person.#Temp
--create physical table
select * into TEST from person.#temp
--view results from physical table
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) 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(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM TEST
From what I have researched, this is all you have to do. I must be missing
a 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,
DBI think you forgot to open the key - you were doing this step on the
original server:
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"DB" <DB@.discussions.microsoft.com> wrote in message
news:8511E6F6-0912-4274-9CA0-605295981376@.microsoft.com...
>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(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM Person.#Temp
> --create physical table
> select * into TEST from person.#temp
> --view results from physical table
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) 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(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM TEST
> From what I have researched, this is all you have to do. I must be
> missing
> a 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