MSSQL 2014 TDE透明加密的使用

https://learn.microsoft.com/zh-cn/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16

透明数据加密 (TDE) 可以加密 SQL Server、Azure SQL 数据库和 Azure Synapse Analytics 数据文件。 这种加密方式称为静态数据加密。

为了帮助保护用户数据库的安全，可以采取以下预防措施：

l 设计安全的系统。

l 对机密资产加密。

l 在数据库服务器外围构建防火墙。

但恶意方如果窃取了硬盘或备份磁带等物理介质，就可以还原或附加数据库并浏览其数据。

一种解决方案是加密数据库中的敏感数据，并使用证书保护用于加密数据的密钥。 此解决方案可以防止没有密钥的人使用这些数据。 但必须提前规划好此类保护。

TDE 对数据和日志文件进行实时 I/O 加密和解密。 加密使用的是数据库加密密钥 (DEK)。 数据库启动记录存储该密钥，供还原时使用。 DEK 是对称密钥，由服务器的 master 数据库存储的证书或 EKM 模块所保护的非对称密钥提供保护。

TDE 保护静态数据，也就是数据和日志文件。 它让你可以遵循许多法律、法规和各个行业建立的准则。 借助此功能，软件开发人员可以使用 AES 和 3DES 加密算法来加密数据，且无需更改现有的应用程序。

TDE的加密：数据库文件加密在页面级执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时会进行解密。 TDE不会增加已加密数据库的大小。

加密层次结构

Windows 数据保护 API (DPAPI) 位于加密树的根目录中，在计算机级别保护密钥层次结构，并用于保护数据库服务器实例的服务主密钥 (SMK,service master key) 。

SMK 保护存储在用户数据库级别的数据库主密钥 (DMK,database master key)，并保护证书和非对称密钥。 这些密钥反过来又保护对称密钥，进而保护数据。

TDE 使用与证书类似的层次结构。

使用 TDE 时，DMK 和证书必须存储在 master 数据库中。 仅用于TDE且称为数据库加密密钥 (DEK ,database encrypt key)  将在用户数据库中创建和存储。

下图显示了 TDE 加密体系结构。 在 SQL 数据库上使用 TDE 时，用户仅能配置数据库级项目（数据库加密密钥和 ALTER DATABASE 部分）。

1. 启用TDE的操作步骤

参考：

https://www.cnblogs.com/woodytu/p/5350075.html

http://www.sqlservercentral.com/articles/Stairway+Series/125948/

总体问分为4步：

1. 在master库创建数据库主密钥

2. 创建或获取受主密钥保护的证书。

3. 创建数据库加密密钥，并使用证书对其进行保护。

4. 将数据库设置为使用加密。

下面的示例演示如何启用和关闭TDE

-- 备份数据库后续用于测试

USE master;

GO

BACKUP DATABASE TSQL2008

    TO DISK = N'D:\TSQL2008.bak'

    WITH NOFORMAT, INIT, NAME = N'TSQL2008 Full Database Backup',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10;

GO

-- 切换到master库

USE master;

GO

-- 查看database master key信息

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

-- 创建主密钥并对秘钥设置密码

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Abcd1234';

GO

-- 查看master key信息

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

-- 备份master key

BACKUP MASTER KEY TO FILE = 'D:\mssql2022\backup\master.key' ENCRYPTION BY PASSWORD ='Abcd1234';

GO

-- 查看当前系统中证书的清单

select * from sys.certificates;

-- 创建受主密钥保护的证书

CREATE CERTIFICATE DBATEST_ServerCert WITH SUBJECT = 'My DEK Certificate';

GO

-- 再次查看证书清单

SELECT

NAME,

pvt_key_encryption_type,

pvt_key_encryption_type_desc,

is_active_for_begin_dialog,

issuer_name,

expiry_date,

start_date

FROM

sys.certificates;

-- 备份CERTIFICATE

