TDE encryption for SQL Server

The primary role of TDE is to prevent a database backup or data file from being stolen, and the person who steals the database backup or file cannot recover or attach the database without the data encryption key.

use [master];

GO



--See if the master database is encrypted

SELECT Name,is_master_key_encrypted_by_server from
sys.databases;



--Create a master database key under the master database

CREATE MASTER KEY Encryption by PASSWORD = N ' ^&* () 0A ';



--View key information under the master database

SELECT * from Sys.symmetric_keys;



--Create a certificate to protect the database encryption key (DEK)

CREATE CERTIFICATE Master_server_cert with
SUBJECT = N ' Master Protect DEK Certificate ';



IF db_id (' db_encryption_test ') is not NULL

DROP DATABASE Db_encryption_test



--Create a test database

CREATE DATABASE db_encryption_test;

GO



Use Db_encryption_test;



--Create a DEK database encryption key (symmetric key) protected by Master_server_cert

CREATE DATABASE Encryption KEY

with algorithm = aes_128

Encryption by SERVER CERTIFICATE Master_server_cert;

GO

Use master;
BACKUP CERTIFICATE master_server_cert to FILE = ' D:\MSSQL\Certificate\master_server_cert.cer '
With PRIVATE KEY (
FILE = ' D:\MSSQL\Certificate\master_server_cert.pvk ',
Encryption by PASSWORD = ' ^&* () 0A ';

--Accordingly, we also back up the database master key (master)
Use master;
--If the automatic decryption of the master key is not enabled
--open MASTER KEY decryption by PASSWORD = ' ^&* () 0A ';
BACKUP MASTER KEY to FILE = ' D:\MSSQL\MasterKey\master.cer '
Encryption by PASSWORD = ' ^&* () 0A ';
GO

--In the production environment, set up a single user to run the encryption
ALTER DATABASE db_encryption_test SET single_user with ROLLBACK IMMEDIATE;
GO

--After the backup is successful, TDE encryption is turned on
ALTER DATABASE db_encryption_test SET encryption on;
GO

--Set multi-user access
ALTER DATABASE db_encryption_test SET multi_user with ROLLBACK IMMEDIATE;
GO

--See if the Db_encryption_test database is encrypted Encryption_state:3 TDE encrypted
SELECT db_name (database_id), encryption_state from Sys.dm_database_encryption_keys;
/*
It was found that tempdb was also encrypted. The MSDN explanation is that if there is a database in the instance that has TDE encryption enabled, tempdb is also encrypted
*/

--Next, find another machine or instance to test if the data file is stolen and prevent additional tests.
Use master;
EXEC sp_detach_db N ' db_encryption_test ';
GO


Use master;
--I first restored master key in his machine (his original machine Master library has no master key)
RESTORE MASTER KEY
From FILE = ' C:\Users\Administrator\Desktop\master.cer '
Decryption by PASSWORD = ' ^&* () 0A '
Encryption by PASSWORD = ' ^&* () 0A ';
GO

--If there is no automatic encryption
OPEN MASTER KEY decryption by Password=n ' ^&* () 0A ';
--Create a certificate
CREATE CERTIFICATE Master_server_cert
From FILE = ' C:\Users\Administrator\Desktop\master_server_cert.cer '
With PRIVATE KEY (FILE = ' C:\Users\Administrator\Desktop\master_server_cert.pvk ',
Decryption by PASSWORD = ' ^&* () 0A ';
GO
--Additional database
CREATE DATABASE Db_encryption_test
On PRIMARY
(
Filename=n ' C:\Users\Administrator\Desktop\db_encryption_test.mdf '
)
LOG on
(
Filename=n ' C:\Users\Administrator\Desktop\db_encryption_test_log.ldf '
)
For ATTACH;
GO

--Test success

--Close Database joins
CLOSE MASTER KEY

TDE encryption for SQL Server