2 deleted 12 characters in body
source | link

Used an empty SQL 2012 test instance, restored SMK from Server2, and restored db2 (my symmetric key admin_key and certificate admin_cert were included by default in the backup/restore).

BACKUP SERVICE MASTER KEY TO FILE = 'path\prod.SMK' ENCRYPTION BY PASSWORD = pass RESTORE SERVICE MASTER KEY FROM FILE = 'path\prod.SMK' DECRYPTION BY PASSWORD = pass

This setup now allows access to the data in the encrypted column, just like on the production system. Run query in db2 on this test instance to demonstrate:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] FROM table_with_enc_column CLOSE SYMMETRIC KEY admin_key

Start with this script in db2 on the test instance to remove the SMK encryption and switch to password encryption:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = pass ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY CLOSE SYMMETRIC KEY admin_key

Now backup this database from the test instance and restore to the destination Server1. No need to restore the SMK on this one.

Run this script in db2 on Server1 to set the SMK encryption:

OPEN MASTER KEY DECRYPTION BY PASSWORD = pass ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = pass

All good, data in encrypted columns is accessible on Server1 both in db1 and db2, with SMK A as intended.

If you have any encrypted data on your instances, make sure you know the passwords for your DBMK, and you backup your SMK. Otherwise, the db backup might be useless. There's a very detailed MSDN post on handling issues with the SMK: http://blogs.msdn.com/b/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx

Used an empty SQL 2012 test instance, restored SMK from Server2, and restored db2 (my symmetric key admin_key and certificate admin_cert were included by default in the backup/restore).

BACKUP SERVICE MASTER KEY TO FILE = 'path\prod.SMK' ENCRYPTION BY PASSWORD = pass RESTORE SERVICE MASTER KEY FROM FILE = 'path\prod.SMK' DECRYPTION BY PASSWORD = pass

This setup now allows access to the data in the encrypted column, just like on the production system. Run query in db2 on this test instance to demonstrate:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] FROM table_with_enc_column CLOSE SYMMETRIC KEY admin_key

Start with this script in db2 on the test instance to remove the SMK encryption and switch to password encryption:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = pass ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY CLOSE SYMMETRIC KEY admin_key

Now backup this database from the test instance and restore to the destination Server1. No need to restore the SMK on this one.

Run this script in db2 on Server1 to set the SMK encryption:

OPEN MASTER KEY DECRYPTION BY PASSWORD = ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD =

All good, data in encrypted columns is accessible on Server1 both in db1 and db2, with SMK A as intended.

If you have any encrypted data on your instances, make sure you know the passwords for your DBMK, and you backup your SMK. Otherwise, the db backup might be useless. There's a very detailed MSDN post on handling issues with the SMK: http://blogs.msdn.com/b/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx

Used an empty SQL 2012 test instance, restored SMK from Server2, and restored db2 (my symmetric key admin_key and certificate admin_cert were included by default in the backup/restore).

BACKUP SERVICE MASTER KEY TO FILE = 'path\prod.SMK' ENCRYPTION BY PASSWORD = pass RESTORE SERVICE MASTER KEY FROM FILE = 'path\prod.SMK' DECRYPTION BY PASSWORD = pass

This setup now allows access to the data in the encrypted column, just like on the production system. Run query in db2 on this test instance to demonstrate:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] FROM table_with_enc_column CLOSE SYMMETRIC KEY admin_key

Start with this script in db2 on the test instance to remove the SMK encryption and switch to password encryption:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = pass ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY CLOSE SYMMETRIC KEY admin_key

Now backup this database from the test instance and restore to the destination Server1. No need to restore the SMK on this one.

Run this script in db2 on Server1 to set the SMK encryption:

OPEN MASTER KEY DECRYPTION BY PASSWORD = pass ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = pass

All good, data in encrypted columns is accessible on Server1 both in db1 and db2, with SMK A as intended.

If you have any encrypted data on your instances, make sure you know the passwords for your DBMK, and you backup your SMK. Otherwise, the db backup might be useless. There's a very detailed MSDN post on handling issues with the SMK: http://blogs.msdn.com/b/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx

1
source | link

Used an empty SQL 2012 test instance, restored SMK from Server2, and restored db2 (my symmetric key admin_key and certificate admin_cert were included by default in the backup/restore).

BACKUP SERVICE MASTER KEY TO FILE = 'path\prod.SMK' ENCRYPTION BY PASSWORD = pass RESTORE SERVICE MASTER KEY FROM FILE = 'path\prod.SMK' DECRYPTION BY PASSWORD = pass

This setup now allows access to the data in the encrypted column, just like on the production system. Run query in db2 on this test instance to demonstrate:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] FROM table_with_enc_column CLOSE SYMMETRIC KEY admin_key

Start with this script in db2 on the test instance to remove the SMK encryption and switch to password encryption:

OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = pass ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY CLOSE SYMMETRIC KEY admin_key

Now backup this database from the test instance and restore to the destination Server1. No need to restore the SMK on this one.

Run this script in db2 on Server1 to set the SMK encryption:

OPEN MASTER KEY DECRYPTION BY PASSWORD = ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD =

All good, data in encrypted columns is accessible on Server1 both in db1 and db2, with SMK A as intended.

If you have any encrypted data on your instances, make sure you know the passwords for your DBMK, and you backup your SMK. Otherwise, the db backup might be useless. There's a very detailed MSDN post on handling issues with the SMK: http://blogs.msdn.com/b/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx