How to transfer sql encrypted data between SQL Server 2005 databases?

Question

I have an existing SQL Server 2005 database that contains data encrypted using a Symmetric key. The symmetric key is opened using a password. I am working on an upgrade to the front end applications that use this database, which include adding dozens of new tables, stored procedures, UDFs, etc. and dozens of modifications to existing tables and database objects. To that end I am making a copy of the existing development database, so that the current system can be independently supported, maintained, and updated while new development takes place.

What is a good way to go about copying the database? Normally, I'd take a backup of the existing database, and then restore it to the new database. However, will this be feasible given the encrypted data? Will I still be able to encrypt and more importantly decrypt data in the new database using the existing symmetric key and password?

Might I instead want to use DTS to transfer the existing schema only. Create a new symmetric key/password in the new database. Then write ad hoc queries to transfer the data, decrypting using existing key/password, and encrypting using new key/password in new database.

I guess at the heart of this is, are symmetric keys good for encrypting/decrypting data in a single database or in many databases on the same server?

Answer 1

The Symmetric keys you are referring to are Database Master Keys (DMKs). They are held at the Database level, so a backup/restore to another SQL server should work OK (with the caveat of differing service accounts, which this thread alludes to)

Before you do anything make sure you have a backup of your keys (presumably you've already done this):

USE myDB
GO
BACKUP MASTER KEY TO FILE = 'path_to_file'
    ENCRYPTION BY PASSWORD = 'password'
GO

From this article:

When you create a Database Master Key, a copy is encrypted with the supplied password and stored in the current database. A copy is also encrypted with the Service Master Key and stored in the master database. The copy of the DMK allows the server to automatically decrypt the DMK, a feature known as "automatic key management." Without automatic key management, you must use the OPEN MASTER KEY statement and supply a password every time you wish to encrypt and/or decrypt data using certificates and keys that rely on the DMK for security. With automatic key management, the OPEN MASTER KEY statement and password are not required.