BACKUP CERTIFICATE DBATEST_ServerCert TO FILE = 'D:\mssql2022\backup\DBATEST_ServerCert.cert'

WITH PRIVATE KEY (

  FILE = 'D:\mssql2022\backup\DBATEST_private.key',

  ENCRYPTION BY PASSWORD = 'Abcd1234'

);

GO

-- 切换到需要加密的数据库下

USE TSQL2008;

GO

-- 创建数据库加密秘钥，并使用此证书保护该秘钥

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DBATEST_ServerCert;

GO

-- 查看当前mssql系统中里的数据库加密秘钥清单

select db_name(database_id) , * from sys.dm_database_encryption_keys;

-- 将数据库设置为启用加密

USE TSQL2008;

GO

ALTER DATABASE TSQL2008 SET ENCRYPTION ON;

GO

-- 查看数据库的加密的进度（我这里演示的库比较小，所以加密很快）

SELECT DB_NAME(database_id) AS DatabaseName,

    key_algorithm AS [Algorithm],

    key_length AS KeyLength,

    CASE encryption_state

        WHEN 0 THEN 'No database encryption key present, no encryption'

        WHEN 1 THEN 'Unencrypted'

        WHEN 2 THEN 'Encryption in progress'

        WHEN 3 THEN 'Encrypted'

        WHEN 4 THEN 'Key change in progress'

        WHEN 5 THEN 'Decryption in progress'

    END AS EncryptionStateDesc,

    percent_complete AS PercentComplete

FROM sys.dm_database_encryption_keys;

GO

再次备份下数据库，后续会将这个数据库恢复到其他mssql机器上，模拟异机恢复的场景

BACKUP DATABASE TSQL2008
    TO DISK = N'D:\TSQL2008_with_tde.bak'
    WITH NOFORMAT, INIT, NAME = N'TSQL2008 Full Database Backup with tde',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

sys.dm_database_encryption_keys DMV的字段的含义：

TDE 扫描【注意】

要在数据库上启用 TDE， SQL Server 必须执行加密扫描。 扫描将数据文件中的每个页面读入缓冲池，然后将加密页面写入磁盘。

注意：

在开启 SQL Server 上的 TDE（Transparent Data Encryption）时，会出现“INPROGRESS ENCRYPTION”状态。在这个过程中，SQL Server 会加密数据库中的数据页，这可能会对系统的负载产生一定的影响。这种影响主要取决于以下几个因素：

l 数据库大小：加密的数据量越大，影响会越明显。

l 系统负载：在高负载情况下开启 TDE 可能会对性能产生更大的影响，因为 CPU 和磁盘资源会被用于加密数据。

l 硬件性能：服务器硬件的性能也将直接影响到 TDE 运行时对系统的实际影响。更强大的硬件能够更快地处理加密过程。

l SQL Server 版本：较新版本的 SQL Server 可能会在 TDE 的性能方面进行了一些优化，因此应该考虑使用最新版本来最大程度地减少影响。

总体来说，开启 TDE 期间会有一定的性能损耗，特别是在大型数据库和高负载环境中。最好在系统较为空闲的时候执行这个操作，以减少对生产系统的影响。通常情况下，一旦 TDE 加密完成，应该回到正常的性能水平。

alwayson集群和TDE

这里提供了一个解决方案： https://www.mssqltips.com/sqlservertip/3146/configuring-transparent-data-encryption-with-sql-server-2012-alwayson-availability-groups/

简单点说步骤就是3步：

l 在主副本和辅助副本上配置 SQL Server TDE

l 在辅助副本上初始化加密的 SQL Server 数据库

l 配置 SQL Server 可用性组

另外，对于已经启用了Alwayson集群的数据库，如果需要开启TDE也是可以的。

1、先把待加密的库从可用性数据库里面剔除

2、在主节点创建密钥对，并对库启用加密

