When a user database is available and TDE is enabled, encryption is implemented at the page level when it is written to disk. Decrypts when the data page is read into memory. If a database file or database backup is stolen, the original certificate that is not used for encryption will not be accessible. This is almost the most exciting feature of the SQL Server2008 security option, and with it we can at least take some of the initial malicious peep out of the box.
The following two examples show how to enable and maintain transparent data encryption.
Example one, enable transparent encryption (TDE)
/********************tde**************** 3w@live.cn ****************/
Use Master
Go
--------Delete Old master key **********************3w@live.cn
--------Drop Master Key
--------Go
--Create master key **********************3w@live.cn
Create MASTER KEY Encryption
by PASSWORD = ' b19ace32-ab68-4589-81ae-010e9092fc6b '
Go
--Create a certificate for transparent data encryption **********************3w@live.cn
CREATE Certificate Tde_server_certificate
with SUBJECT = ' Server-level cert for TDE '
Go
Use Db_encrypt_demo
Go
--The first step: now begin transparent Encryption **********************3w@live.cn
Create DB encryption key--creating a database encryption key
With algorithm = triple_des_3key--encryption method
Encryption by server certificate tde_server_certificate--using the certificate encryption
Go
/*
Warning:the Certificate used for encrypting the database encryption key
Has not been backed up.
Should immediately back up the certificate and the private key
associated with the certificate.
If The certificate ever becomes unavailable or
If you are must restore or attach the database on another server,
You must have backups of both the certificate and the private key
Or you are not being able to open the database.
*/
--The second step: Open the encryption switch **********************3w@live.cn
ALTER DATABASE Db_encrypt_demo
SET encryption on
Go
--See if the database is encrypted
SELECT is_encrypted
From sys.databases
WHERE name = ' Db_encrypt_demo '
Note: Once the encryption is applied to the database, the server-level certificate should be backed up immediately!
There is no encryption DEK certificate, the database will not open, attached to other servers can not be used, database files will not be hack. If a DBA wants to legitimately move a database from an instance of SQL Server to another instance of SQL Server, she should first back up the server-level certificate and then create the certificate in a new instance of SQL Server. You can now legitimately back up, restore, or append data and log files to the database.
Example two, managing and removing transparent encryption (TDE)
Copy Code code as follows:
Use Db_encrypt_demo
Go
--Modify the encryption algorithm
ALTER DATABASE Encryption KEY
Regenerate with algorithm = aes_128
Go
SELECT db_name (database_id) Databasenm,
Case Encryption_state
When 0 THEN ' No encryption '
When 1 THEN ' unencrypted '
When 2 THEN ' encryption in progress '
When 3 THEN ' Encrypted '
When 4 THEN ' Key Change in progress '
When 5 THEN ' decryption in progress '
End Encryption_state,
Key_algorithm,
Key_length
From Sys.dm_database_encryption_keys
/*
Encryption of all user databases also includes processing of tempdb
Databasenm encryption_state Key_algorithm Key_length
tempdb Encrypted AES 256
Db_encrypt_demo Encrypted AES 128
*/
Note: encryption for all user databases also includes processing of tempdb
In addition to changing the DEK algorithm, we can also change the server-level certificate used to encrypt DEK (the certificate should be changed periodically)
Copy Code code as follows:
Use master
Go
CREATE Certificate Tde_server_certificate_v2
with SUBJECT = ' Server-level cert for TDE V2 '
Go
Use Db_encrypt_demo
Go
ALTER DATABASE Encryption KEY
Encryption by SERVER Certificate tde_server_certificate_v2--modified with a new certificate dek
--Remove database transparent encryption
ALTER DATABASE Db_encrypt_demo
SET encryption off
Go
--After removing the TDE, you can delete the Dek
Use Db_encrypt_demo
Go
Drop DATABASE Encryption KEY
Go
Note: If you delete dek is the last user-defined database that uses TDE in an instance of SQL Server, tempdb will also become unencrypted after the instance of SQL Server restarts.
Summary:
1, this article mainly introduces the use of transparent data encryption (TDE).
2. Changes to Dek also affect the encryption state of the tempdb database.
The SQL Server security family is here to pause. Thank you for your patience, and welcome to ask for the month. 3w@live.cn
