SQLSERVER SQL TDE database encryption operation
1, Create the master database key under the master database
stay Transact-SQL execute below
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master master data key password';
2, Create a certificate to protect the database encryption key
stay Transact-SQL execute below
USE master
CREATE CERTIFICATE master_server_cert WITH SUBJECT = N'the name of the certificate';
3, Creating a database
Tip: If encrypting an existing database, you can skip this step.
stay Transact-SQL execute below
CREATE DATABASE db_encryption_test;
4, Create the key for the database to be encrypted
(Choose one of the following two operations to complete the operation).
1. Execute under Transact SQL.
USE db_encryption_test;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
2. Operate in SQL Server Management Studio
Right click on the database you want to encrypt -> Task -> Manage database encryption.
Select the algorithm to encrypt
Select server certificate.
5, Encrypt database
(Choose one of the following two operations to complete the operation)
Script Execution
Execute under Transact SQL
Use db_Encryption_Test
Alter DATABASE db_Encryption_Test SET ENCRYPTION ON;
2. Operate in SQL Server Management Studio
Open the previous management database encryption
Check the dialog box [Set database encryption to ON (S)].
SQL Server Management Studio can be used in conjunction with the previous step.
The above operations have completed database encryption.
When opening the database properties dialog box, select [Options] and you will see that the [Encryption enabled] option is true, which indicates that the database has been encrypted SQLSERVER SQL TDE database decryption operation.
Reverse decryption operation
1, Decrypting databases
USE db_encryption_test;
ALTER DATABASE db_encryption_test SET ENCRYPTION OFF;
After execution, the database will display as unencrypted Can be executed.
select name,is_master_key_encrypted_by_server from sys.databases
View which databases are encrypted.
2, Delete database key
USE db_encryption_test;
DROP DATABASE ENCRYPTION KEY;
Can be executed.
SELECT * FROM master.sys.dm_database_encryption_keys
Check which databases still have keys.
3, Delete certificate
USE master;
DROP CERTIFICATE MyServerCert (certificate name) ;
can be executed SELECT * FROMmaster.sys.certificates view which certificates are available
4, Delete main database key
USE master;
DROP MASTER KEY;
can be executed select * from master.sys.key_encryptions check if the master key has been deleted
SQLSERVER SQL TDE database backup encryption certificate.
USE master;
BACKUP CERTIFICATE master_server_cert TO FILE = 'D:MSSQLCertificatemaster_server_cert.cer'
WITH PRIVATE KEY (
FILE = 'D:MSSQLCertificatemaster_server_cert.pvk' ,
ENCRYPTION BY PASSWORD = '123456789' );
When encrypting the database, you will be prompted to backup the certificate.
SQLSERVER SQL TDE database remote attachment database
1, Detaching databases in TDE encrypted environment
execmaster.dbo.sp_detach_db @dbname = N'db_encryption_test ';
After separating the database, copy the database files and log files to the new environment for attachment;
2, Attach a database in a new environment without recovery authentication
CREATE DATABASE db_encryption_test ON
(FILENAME = N'C:SQLData db_encryption_test.mdf'), --data files
(FILENAME = N'C:SQLData db_encryption_test _log.ldf') --log files
FOR ATTACH;
When completing the above additional operations, an error will be reported:
Unable to find server certificate with fingerprint '0xA7ED737B887F1707B99F4E8D1C43B54AA25AC542'. (Prove successful database encryption).
3, Create the master database key for the master database in a new environment
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master master data key password';
4, Restoring certificates in a new environment
CREATE CERTIFICATE MyServerCert
FROM FILE = N'C:SQLDataKEYMyServerCert.cer'
WITH PRIVATE KEY
(
FILE = N'C:SQLDataKEYMyServerCert_key.pvk',
DECRYPTION BY PASSWORD = N'123456789'
);
5, Attach a database in a new environment where authentication has been restored
CREATE DATABASE db_encryption_test ON
(FILENAME = N'C:SQLData db_encryption_test.mdf'), --data files
(FILENAME = N'C:SQLData db_encryption_test _log.ldf') --log files
FOR ATTACH;
Attachment successful.
SQLSERVER SQL TDE database remote restore database
1, Backing up databases in TDE encrypted environment
BACKUP DATABASE db_encryption_testTO DISK = N'D:db_encryption_test.bak'
2, Restoring a database in a new environment without recovery authentication.
RESTORE DATABASE db_encryption_test FROM DISK = N'C:SQLDatadb_encryption_test.bak'
WITH RECOVERY,
MOVE N'db_encryption_test' TO N'C:SQLDatadb_encryption_test.mdf',
MOVE N'db_encryption_test _log TO N'C:SQLDatadb_encryption_test _log.ldf';
When completing the above additional operations, an error will be reported:
Unable to find server certificate with fingerprint '0xA7ED737B887F1707B99F4E8D1C43B54AA25AC542'. (Prove successful database encryption).
3, Create the master database key for the master database in a new environment
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master master data key password';
4, Restoring certificates in a new environment
CREATE CERTIFICATE MyServerCert
FROM FILE = N'C:SQLDataKEYMyServerCert.cer'
WITH PRIVATE KEY
(
FILE = N'C:SQLDataKEYMyServerCert_key.pvk',
DECRYPTION BY PASSWORD = N'123456789'
);
5, Restore the database in a new environment where authentication has been restored
RESTORE DATABASE db_encryption_test FROM DISK = N'C:SQLDatadb_encryption_test.bak'
WITH RECOVERY,
MOVE N'aa' TO N'C:SQLDatadb_encryption_test.mdf',
MOVE N'aa_log' TO N'C:SQLDatadb_encryption_test _log.ldf';
Restore successful
The above operation instructions: SQL SERVER's TDE encryption is designed to encrypt database files, log files, and backup files
When there is no backup certificate, the password for the certificate is forgotten, or the certificate is lost, it can cause problems that cannot be restored remotely, but it can be restored locally. So the certificate and password need to be saved well
When the user owns the db of the master database_Owner permission or server-level sysadmin permission can export certificates
If the database is encrypted after certificate backup, remote recovery can also be performed.