3、将秘钥复制到备库并应用到数据库实例上

4、将加密后的库重新加回可用性数据库中

启用TDE后的数据的还原

场景：例如需要把当天的备份数据库还原到UAT（或者是定期恢复抽验）。

启用了 TDE 的数据库备份文件也使用 DEK 进行加密。 因此，在还原这些备份时，用于保护 DEK 的证书必须可用。 因此，除了备份数据库之外，一定要注意维护好服务器证书的备份。 如果证书不再可用，就会造成数据丢失。

假如要将启用TDE的TSQL2008库 还原到待加密的机器上，需要如下操作：

0、从已经启用TED的机器上，备份下库的文件

BACKUP DATABASE TSQL2008Copy
    TO DISK = N'D:\SQLBackups\TSQL2008Copy.bak'
    WITH NOFORMAT, INIT, NAME = N'TSQL2008Copy Full Database Backup',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

1、将之前备份的 CERTIFICATE 文件（一共2个文件），都拷贝到待进行加密的机器上

例如我这里都放到了 D:\mssql2022\backup\ 目录

2、看下master key是否存在，不存在则创建MASTER KEY

USE master;
GO

-- 先确认下是否已经存在了master key
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
GO

-- 如果不存在master key，则需要创建下
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Abcd1234';
GO

3、还原CERTIFICATE

select * from sys.certificates; 先看下cert文件是否已经被导入过了，没有的话就执行下面的CREATE CERTIFICATE操作

CREATE CERTIFICATE DBATEST_ServerCert FROM FILE = 'D:\mssql2022\backup\DBATEST_ServerCert.cert' 
WITH PRIVATE KEY (
  FILE = 'D:\mssql2022\backup\DBATEST_private.key',
  DECRYPTION BY PASSWORD = 'Abcd1234'
);
GO

4、还原数据，命令类似（注意data和log需要根据实际情况来修改）：

USE [master];
GO

RESTORE DATABASE TSQL2008
FROM DISK = N'/tmp/TSQL2008_with_tde.bak' WITH  FILE = 1, NOUNLOAD, STATS = 5,
        MOVE 'TSQL2008' TO N'/var/opt/mssql/data/TSQL2008Copy.mdf',
        MOVE 'TSQL2008_Log' TO N'/var/opt/mssql/data/TSQL2008Copy.ldf';
GO

TIPS: 如果我们没有执行第三步的导入CERTIFICATE操作，直接执行 RESTORE DATABASE 会有类似如下的报错：

5、确认恢复的库是否处于TDE加密状态

SELECT DB_NAME(database_id) AS DatabaseName,
    key_algorithm AS [Algorithm],
    key_length AS KeyLength,
    CASE encryption_state
        WHEN 0 THEN 'No database encryption key present, no encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
    END AS EncryptionStateDesc,
    percent_complete AS PercentComplete
FROM sys.dm_database_encryption_keys;
GO

select name,is_encrypted from sys.databases where name='TSQL2008';
GO

6、查询抽验数据

随机找几个TSQL2008库里表，执行查询操作。正常情况下，数据都是可以被查询的。

TDE的关闭

原文 https://juejin.cn/post/7170217154541322276  作者不知道从哪里机翻了文章，有点乱，我这里实验顺带整理了下。

删除TDE是一个简单的过程。如果我们不按照推荐的程序，那么我们在删除它的密钥和证书时将会面临问题。我们按照创建TDE配置时的相反顺序进行操作。要从SQL Server数据库中删除TDE，需要遵循以下的活动顺序。

• l 关闭数据库中的TDE
• l 删除数据库加密密钥
• l 删除证书
• l 删除主密钥

如果你观察删除TDE的过程，你就会知道，与启用TDE的过程相比，我们的活动顺序完全相反。

在启用它时，我们首先创建一个主密钥，然后创建一个证书，再创建一个数据库加密密钥，最后我们在数据库上启用透明数据加密，而我们在这里是按照相反的顺序进行的。

