Copy SQL server certificates and symmetric keys

The boss has another command and asked us to encrypt our database. I handed this task over to the database. There is no way. the hard work of the database DBA is here, in the past, I had some knowledge about database encryption, but I didn't know much about it. The steps of setting column encryption and TDE only copy the data on msdn or on the Internet. After two days of research and learning, I think I have a deep understanding.

For information on column encryption can refer to the Microsoft msdn write very good: http://msdn.microsoft.com/en-us/library/ms189586 (V = SQL .105). aspx

For more information about TDE encryption, see: http://msdn.microsoft.com/en-us/library/bb934049 (D = printer, V = SQL .105). aspx

I will talk about certificates, symmetric and non-symmetric keys themselves:

 

1. The Service master key and database master key are both hierarchical keys.
2. Under normal circumstances, certificates, symmetric keys, and asymmetric keys are generated differently each time. Even if the command is the same.
3. For asymmetric Keys, keys cannot be backed up or copied. Once created, it will always be the same.
4. If you want to ensure that the certificates are the same, you can use the backup and recovery methods to copy the certificates.

-- Backup Certificate

Backup certificate master_database_cert to file = 'd: \ master_database_cert_20121226.cer'

With private key (

File = 'd: \ master_database_priv_201212261431.pvk ',

Encryption By Password = 'gaupeng123 ');

-- Restore Certificate

Create certificate master_server_cert

From file = 'd: \ gwszapp03_cert \ master_database_cert_20121226.cer'

With private key (file = 'd: \ gwszapp03_cert \ master_database_priv_201212261431.pvk ',

Decryption by Password = 'gaupeng123 ');

5. For symmetric keys, you can set the parameters to copy certificates between different entities.

Implemented through the key_source/identity_value parameter.

-- Create symmetric keys

Create foreign Ric key [key_datashare]
Key_source = 'my key generation BITs. This is a shared secret! ',
Algorithm = aes_256,
Identity_value = 'key identity generation BITs. Also a shared secret'
Encryption by certificate [cert_keyprotection];
-- Generate symmetric keys in other databases

Create foreign Ric key [key_datashare001]
Key_source = 'my key generation BITs. This is a shared secret! ',
Algorithm = aes_256,
Identity_value = 'key identity generation BITs. Also a shared secret'
Encryption by certificate [cert_keyprotection001];

-- The query result shows that the two symmetric keys are the same.

I have summarized the above in a rush and hope you can give me more advice.