Geeks With Blogs
Chris Breisch   .NET Data Practices
Search this Blog!

We've recently run into trouble moving encrypted data between databases on SQL Server 2005.  I've solved the riddle and am posting the answer here.  For us, the key part is that we weren't specifying all the options when creating the SYMMETRIC KEYs, so the keys weren't identical between databases.

Microsoft recommends that you back up all your keys and store them in a secure location.  Good idea, but doesn't do you any good if you then restore them and still can't decrypt your data.  That's the situation we found ourselves in.

The sample code below requires that the folder "C:\encrypt_test" exist on the database server.

-- First, we'll create some test databases
USE master

-- drop any databases left around from previous run
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test1')
    DROP DATABASE encrypt_test1

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test2')
    DROP DATABASE encrypt_test2

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test3')
    DROP DATABASE encrypt_test3

-- create two test databases
CREATE DATABASE encrypt_test1
CREATE DATABASE encrypt_test2

-- Next, create a simple table in each
USE encrypt_test1
CREATE TABLE Color (Name NVARCHAR(255), EncryptedName VARBINARY(255))
USE encrypt_test2
CREATE TABLE Color (Name NVARCHAR(255), EncryptedName VARBINARY(255))
GO

-- get a random file name for our tests
DECLARE @FILE_NAME NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

SET @FILE_NAME = CAST(CAST((RAND() * 1000000) AS INT) AS NVARCHAR(255))

-- create master key the master key using a password
USE encrypt_test1
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'asldkjfaseiorujkhadfjkleio203948as;ehfjk'

-- create certificate
IF NOT EXISTS
    (SELECT * FROM sys.certificates WHERE [name] = 'AimsCert01')
    CREATE CERTIFICATE AimsCert01
    WITH SUBJECT = 'Codes';

-- create symmettric key.  The KEY_SOURCE, ALGORITHM, AND IDENTITY_VALUE must be
-- specified, and be identical between databases.
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE SYMMETRIC KEY CodeKey01 
        WITH KEY_SOURCE = 'a;sli98754;uy98asjh',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = '435987;klasjdf'
        ENCRYPTION BY CERTIFICATE AimsCert01;

-- store some encrypted data
DECLARE @Name NVARCHAR(255);
DECLARE @EncryptedName VARBINARY(255);

SET @Name = 'RED';

-- open/close the symmetric key surrounding all encrypted database activity
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SET @EncryptedName = EncryptByKey(Key_GUID('CodeKey01'), @Name);
INSERT INTO Color(Name, EncryptedName) VALUES(@Name, @EncryptedName);

CLOSE SYMMETRIC KEY CodeKey01;

-- verify the data was stored as expected
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- copy data into encrypt_test2
INSERT INTO encrypt_test2..Color(Name, EncryptedName)
    SELECT Name, EncryptedName
        FROM Color

-- retrieve data using encrypt_test1's key to verify
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM encrypt_test2..Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- now, to share encrypted data between databases, we must
--    a) use the same SERVICE MASTER KEY
--    b) use the same DATABASE MASTER KEY
--    c) use the same CERTIFICATE
--    d) use the same SYMMETRIC KEY

-- this means we need to back up all of these.  Since our tests
-- are on the same server, we don't really need the SERVICE MASTER KEY,
-- but it's provided for completeness

-- backup SMK
SET @SQL = 'BACKUP SERVICE MASTER KEY 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.smk''
    ENCRYPTION BY PASSWORD = ''3dH85Hhk003GHk2597gheij4'';'

EXEC sp_ExecuteSQL @SQL

-- backup DMK
SET @SQL = 'BACKUP MASTER KEY 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.dmk'' 
    ENCRYPTION BY PASSWORD = ''sd092735kjn$&adsg'';'

EXEC sp_ExecuteSQL @SQL

-- backup CERT
SET @SQL = 'BACKUP CERTIFICATE AimsCert01 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.cer''
    WITH PRIVATE KEY ( 
    FILE = ''c:\encrypt_test\' + @FILE_NAME + '.pvk'' , 
    ENCRYPTION BY PASSWORD = ''9n34khUbhk$w4ecJH5gh'' );'

EXEC sp_ExecuteSQL @SQL