如果你在想，如果我们不按照这个顺序，在没有关闭数据库上的TDE的情况下，试图放弃数据库加密密钥或证书或主密钥，会发生什么？除非你按照这个顺序，否则SQL Server将不允许你删除任何密钥或证书。

如果你在关闭TDE之前试图放弃它们，你将会得到以下错误。

试图在没有关闭TDE的情况下删除主密钥
Msg 15580, Level 16, State 1, Line 3 Cannot drop master key because certificate ‘TDE_DB_Cert’ is encrypted by it.

试图在不删除数据库加密密钥的情况下删除证书
Msg 3716, Level 16, State 15, Line 3 The certificate ‘TDE_DB_Cert’ cannot be dropped because it is bound to one or more database encryption key.

试图放弃数据库加密密钥而不在数据库上禁用 TDE
MSG 33115, Level 16, State 1, Line 5 Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

即使你关闭了数据库的透明数据加密，并且你错过了之后的顺序来删除密钥和证书，那么SQL Server也不会允许你删除该密钥，因为主密钥和证书有依赖关系，它们是相互结合的。我们需要首先删除依赖关系，然后再进行删除过程。上述顺序将使我们能够顺利地删除它们而不出现任何问题。

让我们通过删除TDE和它的组件来清理你的系统，这样我们就可以毫无问题地实施另一个加密方案。

前提条件

一旦你决定从SQL Server数据库中删除TDE，你必须考虑以下几点作为删除前计划的一部分。

• 对主密钥和证书进行备份，并将其保存在一个安全的地方。如果你将来需要借助其旧的备份文件来恢复这个数据库，就会需要这些密钥。
• 如果该证书被多个数据库共享，而你只想从一个数据库中删除TDE，则不要删除该证书。如果你想从TDE中清理出完整的实例，那么你可以继续进行删除。
• 如果你有一个临时删除TDE的要求，那么不要删除它的主密钥和证书。你可以直接从数据库中关闭TDE，然后通过运行ALTER语句打开TDE。
• 如果可能的话，通过删除不需要的数据来减少数据库文件的大小，以减少TDE在移除过程中的扫描时间。

在执行这项工作之前，请确保运行一个完整的数据库备份。

始终在非工作时间执行这项活动，因为TDE删除将在后端启动扫描过程，这将增加数据库系统的负载。

检查SQL服务器实例以验证我们的目标数据库是否启用了TDE。运行下面的T-SQL语句来获得所有加密数据库的详细信息。

1 检查SQL服务器实例以验证我们的目标数据库是否启用了TDE

SELECT 
 DB_Name(database_id) As DBName, 
 encryption_state  -- 状态为3表示处于加密状态
FROM sys.dm_database_encryption_keys
GO

SELECT name, is_encrypted FROM sys.databases;
GO

2 关闭对应的库的TDE加密

USE master;
GO

ALTER DATABASE TSQL2008 SET ENCRYPTION OFF;
GO

注意：

如果你的数据库规模很大，上述命令将花费一些时间，因为在加密或解密过程中，后端将进行TDE扫描过程。这个过程是资源密集型的，所以要在非工作时间计划这项活动。

一旦上述命令成功执行，那么TSQL2008 数据库的加密状态将改变为 未加密，但是tempdb仍然显示为加密的。

3 再次查看加密情况

SELECT name, is_encrypted FROM sys.databases where name in ('TSQL2008','tempdb');

从sys.databases视图看，数据库均处于未加密状态了。

但是，如果查询DMV动态视图：

select db_name(database_id) , * from sys.dm_database_encryption_keys ;

可以看到

1、TSQL2008 已经是未加密的状态

2、tempdb显示仍然是加密状态的

这意味着透明数据加密并没有被完全删除，其相关的数据库加密密钥仍然存在。接下来，我们将删除数据库加密密钥，从数据库中彻底删除TDE。

