SQL Server2016 数据库透明加密TDE、解密、加密备份数据还原

天一道长--玄彬

已于 2023-10-20 17:27:18 修改

阅读量693

点赞数 2

文章标签：数据库 sqlserver sql

于 2023-10-20 11:29:15 首次发布

本文链接：https://blog.csdn.net/weixin_40344051/article/details/133942126

版权

官方文档描述：数据库文件加密在页面级执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时会进行解密。 TDE 不会增加已加密数据库的大小。会增加CPU3%-5%的消耗

为SQL Server数据库启用TDE加密的过程：

1、创建主密钥。
2、创建或获取由主密钥保护的证书。
3、备份密钥,证书和证书的私钥,为了在别的机器上还原加密后的数据库，必须要有证书以及证书的私钥
4、创建数据库加密密钥并使用此证书保护该密钥。
5、将数据库设置为使用加密。
USE master;
GO
-- 查询证书：
select * from sys.certificates
-- 查询主密钥：
SELECT * FROM sys.symmetric_keys

-- 查看日志文件加密的状态
select * from sys.dm_database_encryption_keys

-- 查询数据库是否加密
SELECT database_id,name,is_encrypted FROM sys.databases WHERE name = 'icon_ods'

/**************************开始进行TED加密*****************/

USE master;
GO

-- 创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TianQi#aasskdhh!';
GO

-- 创建证书
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'MyDEKCertificate2023', START_DATE = '2023-10-19',EXPIRY_DATE = '2099-12-31'; --证书的有效时间;
GO
--备份数据库主密钥MASTER KEY
BACKUP MASTER KEY TO FILE = 'D:\DataBaseBak\DataBaseEnc\masterkey' ENCRYPTION BY PASSWORD = 'TianQi#aasskdhh!'
GO

--备份数据库证书和证书的私钥
USE master;
GO
BACKUP CERTIFICATE MyServerCert TO FILE = 'D:\DataBaseBak\DataBaseEnc\MyServerCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\DataBaseBak\DataBaseEnc\MyServerCert.pvk' ,
ENCRYPTION BY PASSWORD = 'TianQi#123456' );
GO

USE [icon_ods];
GO
-- 创建一个数据库加密密钥
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO

-- 开启加密
ALTER DATABASE icon_ods SET ENCRYPTION ON;
GO

-- 在进行正常的数据库备份，全备.bak文件

/**************************结束TED加密*****************/

-----------------------对已开启TED加密数据库备份还原需要的操作------------------------------------

-- 还原数据库主密钥
USE master;
GO
RESTORE MASTER KEY
FROM FILE = 'D:\DataBaseBak\DataBaseEnc\masterkey'
DECRYPTION BY PASSWORD = 'TianQi#aasskdhh!'
ENCRYPTION BY PASSWORD = 'TianQi#aasskdhh!';
GO

-- 如果报错消息 15581，在执行此操作之前，请在数据库中创建一个主密钥或在会话中打开该主密钥。可执行以下语句
-- 开启数据库主密钥的服务器主密钥加密方式：
Use master
go
OPEN MASTER KEY DECRYPTION BY PASSWORD='TianQi#aasskdhh!'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
-- 使用以下查询来确定是否已为 master 数据库禁用服务主密钥对主密钥的自动解密：
-- 如果此查询返回的值为 0，则禁用了服务主密钥对主密钥的自动解密。
select is_master_key_encrypted_by_server from sys.databases where name = 'master'

-- 在通过备份的证书和证书的私钥进行还原证书
CREATE CERTIFICATE MyServerCert
FROM FILE = 'D:\DataBaseBak\DataBaseEnc\MyServerCert.cer'
WITH PRIVATE KEY (
FILE = N'D:\DataBaseBak\DataBaseEnc\MyServerCert.pvk',
DECRYPTION BY PASSWORD = 'TianQi#123456'
);
GO

-- 正常进行数据库.bak文件还原

-----------------------对已开启TED加密数据库备份还原结束------------------------------------

-- --- 额外知识补充关闭加密，删除证书，删除主密钥

-- 查看当前端点
SELECT * FROM sys.endpoints
-- 删除证书使用的端点
USE [master]
GO
DROP ENDPOINT SQLAG_Endpoint -- 端点名称
GO

-- 从数据库中删除TED加密,类似解密
ALTER DATABASE icon_ods SET ENCRYPTION OFF;
-- 删除透明数据库加密使用的数据库加密密钥，必须先解密完成，再删除数据库加密密钥
USE icon_ods;
GO
DROP DATABASE ENCRYPTION KEY;
GO
-- 查看日志文件加密的状态
USE icon_ods;
GO
SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3;
GO
--删除原有的证书和密钥
USE MASTER;
GO
DROP CERTIFICATE MyServerCert
GO
DROP MASTER KEY
GO

-- --- 额外知识补充关闭加密，删除证书，删除主密钥

-- SQL Server 2019 (15.x) 引入了 TDE 扫描,其中包含暂停和恢复语法
-- 暂停 TDE 加密扫描：
ALTER DATABASE icon_ods SET ENCRYPTION SUSPEND;
-- 恢复 TDE 加密扫描：
ALTER DATABASE icon_ods SET ENCRYPTION RESUME;

-- 还原证书,将导出的证书导入：
USE master;
GO
CREATE CERTIFICATE MyServerCer
FROM FILE = 'D:\DataBaseBak\DataBaseEnc\MyServerCert.cer';
GO

-- 备份服务主密钥

USE MASTER;
GO
-- 备份服务主密钥MASTER KE
BACKUP SERVICE MASTER KEY TO FILE = 'D:\DataBaseBak\DataBaseEnc\masterkey' ENCRYPTION BY PASSWORD = 'TianQi#aasskdhh!'
GO
-- 重新生成服务主密钥，类似于回收删除
ALTER SERVICE MASTER KEY REGENERATE;
-- 还原服务主密钥
USE MASTER;
GO
RESTORE SERVICE MASTER KEY
FROM FILE = 'D:\DataBaseBak\DataBaseEnc\masterkey'
DECRYPTION BY PASSWORD = 'TianQi#aasskdhh!';
GO

-- 数据库加密备份和还原，是通过证书进行加密备份，也就是说做这个操作需要创建主密钥和证书，还原的时候需要还原主密钥和证书
BACKUP DATABASE List
TO DISK = N'D:\DataBaseBak\List\List_Q_202310201544.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = MyServerCert
),
STATS = 10
GO

-- 数据库加密后还原，还原主密钥和证书后，正常数据还原即可
RESTORE DATABASE [List] FROM DISK = N'D:\DataBaseBak\DataBaseEnc\list_Q_202310201431.bak' WITH FILE = 1, MOVE N'list' TO N'D:\DataBaseBak\DataBaseEnc\list.mdf', MOVE N'list_log' TO N'D:\DataBaseBak\DataBaseEnc\list_log.ldf', NOUNLOAD, REPLACE, STATS = 5