-- restore SMK
-- NOTE!!!! DON'T DO THIS IF YOU ALREADY HAVE ANY ENCRYPTED DATA
--        ON THIS SERVER USING A DIFFERENT SERVICE MASTER KEY.  YOU
--        WILL NO LONGER BE ABLE TO DECRYPT ANY DATA ENCRYPTED USING
--        THE PREVIOUS SERVICE MASTER KEY
USE encrypt_test2
SET @SQL = 'RESTORE SERVICE MASTER KEY 
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.smk''
    DECRYPTION BY PASSWORD = ''3dH85Hhk003GHk2597gheij4'';'

EXEC sp_ExecuteSQL @SQL

-- restore DMK
SET @SQL = 'RESTORE MASTER KEY 
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.dmk''
    DECRYPTION BY PASSWORD = ''sd092735kjn$&adsg'' 
    ENCRYPTION BY PASSWORD = ''asldkjfaseiorujkhadfjkleio203948as;ehfjk'';'

EXEC sp_ExecuteSQL @SQL

-- We want to change the encryption mode to use the SERVICE MASTER KEY.
-- This way we don't have to open the MASTER KEY every time we want to
-- do any encryption.
OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'asldkjfaseiorujkhadfjkleio203948as;ehfjk';

ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

-- restore CERT
SET @SQL = 'CREATE CERTIFICATE AimsCert01
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.cer''
    WITH PRIVATE KEY (
    FILE = ''c:\encrypt_test\' + @FILE_NAME + '.pvk'', 
    DECRYPTION BY PASSWORD = ''9n34khUbhk$w4ecJH5gh'');'

EXEC sp_ExecuteSQL @SQL

-- create symmetric key.  NOTE: Use identical settings from above.
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE SYMMETRIC KEY CodeKey01 
        WITH KEY_SOURCE = 'a;sli98754;uy98asjh',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = '435987;klasjdf'
        ENCRYPTION BY CERTIFICATE AimsCert01;

-- read data
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- Attempt to backup and restore a database and verify that you can
-- still read the encrypted data
USE master
SET @SQL = 'BACKUP DATABASE encrypt_test1
    TO DISK = ''c:\encrypt_test\' + @FILE_NAME + '.bak'';'

EXEC sp_ExecuteSQL @SQL

--SET @SQL = 'RESTORE FILELISTONLY
--    FROM DISK = ''c:\encrypt_test\' + @FILE_NAME + '.bak'';'
--
--EXEC sp_ExecuteSQL @SQL

-- restore as encrypt_test3
SET @SQL = 'RESTORE DATABASE encrypt_test3
    FROM DISK=''C:\encrypt_test\' + @FILE_NAME + '.bak''
    WITH MOVE ''encrypt_test1'' TO ''C:\encrypt_test\' + @FILE_NAME + '.mdf'',
    MOVE ''encrypt_test1_log'' TO ''C:\encrypt_test\' + @FILE_NAME + '.ldf'';'

EXEC sp_ExecuteSQL @SQL
GO
USE encrypt_test3

-- retrieve data from encrypt_test3 using its keys
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;

 

If you run the above code, you'll get 4 result sets, all of which should be identical.

Posted on Friday, March 23, 2007 1:40 PM Database Practices | Back to top


Comments on this post: Moving Encrypted Data Between Databases

# re: Moving Encrypted Data Between Databases
Requesting Gravatar...
Hey - Sorry that this is a comment not really related to your post, but how do you do code colorization? Is it just a tool elsewhere (like Manoli.net) or is there a standardized GWB-ified way of doing it? Thanks!
Left by Rob on Mar 23, 2007 6:29 PM

# re: Moving Encrypted Data Between Databases
Requesting Gravatar...
Well, I'd like to say that I did a lot to make that happen, but it's not so. I use Windows Live Writer as my blogging tool, and Visual Studio for writing code. Windows Live writer has a number of plug-ins available and one is called "Insert Code Snippet". So, I wrote the code in VS, copied it to the clipboard and hit "Insert Code Snippet" in Windows Live Writer.

Voila.
Left by Chris J. Breisch on Mar 23, 2007 8:06 PM

# re: Moving Encrypted Data Between Databases
Requesting Gravatar...
This blog is really cool. I was facing the same problem and this soln works for me :-)
Left by Arul Prasad on Apr 04, 2007 2:46 AM

# re: Moving Encrypted Data Between Databases
Requesting Gravatar...
I really like your post. Is it possible to Moving Encrypted Data Between Servers?

Thanks.
Binh
Left by binh on Sep 30, 2008 5:28 PM

Your comment:
 (will show your gravatar)


Copyright © Chris J. Breisch | Powered by: GeeksWithBlogs.net