TIPS：

如果你不想删除数据库主密钥和相关证书，那么还需要重启SQL Server服务才能把tempdb数据库的加密功能也删除。

我已经重新启动了SQL Server实例，然后再次检查了透明数据加密状态。

如下图，可以看到数据库都处于未加密状态了。

4 删除数据库加密秘钥

让我们删除数据库加密密钥，因为我们已经关闭了这个数据库的TDE。如果你没有关闭用户数据库的透明数据加密功能，你将无法删除数据库加密密钥，而且你将被抛出错误，正如我在本文中给出的那样。请确保首先按照上面的步骤进行操作。

一旦你关闭了用户数据库中的TDE，运行下面的T-SQL语句来删除数据库加密密钥。

USE TSQL2008;
GO

DROP DATABASE ENCRYPTION KEY;
GO

现在数据库的加密密钥已经被放弃了。让我们再次检查用户数据库的TDE状态，以确保它在上述DMV输出中是否仍然存在。

USE TSQL2008;
GO

SELECT DB_Name(database_id) As DBName, encryption_state FROM sys.dm_database_encryption_keys
GO

SELECT name, is_encrypted FROM sys.databases where name in ('TSQL2008','tempdb');
GO

我们可以看到，现在这个DMV中没有任何条目。这意味着透明数据加密已经从这个用户数据库中完全删除，但它的一些相关文件仍然存在于主数据库中，也就是主密钥及其证书。

如果你正在使用主密钥和相同的证书来加密任何其他用户数据库，那么你不应该删除它们，而应该让它们保持原状。

但是，如果你想完全删除TDE和它的主密钥/证书，因为你在你的SQL Server实例上没有任何使用TDE加密的数据库，那么你也必须删除它们来清理你的系统。接下来，我将向你展示如何删除它的主密钥和证书来清理SQL Server实例上的透明数据加密组件。

5 从主数据库中删除TDE证书

运行下面的T-SQL语句，删除为加密数据库TDE_DB而创建的TDE证书。

use master;
GO

select * from sys.certificates;
GO

DROP CERTIFICATE DBATEST_ServerCert;

select * from sys.certificates;   可以看到已经找不到DBATEST_ServerCert 这个证书了。 
GO

6 删除主秘钥

TDE中的主密钥是用来保护与TDE加密相关的证书。由于我们已经决定从SQL Server上禁用TDE，并在上面的步骤中删除了相关的证书，所以在SQL Server实例上保留数据库主密钥是没有用的。

让我们通过在主数据库中运行下面的T-SQL语句来删除主密钥。

USE master
GO

DROP MASTER KEY;
GO

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
GO

上面的T-SQL将删除数据库的主密钥，通过这一步，你就完成了透明数据加密的删除过程。

然后，看下tempdb的加密状态，如果还是加密的，需要重启下mssql的进程。

SELECT DB_Name(database_id) As DBName, encryption_state FROM sys.dm_database_encryption_keys
GO

SELECT name, is_encrypted FROM sys.databases where name in ('TSQL2008','tempdb');
GO

现在可以重新启动SQL Server服务，为tempdb数据库创建新的文件，至此TDE的清理全部结束。

最后，你必须立即对你的未加密的数据库进行全面备份，以确保你有一个没有任何密钥或证书的健康备份。

TDE对性能的影响

这这里我没有自己测试，看的是网上基于mssql2008测试报告。具体仅供参考。

https://www.mssqltips.com/sqlservertip/2641/sql-server-transparent-data-encryption-tde-performance-comparison/

https://www.databasejournal.com/ms-sql/performance-testing-sql-2008s-transparent-data-encryption/

在实际应用程序中，启用 TDE 后，性能会下降 3-10%。与往常一样，您应该在自己的环境中进行测试，因为结果可能会因数据库中数据的性质和访问它的应用程序而异。