In the real environment, we will encounter the loss or theft of database backup disks. At this time, malicious destructive parties only need to restore or attach the database to view data. Next, I will introduce the Transparent Data Encryption method to protect your database. If data A is encrypted using TDE, the database uses A certificate-protected encrypted data key because it cannot be restored or attached to A backup disk.
1. What is TDE?
Transparent database encryption performs real-time I/O encryption and decryption on data and log files. this encryption uses the database encryption Key (Data Encrtyption Key). DEK is a symmetric password protected by a certificate stored in the master database of the server, or an asymmetric Key protected by the EKM module. use (Advanced Encryption Standard) or 3DES (Data Encryption Standard) Encryption algorithm to encrypt Data without changing existing applications.
2. How to Use TDE
1. Create a CMK
2. Create or obtain a certificate protected by this key
3. Create a database encryption key and the certificate protects the key.
4. Set the database to encrypted
The following example performs TDE encryption on TEST DB
-- Create a master key
USE master
GO
Create master key encryption by password = '~ Qaz123 [] \ ';
GO
-- Create a certificate
Create certificate MyServerCert with subject = 'my DEK Certificate ';
GO
-- Create a database encryption key and protect it by the certificate
USE TEST
GO
CREATE DATABASE ENCRYPTION KEY
With algorithm = AES_128
Encryption by server certificate MyServerCert;
GO
-- Set the database to use encryption
USE TEST
ALTER DATABASE TEST
Set encryption on;
GO
After you complete the preceding operations, verify the detailed information of the encrypted data.
SELECT DB_NAME (database_id) AS DatabaseName, * FROM sys. dm_database_encryption_keys
At this time, you will find that in addition to test db, there is also tempdb, because only when you encrypt any database, it will automatically encrypt tempdb
3. Check whether TDE works.
Use the bak file on another Server to restore the TEST database.
RESTORE DATABASE TEST
From disk = 'e: \ database \ TEST. Bak'
With move 'test' TO 'e: \ database \ TEST. mdf ',
MOVE 'test _ log' TO 'e: \ database \ TEST. ldf ',
NOUNLOAD, REPLACE
After the above code is executed
The Message is as follows:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x739e848fd2ecb7d945d39b7836bcd65b55d75d43 '.
Msg 3013, Level 16, State 1, Line 1
Restore database is terminating abnormally.
The same is true if you use the append database. How can you solve this problem? Add the same certificate on this Server
Before adding the certificate to this Server, back up the certificate to the file on the previous Server (in fact, I have done this operation when adding the certificate)
The Code is as follows:
-- Backup certificate nadprimary key to file
USE master
GO
Backup certificate MyServerCert to file = 'd: \ Test DB \ Test \ MyServerCert. cert'
With private key (
FILE = 'd: \ Test DB \ Test \ MyServerCertPrivate. key ',
Encryption by password = '~ Qaz123 [] \ ');
GO
After the backup is successful, use the successfully backed up certificate to create a certificate on the target Server.
USE master
GO
Create certificate MyServerCert
From file = 'e: \ learn \ database \ test database \ MyServerCert. cert'
With private key (
FILE = 'e: \ learn \ database \ test database \ MyServerCertPrivate. key'
, Decryption by password = '~ Qaz123 [] \ ')
GO
Created successfully. Check whether the certificate is created successfully.
SELECT * FROM sys. certificates where name = 'myservercert'
Now restore the database again
RESTORE DATABASE TEST
From disk = 'e: \ database \ TEST. Bak'
With move 'test' TO 'e: \ database \ TEST. mdf ',
MOVE 'test _ log' TO 'e: \ database \ TEST. ldf ',
NOUNLOAD, REPLACE
The database is restored successfully! ,
From the column ws_hgo