Copy SQL server certificates and symmetric keys

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.