Using Transparent Data Encryption (Transparent Data Encryption) in sqlserver to encrypt your Data

 

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