04.项目四数据加密

上节课知识回顾

1. 数据库备份的三种类型分别是什么？各有什么特点？

💡 提示：完整备份、差异备份、日志备份。完整备份是全部数据的副本；差异备份只备份自上次完整备份以来变化的数据；日志备份记录所有事务日志，可用于恢复到任意时间点。

2. RESTORE 语句中 RECOVERY 和 NORECOVERY 的区别是什么？

💡 提示：RECOVERY 表示恢复完成后数据库立即可用（不可继续恢复后续备份）；NORECOVERY 表示恢复后数据库仍处于还原状态（可以继续恢复后续差异备份或日志备份）。还原序列中除最后一个备份外都使用 NORECOVERY。

3. BULK INSERT 导入数据时，常用哪些关键参数？

💡 提示：FIELDTERMINATOR（字段分隔符）、ROWTERMINATOR（行分隔符）、FIRSTROW（起始行）、DATAFILETYPE（数据文件格式，如 char/widechar/native）。

💡 引入：上节课我们学习了如何保护数据的持久性（备份与还原），但备份文件本身如果被窃取，数据依然会泄露。本节课学习如何让数据即使被偷走也无法阅读——数据加密。加密是数据安全的最后一道防线，在《网络安全法》和《数据安全法》的背景下，掌握数据库加密技术是每个数据库管理员的必备技能。

学习目标

层次	内容
知识	掌握对称加密与非对称加密的原理及区别；理解SQL Server加密层次结构（SMK→DMK→证书→对称密钥→数据）；理解透明数据加密（TDE）的工作机制与适用场景
技能	能使用SQL脚本完成数据库主密钥（DMK）、证书、对称密钥的创建、备份与还原；能使用SQL脚本完成列级加密与解密；能使用SQL脚本配置TDE透明数据加密；能通过存储过程简化解密查询操作
素养	树立”加密是数据安全的最后一道防线”的安全意识；养成证书备份和密钥管理的良好运维习惯；理解合规要求对数据加密的强制规定

重难点提示

类型	重难点	说明
重点	对称加密 vs 非对称加密	两种加密的核心原理、适用场景和性能差异，是理解所有加密操作的基础
重点	SQL Server加密层次结构	SMK→DMK→证书→对称密钥→数据，每一层如何保护下一层，层次关系的逻辑链条
重点	TDE透明数据加密的配置	使用SQL脚本完成配置流程（创建证书→创建DEK→启用加密），理解TDE与列级加密的区别
难点	DEK创建必须在启用TDE之前	DEK创建后才能启用TDE加密，顺序不能颠倒
难点	加密层次结构的理解	每一层密钥被上一层保护，理解这种”套娃”式的保护机制
难点	证书备份与恢复的完整流程	证书丢失=加密数据永久丢失，备份时需同时导出公钥(.cer)和私钥(.pvk)
难点	TDE加密数据库的迁移	需要在目标服务器先还原证书，否则数据库无法附加或还原

任务一加密的基础知识

知识点1：加密分类（对称加密与非对称加密）

概念：

加密（Encryption）是将明文（Plaintext）通过加密算法和密钥转换为密文（Ciphertext）的过程。解密（Decryption）则是将密文还原为明文的逆过程。根据密钥的使用方式，加密分为两大类：

对称加密（Symmetric Encryption）： 加密和解密使用同一把密钥。发送方和接收方必须事先共享这个密钥。对称加密速度快、效率高，适合加密大量数据。常见的对称加密算法有AES、DES、3DES、RC4、RC2等。

AES（Advanced Encryption Standard，高级加密标准）：是美国国家标准与技术研究院（NIST）于2001年发布的标准，取代DES。支持128位、192位、256位密钥长度，AES-256是当前安全性最高的版本。
DES（Data Encryption Standard，数据加密标准）：使用56位密钥，已被证明不够安全，于2005年被正式废弃。
3DES（Triple DES，三重DES）：对数据执行三次DES加密，密钥长度为112位或168位，安全性优于DES但速度更慢，逐步被AES取代。
RC4（Rivest Cipher 4）：流密码算法，密钥长度可变（40-2048位），已被发现存在安全漏洞，SQL Server 2008仍可使用，但建议使用AES替代。
RC2（Rivest Cipher 2）：块密码算法，密钥长度40-128位，安全性有限，SQL Server 2008仍可使用，但建议使用AES替代。

非对称加密（Asymmetric Encryption）： 使用一对密钥——公钥（Public Key）和私钥（Private Key）。公钥可以公开分发，用于加密数据；私钥必须保密，用于解密数据。非对称加密安全性高，但速度远慢于对称加密，通常用于密钥交换和数字签名，不适合加密大量数据。常见的非对称加密算法有RSA、ECC、DSA等。

RSA：由Rivest、Shamir、Adleman三人于1977年提出。安全性基于大整数分解的困难性。常用密钥长度为1024位、2048位、4096位，2048位及以上被认为是安全的。
ECC（Elliptic Curve Cryptography，椭圆曲线密码学）：基于椭圆曲线数学问题，相同安全级别下密钥长度远小于RSA（256位ECC相当于3072位RSA），适用于资源受限环境。
DSA（Digital Signature Algorithm，数字签名算法）：由美国国家标准与技术研究院制定，主要用于数字签名而非加密。

混合加密机制（以HTTPS为例）：

HTTPS通信使用混合加密机制，完美结合了两种加密的优势：

客户端发起连接，服务器将自己的数字证书（包含公钥） 发送给客户端。
客户端验证证书合法性后，用服务器的公钥加密一个随机生成的会话密钥（对称密钥），发送给服务器。
服务器用自己的私钥解密，得到会话密钥。
此后双方使用会话密钥进行对称加密通信，速度极快。

对称加密算法对比表：

算法	密钥长度	安全性	速度	推荐状态
AES-128	128位	高	快	推荐
AES-192	192位	很高	快	推荐
AES-256	256位	极高	快	强烈推荐
DES	56位	已被破解	较快	已废弃
3DES	112/168位	中	慢（3次DES）	逐步淘汰
RC4	40-2048位	存在漏洞	快	不推荐使用
RC2	40-128位	低	一般	不推荐使用

非对称加密算法对比表：

算法	密钥长度	典型应用	特点
RSA-1024	1024位	密钥交换、数字签名	已不推荐，安全性不足
RSA-2048	2048位	TLS/SSL、数字签名	当前主流标准
RSA-4096	4096位	高安全要求场景	安全性极高，速度更慢
ECC-256	256位	移动设备、IoT	相当于3072位RSA
ECC-384	384位	高安全要求	相当于7680位RSA
DSA	1024-3072位	数字签名	仅用于签名，不能加密

对称加密 vs 非对称加密对比表：

对比项	对称加密	非对称加密
密钥数量	一个密钥（加密和解密共用）	一对密钥（公钥+私钥）
加密速度	非常快（适合大量数据）	非常慢（适合小数据量）
安全性	依赖密钥保密	依赖私钥保密，公钥可公开
密钥分发问题	严重（如何安全传输密钥）	无此问题（公钥可公开分发）
典型用途	加密文件、数据库数据	密钥交换、数字签名、身份认证
常用算法	AES、3DES、RC4	RSA、ECC、DSA
SQL Server支持	AES_128/192/256、DES、3DES、RC2、RC4	RSA_512/1024/2048/4096

💡 类比理解：对称加密像共用一把钥匙的信箱——你和朋友各有一把相同的钥匙，都能开锁，但钥匙在传递过程中可能被截获。非对称加密像投币箱和专用钥匙——投币箱的口（公钥）是敞开的，谁都能投信，但只有持有专用钥匙（私钥）的人才能打开。混合加密则是：先用非对称加密安全地”交换”一把对称密钥，再用这把对称密钥快速加密实际数据。

⚠️ 重要提醒：DES已被破解，3DES正在逐步淘汰，当前国际标准是AES。国密标准是SM4（对称）和SM2（非对称）。在实际生产环境中，务必使用AES-256或以上强度的加密算法。

💡 知识点1小结：到这里，我们已经了解了加密技术的两大阵营——对称加密（速度快，适合大量数据）和非对称加密（安全性高，适合密钥交换）。在实际应用中，两者往往配合使用，如HTTPS的混合加密机制。但加密理论和数据库实践之间还有一段距离——SQL Server是如何组织和管理这些加密对象的？让我们进入实验1，先从SSMS图形界面中观察SQL Server的加密对象。

实验1：了解SQL Server加密对象（SSMS图形界面）

实验目的： 通过SSMS图形界面查看SQL Server中已有的加密对象，了解加密层次结构。

实验步骤：

打开SSMS，连接到SQL Server实例。
在对象资源管理器中，依次展开以下节点，观察加密层次结构：

SQL Server实例
  └── 安全性（Security）
        └── 证书（Certificates）        ← 服务级别证书
  └── 数据库（Databases）
        └── 某个数据库
              └── 安全性（Security）
                    ├── 数据库主密钥（Database Master Key）  ← DMK
                    ├── 证书（Certificates）                ← 数据库级证书
                    ├── 非对称密钥（Asymmetric Keys）
                    └── 对称密钥（Symmetric Keys）           ← 对称密钥

在每个节点上右键 → 属性，查看有哪些加密对象。
记录观察结果（注意：SQL Server 2008中”数据库主密钥”节点可能不会显示，需用SQL查询验证）：
- 当前实例有哪些服务级证书？
- 当前数据库是否已创建数据库主密钥（DMK）？可通过执行 SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101; 验证
- 当前数据库有哪些证书、对称密钥？

💡 注意：如果是全新安装的SQL Server，这些节点可能是空的。后续实验将逐步创建这些对象。

💡 实验1小结与过渡：通过实验1，我们从SSMS中观察到了加密对象呈现清晰的层级结构——实例级有证书，数据库级有DMK、证书、对称密钥等。你可能会好奇：为什么SQL Server要设计这么多层？每一层之间到底是什么关系？接下来我们进入知识点2，深入理解SQL Server的加密层次结构。

知识点2：SQL Server加密层次结构

概念：

SQL Server采用多层加密体系，每一层密钥由上一层保护，形成”套娃”式的安全保护链：

┌─────────────────────────────────────────────────┐
│         Windows DPAPI（数据保护API）              │ ← 操作系统级别
│         由Windows操作系统自动管理                   │
└──────────────────┬──────────────────────────────┘
                   │ 保护
┌──────────────────▼──────────────────────────────┐
│      SMK（服务主密钥，Service Master Key）         │ ← 实例级别
│      每个SQL Server实例只有一个SMK                  │
│      由DPAPI自动加密保护                           │
└──────────────────┬──────────────────────────────┘
                   │ 加密保护
┌──────────────────▼──────────────────────────────┐
│    DMK（数据库主密钥，Database Master Key）         │ ← 数据库级别
│    每个需要加密的数据库各有一个DMK                    │
│    由SMK加密保护（默认）                            │
└──────────────────┬──────────────────────────────┘
                   │ 加密保护
┌──────────────────▼──────────────────────────────┐
│    证书（Certificate）/ 非对称密钥                  │ ← 数据库级别
│    用于保护对称密钥或进行非对称加密                   │
└──────────────────┬──────────────────────────────┘
                   │ 加密保护
┌──────────────────▼──────────────────────────────┐
│      对称密钥（Symmetric Key）                    │ ← 数据库级别
│      实际用于加密/解密数据的密钥                     │
└──────────────────┬──────────────────────────────┘
                   │ 加密/解密
┌──────────────────▼──────────────────────────────┐
│              数据（Data）                         │ ← 最终被保护的对象
│      列中的敏感数据（如身份证号、银行卡号等）          │
└─────────────────────────────────────────────────┘

各层详解：

1. SMK（Service Master Key，服务主密钥）：

是整个SQL Server实例加密体系的根密钥
每个SQL Server实例只有一个SMK
SQL Server安装时自动生成
由Windows DPAPI自动加密保护（与Windows账户绑定）
不能手动创建，但可以备份

2. DMK（Database Master Key，数据库主密钥）：

每个需要加密的数据库各创建一个DMK
必须手动创建（使用SQL语句）
默认由SMK加密（也可由密码保护）
是数据库级别加密的基础

3. 证书（Certificate）：

包含公钥和私钥的非对称密钥对
用于保护对称密钥或直接加密小量数据
可以备份和还原（导出为.cer公钥文件和.pvk私钥文件）

4. 对称密钥（Symmetric Key）：

实际执行数据加密/解密的密钥
由证书或非对称密钥保护
加密/解密速度快，适合大量数据

💡 类比理解：想象一个银行的安全系统——DPAPI是银行大楼的门禁系统，SMK是金库的主钥匙，DMK是每个保险柜的钥匙，证书是保险柜钥匙的授权书，对称密钥是实际锁住钱的锁，数据就是钱本身。要打开保险柜取钱，需要依次通过每一层验证。

⚠️ 重要：如果SMK损坏或丢失，所有由它保护的DMK都无法自动解密，所有加密数据将无法访问！因此必须定期备份SMK。

💡 知识点2小结与过渡：通过知识点2，我们理解了SQL Server加密层次的核心设计思想——每一层密钥由上一层保护，形成”套娃”式的安全链条。DPAPI保护SMK，SMK保护DMK，DMK保护证书，证书保护对称密钥，对称密钥最终加密数据。理解了理论之后，接下来进入实验2，我们亲自动手创建这套完整的密钥体系。

实验2：创建完整的密钥体系（SQL脚本）

实验目的： 使用SQL脚本完成数据库主密钥（DMK）、证书、对称密钥的创建，建立完整的加密体系。

实验步骤：

第一步：创建数据库主密钥（DMK）

注意：SQL Server 2008的SSMS没有图形界面选项来创建DMK，只能通过SQL语句创建。请在SSMS中打开”新建查询”，输入以下语句：

在SSMS中，展开数据库 → 找到目标数据库（如 TestDB）
右键目标数据库 → 新建查询
执行以下SQL语句创建DMK：

USE TestDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!';
GO

创建成功后，使用以下SQL查询验证DMK是否已创建成功（注意：SQL Server 2008的SSMS图形界面中，“数据库主密钥”节点可能不会自动显示，需使用SQL验证）：

USE TestDB;
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101;

💡 查询返回结果即表示DMK创建成功。

第二步：创建证书（SQL方式）

在SSMS中，继续在新建查询中执行以下SQL创建证书：

USE TestDB;
GO

-- 创建证书
CREATE CERTIFICATE MyCert_DataEncryption
WITH SUBJECT = 'Data Encryption Certificate';
GO

执行以下SQL验证证书是否创建成功：

USE TestDB;
SELECT name, subject, start_date, expiry_date FROM sys.certificates;

💡 查询结果中应能看到 MyCert_DataEncryption 证书。

第三步：创建对称密钥（SQL方式）

在SSMS中，继续在新建查询中执行以下SQL创建对称密钥：

USE TestDB;
GO

-- 创建对称密钥
CREATE SYMMETRIC KEY MySymKey_DataEncryption
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyCert_DataEncryption;
GO

执行以下SQL验证对称密钥是否创建成功：

USE TestDB;
SELECT name, algorithm_desc, key_length FROM sys.symmetric_keys WHERE symmetric_key_id > 101;

💡 查询结果中应能看到 MySymKey_DataEncryption 对称密钥，算法为 AES_256。

💡 验证：创建完成后，通过以上SQL查询验证加密体系是否完整。这就是一个完整的加密密钥体系：DMK → 证书 → 对称密钥。

💡 任务一总结：通过任务一，我们完成了加密知识从理论到实践的完整跨越：

知识点1奠定了理论基础——理解了对称加密与非对称加密的原理、区别和混合使用方式

实验1让我们从SSMS中直观观察到SQL Server的加密对象层级

知识点2揭示了SQL Server加密层次的设计思想——“套娃”式的多层保护机制

实验2让我们亲手搭建了一套完整的加密密钥体系（DMK → 证书 → 对称密钥）

密钥体系已经搭建完成，下一步就是利用它来做真正的数据加密——对表中特定列的敏感数据进行加密保护。进入任务二！

任务二数据列加密

知识点3：列级加密（Cell-level Encryption）

概念：

列级加密是指对表中的特定列进行加密，使得只有拥有正确密钥的用户才能查看原始数据。这是保护敏感数据（如身份证号、银行卡号、手机号等）的常用手段。

工作原理：

原始数据  →  EncryptByKey()  →  密文（存储在表中）
密文数据  →  DecryptByKey()  →  原始数据（授权用户可查看）

关键函数：

函数	作用	示例
`EncryptByKey(Key_GUID, '明文')`	使用对称密钥加密数据	`EncryptByKey(Key_GUID('MySymKey'), '123456')`
`DecryptByKey(密文列)`	使用对称密钥解密数据	`CONVERT(varchar, DecryptByKey(IDCard))`
`Key_GUID('密钥名')`	获取对称密钥的GUID	`Key_GUID('MySymKey_DataEncryption')`
`OPEN SYMMETRIC KEY`	打开对称密钥（使用前必须打开）	`OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert`
`CLOSE SYMMETRIC KEY`	关闭对称密钥	`CLOSE SYMMETRIC KEY MySymKey`
`CLOSE ALL SYMMETRIC KEYS`	关闭所有打开的对称密钥	`CLOSE ALL SYMMETRIC KEYS`

注意事项：

使用对称密钥加密前，必须先执行 OPEN SYMMETRIC KEY 打开密钥
加密后的数据类型为 varbinary，所以加密列的数据类型需要设为 varbinary
每次加密同一明文，由于使用了初始化向量(IV)，得到的密文可能不同（这是正常的安全特性）
解密时需要将结果转换为原始数据类型

💡 存储过程简化查询：每次查询都要打开密钥、解密、关闭密钥，操作繁琐。可以通过创建存储过程将这些步骤封装起来，用户只需调用存储过程即可。

💡 知识点3小结与过渡：列级加密的核心流程可以概括为三步：打开密钥 → 加密/解密 → 关闭密钥。加密使用 EncryptByKey，解密使用 DecryptByKey，加密前必须先打开对称密钥。理解了原理之后，我们进入实验3，亲自动手对员工表中的身份证号和银行卡号进行加密保护。

实验3：数据列加密与解密操作

实验目的： 对表中的敏感列进行加密，理解列级加密的完整流程。

实验步骤：

第一步：准备测试数据

在SSMS中，右键目标数据库 → 新建查询
执行以下SQL创建测试表并插入数据：

USE TestDB;
GO

-- 创建员工表
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName NVARCHAR(50),
    IDCard VARBINARY(256),      -- 加密后的身份证号
    BankCard VARBINARY(256)      -- 加密后的银行卡号
);
GO

-- 插入测试数据
INSERT INTO Employees (EmpID, EmpName, IDCard, BankCard)
VALUES (1, N'张三', NULL, NULL),
       (2, N'李四', NULL, NULL),
       (3, N'王五', NULL, NULL);
GO

查询确认数据已插入：SELECT * FROM Employees;

第二步：加密敏感列数据

💡 前提：执行本实验前，需确保已完成实验1-2，即已创建完整的密钥体系（DMK → 证书 → 对称密钥）。如果尚未创建，请先完成实验1-2。

在SSMS中继续执行以下SQL：

USE TestDB;
GO

-- 打开对称密钥
OPEN SYMMETRIC KEY MySymKey_DataEncryption
DECRYPTION BY CERTIFICATE MyCert_DataEncryption;
GO

-- 加密身份证号和银行卡号
UPDATE Employees
SET IDCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), N'110101199001011234'),
    BankCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), '6222021234567890123')
WHERE EmpID = 1;

UPDATE Employees
SET IDCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), N'310101199203034567'),
    BankCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), '6222029876543210987')
WHERE EmpID = 2;

UPDATE Employees
SET IDCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), N'440101199505058901'),
    BankCard = EncryptByKey(Key_GUID('MySymKey_DataEncryption'), '6222025555666677778')
WHERE EmpID = 3;
GO

-- 关闭对称密钥
CLOSE SYMMETRIC KEY MySymKey_DataEncryption;
GO

查询验证加密结果：

SELECT EmpID, EmpName,
       IDCard AS '加密后的身份证号',
       BankCard AS '加密后的银行卡号'
FROM Employees;
GO

💡 观察：此时身份证号和银行卡号显示为一堆不可读的乱码（二进制数据），说明加密成功。

第三步：解密查看原始数据

执行以下SQL解密并查看数据：

USE TestDB;
GO

-- 打开对称密钥
OPEN SYMMETRIC KEY MySymKey_DataEncryption
DECRYPTION BY CERTIFICATE MyCert_DataEncryption;
GO

-- 解密查询
SELECT EmpID, EmpName,
       CONVERT(NVARCHAR(18), DecryptByKey(IDCard)) AS '身份证号',
       CONVERT(VARCHAR(19), DecryptByKey(BankCard)) AS '银行卡号'
FROM Employees;
GO

-- 关闭对称密钥
CLOSE SYMMETRIC KEY MySymKey_DataEncryption;
GO

💡 观察：此时可以看到明文的身份证号和银行卡号，说明解密成功。

💡 任务二总结：通过任务二，我们完成了从”密钥搭建”到”数据加密”的关键一步：

知识点3介绍了列级加密的原理和关键函数（EncryptByKey、DecryptByKey、OPEN/CLOSE SYMMETRIC KEY）

实验3让我们亲手对员工表中的身份证号和银行卡号进行了加密和解密操作，体验了完整的”打开密钥→加密→关闭密钥”流程

数据已经加密保护了，但一个新的、极其重要的问题随之而来：如果密钥或证书丢失了怎么办？ 答案是——加密数据将永久无法解密！证书丢失等于数据丢失。因此，密钥的备份与管理是加密运维中最不可忽视的一环。进入任务三！

任务三多级密钥管理与证书备份

知识点4：密钥与证书的备份和还原

概念：

密钥和证书的备份是数据库加密运维中最重要的操作之一。如果密钥或证书丢失，所有使用它们加密的数据将永久无法解密！

备份策略：

备份对象	备份方式	备份内容	重要性
服务主密钥（SMK）	`BACKUP SERVICE MASTER KEY`	整个SMK	极高（丢失后所有DMK失效）
数据库主密钥（DMK）	`BACKUP MASTER KEY`	整个DMK	极高（丢失后所有证书失效）
证书	`BACKUP CERTIFICATE`	公钥(.cer) + 私钥(.pvk)	极高（丢失后对称密钥失效）

证书备份的注意事项：

备份证书时，需要同时导出公钥和私钥
公钥文件（.cer）：包含证书信息，可以公开
私钥文件（.pvk）：必须严格保密
导出私钥时需要设置密码保护
公钥和私钥需要分开存放

⚠️ 重要提醒：证书丢失 = 加密数据永久丢失！没有备份证书，就无法还原加密的数据。务必在创建证书后立即备份。

💡 知识点4小结与过渡：密钥备份的核心可以总结为一条铁律——没有备份就没有安全。备份对象分为三个层级：SMK（实例级）、DMK（数据库级）、证书（加密对象级），每一层都需要单独备份。特别要注意证书备份需要同时导出公钥(.cer)和私钥(.pvk)两个文件。接下来进入实验4，我们动手完成证书的备份和还原操作。

实验4：备份和还原证书（SQL方式）

实验目的： 掌握证书的备份与还原操作，理解密钥管理的重要性。

实验步骤：

第一步：备份证书（SQL方式）

在SSMS中，连接到SQL Server实例
右键目标数据库 → 新建查询
执行以下SQL语句备份证书：

USE TestDB;
GO

-- 备份证书到文件（同时导出公钥和私钥）
BACKUP CERTIFICATE MyCert_DataEncryption
TO FILE = 'C:\SQLbak\MyCert_DataEncryption.cer'      -- 公钥文件
WITH PRIVATE KEY (
    FILE = 'C:\SQLbak\MyCert_DataEncryption.pvk',     -- 私钥文件
    ENCRYPTION BY PASSWORD = 'CertBackupPassword456!'  -- 私钥保护密码
);
GO

💡 易错提示：备份和还原证书的关键字不同！备份用 ENCRYPTION BY PASSWORD，还原用 DECRYPTION BY PASSWORD。如果还原时错写为 ENCRYPTION，会报错”证书或私钥文件格式无效”。

在Windows资源管理器中，确认 C:\SQLbak\ 目录下生成了两个文件：
- MyCert_DataEncryption.cer（公钥文件）
- MyCert_DataEncryption.pvk（私钥文件）

第二步：通过SQL查看证书信息（SQL验证）

在SSMS中打开新建查询，执行以下SQL查看证书信息：

USE TestDB;
GO

SELECT name AS '证书名称',
       issuer_name AS '颁发者',
       start_date AS '生效日期',
       expiry_date AS '过期日期'
FROM sys.certificates
WHERE name = 'MyCert_DataEncryption';
GO

💡 查询结果会显示证书的名称、颁发者、有效期和密钥长度等信息。

第三步：还原证书（SQL方式）

💡 以下步骤用于演示：先删除证书，再用备份文件还原。

在SSMS中打开新建查询，执行以下SQL删除证书：(删除证书之前需要先删除由证书保护的对称密钥)

USE TestDB;
GO
DROP CERTIFICATE MyCert_DataEncryption;
GO

在SSMS中执行以下SQL验证证书已被删除：SELECT name FROM sys.certificates;（结果中应无 MyCert_DataEncryption）
执行以下SQL还原证书：

USE TestDB;
GO

-- 从备份文件还原证书
CREATE CERTIFICATE MyCert_DataEncryption
FROM FILE = 'C:\SQLbak\MyCert_DataEncryption.cer'      -- 公钥文件
WITH PRIVATE KEY (
    FILE = 'C:\SQLbak\MyCert_DataEncryption.pvk',     -- 私钥文件
    DECRYPTION BY PASSWORD = 'CertBackupPassword456!'  -- 还原时使用的密码（与备份时相同）
);
GO

再次查询 sys.certificates 确认证书已还原

💡 验证要点：还原证书后，再次执行第二步的SQL查询，确认证书信息与删除前一致。

备份服务主密钥SMK和数据库主密钥DMK：

-- 备份服务主密钥（SMK）
BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQLbak\SMK.bak';
GO

-- 备份数据库主密钥（DMK）
BACKUP MASTER KEY TO FILE = 'C:\SQLbak\DMK.bak'
    ENCRYPTION BY PASSWORD = 'DMKBackupPassword789!';
GO

💡 任务三总结：通过任务三，我们掌握了加密运维中至关重要的”保险箱钥匙管理”技能：

知识点4明确了备份的重要性——证书丢失等于数据永久丢失，每个层级的密钥都必须备份

实验4让我们亲手完成了证书的完整备份与还原流程，并掌握了一个关键易错点：备份用 ENCRYPTION BY PASSWORD，还原用 DECRYPTION BY PASSWORD

至此，我们已经掌握了列级加密（精确到字段）和密钥备份管理。但列级加密有一个明显缺点——每条查询都需要手动调用加密/解密函数，代码侵入性强。有没有一种方式能对整个数据库自动加密，而应用程序完全无感知呢？答案是——透明数据加密（TDE）。进入任务四！

任务四透明数据加密（TDE）

知识点5：透明数据加密（TDE）

概念：

透明数据加密（Transparent Data Encryption，TDE）是SQL Server提供的一种数据库级别的加密技术。它对整个数据库的数据文件和日志文件进行加密，加密和解密过程由数据库引擎自动完成，对应用程序和用户完全透明（无需修改任何代码或查询语句）。

工作原理：

应用程序 → 发送普通SQL查询 → SQL Server引擎
                                      │
                                      ├─ 读取数据文件时自动解密
                                      ├─ 写入数据文件时自动加密
                                      │
                                      ▼
                              加密的数据文件(.mdf) + 加密的日志文件(.ldf)

TDE与列级加密的对比：

对比项	TDE透明数据加密	列级加密
加密范围	整个数据库（所有表、所有列）	指定列
对应用透明	完全透明（无需改代码）	需要使用加密/解密函数
性能影响	极小（约2-5%开销）	较大（每次查询都要加解密）
加密粒度	粗（文件级别）	细（单个列级别）
防护场景	防止数据库文件被窃取	防止未授权用户查看敏感数据
适用场景	笔记本电脑防丢、数据库文件备份防泄露	保护特定敏感字段（身份证号等）
是否可同时使用	可以同时使用	可以同时使用

TDE的加密层次：

数据库加密密钥（DEK）
    ↑ 由证书保护（存储在master数据库中）
    ↑
TDE证书（必须存储在master数据库中）
    ↑ 由DMK保护
    ↑
master数据库的DMK
    ↑ 由SMK保护

TDE开启的完整逻辑链：

理解TDE的工作原理，关键在于搞清楚”为什么要按这个顺序操作”：

第一步：USE master; CREATE MASTER KEY  →  创建master的DMK
    ↓
    DMK由SMK自动加密保护，SMK由Windows DPAPI保护
    这是整个TDE链的基础，没有DMK就无法在master中创建证书
    ↓
第二步：CREATE CERTIFICATE MyCert_TDE  →  创建TDE证书（在master中）
    ↓
    证书由master的DMK加密保护
    证书是非对称密钥（公钥+私钥），加密速度慢，不适合直接加密数据文件
    它的作用是保护第三步的DEK
    ↓
第三步：CREATE DATABASE ENCRYPTION KEY  →  创建DEK（在用户数据库中）
    ↓
    DEK由TDE证书加密保护
    DEK是对称密钥（AES-256），加密速度快，才是实际加密数据文件的密钥
    为什么要多此一举？因为非对称加密太慢，直接用证书加密整个数据库不现实
    所以用证书保护DEK（只做一次），再由DEK加密数据（持续进行）
    ↓
第四步：ALTER DATABASE SET ENCRYPTION ON  →  启用TDE加密
    ↓
    SQL Server使用DEK对数据文件(.mdf)和日志文件(.ldf)进行加密
    同时自动加密tempdb系统数据库（防止临时数据泄露）
    此后所有读写操作自动加解密，应用程序完全无感知

💡 核心要点：TDE证书是”锁匠”，DEK是”锁”。锁匠（证书）制造并保护锁（DEK），锁（DEK）实际锁住保险柜（数据文件）。这就是为什么必须先创建证书再创建DEK，顺序不能颠倒。迁移时也必须先在新服务器还原证书（锁匠），才能解密DEK（开锁），从而访问数据文件（打开保险柜）。

注意事项：

TDE证书必须创建在 master 数据库中（TDE仅在SQL Server 2008 Enterprise Edition中可用）
启用TDE后，数据库的备份文件也会被加密
TDE不会加密内存中的数据，也不会加密客户端与服务器之间的网络传输
要迁移TDE加密的数据库，必须先在目标服务器还原证书

⚠️ 重要：TDE加密的数据库备份后，在没有证书的其他服务器上无法还原！迁移前必须先备份并还原TDE证书。

💡 知识点5小结与过渡：TDE的核心优势在于”透明”二字——加密和解密由数据库引擎自动完成，应用程序无需任何改动。它与列级加密是互补关系：TDE保护数据库文件（防窃取），列级加密保护敏感字段（防未授权访问），两者可以同时使用。TDE的配置遵循固定的三步流程：创建证书 → 创建DEK → 启用加密。注意顺序不能颠倒！接下来进入实验5-6，我们亲手配置TDE并模拟TDE数据库的迁移场景。

实验5：配置TDE透明数据加密（SQL脚本）

实验目的： 使用SQL脚本为数据库启用TDE加密，理解透明加密的工作方式。

实验步骤：

第一步：在master数据库中创建TDE证书（SQL方式）

注意：TDE证书必须创建在master数据库中。TDE功能仅在SQL Server 2008 Enterprise Edition（企业版）中可用，Standard Edition不支持。

在SSMS中，打开新建查询，选择 master 数据库
执行以下SQL创建TDE证书：

USE master;
GO

-- 确保master数据库已有DMK，如果没有则创建
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterDMKPassword123!';
END;
GO

-- 在master数据库中创建TDE证书
CREATE CERTIFICATE MyCert_TDE
WITH SUBJECT = 'TDE Certificate for TestDB';
GO

执行以下SQL验证证书创建成功：

USE master;
SELECT name, subject FROM sys.certificates WHERE name = 'MyCert_TDE';

第二步：为数据库创建数据库加密密钥（DEK）（SQL方式）

在SSMS中打开新建查询，执行以下SQL创建DEK：

USE TestDB;
GO

-- 创建数据库加密密钥（DEK）
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCert_TDE;
GO

💡 注意：创建DEK时，SQL Server会自动在数据库中创建一个由TDE证书保护的数据库加密密钥（DEK）。这个过程需要访问master数据库中的证书。如果执行报错”找不到证书 MyCert_TDE”，请确认证书已在 master 数据库中创建成功。

第三步：启用TDE加密（SQL方式）

在SSMS中打开新建查询，执行以下SQL启用TDE：

USE TestDB;
GO

-- 启用TDE透明数据加密
ALTER DATABASE TestDB
SET ENCRYPTION ON;
GO

💡 注意：启用TDE后，SQL Server会在后台自动对数据库文件进行加密，加密过程可能需要一些时间。期间数据库正常可用。可通过第四步的SQL查询监控加密状态（encryption_state 显示为 1 表示加密完成）。

第四步：验证TDE加密效果

在SSMS中查询数据，确认查询不受影响（透明）：

USE TestDB;
GO
SELECT EmpID, EmpName FROM Employees;
GO

💡 观察：查询结果与之前完全一样，说明TDE对用户完全透明。

查看TDE加密状态：

-- 查看数据库加密状态
SELECT DB_NAME(database_id) AS '数据库名',
       encryption_state AS '加密状态',
       key_algorithm AS '加密算法',
       key_length AS '密钥长度'
FROM sys.dm_database_encryption_keys;
GO

💡 加密状态说明：0=无加密密钥，1=未加密（有DEK但未启用），2=加密中，3=已加密，4=密钥更改中，5=解密中。

第五步：备份TDE证书（必须操作！）

在SSMS中打开新建查询，执行以下SQL：

USE master;
GO

-- 备份TDE证书（必须备份，否则数据库无法迁移）
BACKUP CERTIFICATE MyCert_TDE
TO FILE = 'C:\SQLbak\MyCert_TDE.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SQLbak\MyCert_TDE.pvk',
    ENCRYPTION BY PASSWORD = 'TDECertBackupPassword123!'
);
GO

确认备份文件已生成

⚠️ 重要提醒：TDE证书丢失 = 数据库无法还原/附加！务必在启用TDE后立即备份证书。

实验6：模拟TDE加密数据库的迁移（单机模拟）

实验目的： 在同一台服务器上模拟TDE加密数据库的迁移流程，理解TDE证书在迁移中的关键作用。

💡 实验说明：在生产环境中，TDE加密数据库的迁移需要源服务器和目标服务器两台机器。本实验在单机上通过”备份→删除→重建证书→还原”的流程模拟迁移过程，帮助大家理解迁移的核心步骤。实际生产中，步骤2和步骤3在目标服务器上执行即可。

实验步骤：

第一步：备份TDE加密的数据库

在SSMS中，右键 TestDB → 任务 → 备份…
在”备份数据库”对话框中：
- 备份类型：完整
- 目标：选择”磁盘”，设置备份路径为 C:\SQLbak\TestDB_TDE.bak
点击确定，等待备份完成

💡 注意：TDE加密数据库的备份文件本身也是加密的，没有证书无法还原。

第二步：关闭TDE加密并清理当前环境

💡 模拟迁移场景：假设我们要把数据库从”旧服务器”迁移到”新服务器”，现在模拟新服务器上没有TDE证书的情况——先删除TDE证书，再尝试还原数据库，看看会发生什么。

在SSMS中打开新建查询，执行以下SQL关闭TDE加密：

USE TestDB;
GO

-- 关闭TDE加密
ALTER DATABASE TestDB
SET ENCRYPTION OFF;
GO

确认加密已关闭（encryption_state 应为 0）：

SELECT DB_NAME(database_id) AS '数据库名',
       encryption_state AS '加密状态'
FROM sys.dm_database_encryption_keys;
GO

删除数据库加密密钥（DEK）：

USE TestDB;
GO
DROP DATABASE ENCRYPTION KEY;
GO

删除TDE证书（模拟新服务器上没有证书的情况）：

USE master;
GO
DROP CERTIFICATE MyCert_TDE;
GO

验证证书已删除：

USE master;
SELECT name FROM sys.certificates WHERE name = 'MyCert_TDE';
GO

💡 此时master数据库中已没有MyCert_TDE证书，模拟了一台”全新”的目标服务器。

第三步：在不还原证书的情况下尝试还原数据库（演示失败）

先删除当前数据库，为还原腾出位置：

USE master;
GO
DROP DATABASE TestDB;
GO

在SSMS中，右键 数据库 → 还原数据库…
在”还原数据库”对话框中：
- 设备：选择 C:\SQLbak\TestDB_TDE.bak
- 目标数据库：输入 TestDB
点击确定

⚠️ 观察：还原会失败！因为备份文件中的数据库是TDE加密的，需要先还原TDE证书才能还原数据库。记住这个报错信息，这就是为什么TDE证书备份如此重要。

第四步：先还原TDE证书，再还原数据库（正确流程）

在SSMS中打开新建查询，先还原TDE证书：

USE master;
GO

-- 确保master数据库有DMK
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterDMKPassword123!';
END;
GO

-- 从备份文件还原TDE证书
CREATE CERTIFICATE MyCert_TDE
FROM FILE = 'C:\SQLbak\MyCert_TDE.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SQLbak\MyCert_TDE.pvk',
    DECRYPTION BY PASSWORD = 'TDECertBackupPassword123!'
);
GO

验证证书已还原：

USE master;
SELECT name, subject FROM sys.certificates WHERE name = 'MyCert_TDE';
GO

在SSMS中，右键 数据库 → 还原数据库… ，再次尝试还原：
- 设备：选择 C:\SQLbak\TestDB_TDE.bak
- 目标数据库：输入 TestDB
点击确定，等待还原完成

💡 观察：这次还原成功了！因为TDE证书已经提前还原到master数据库中。

还原完成后，查询验证数据正常：

USE TestDB;
GO
SELECT EmpID, EmpName FROM Employees;
GO

💡 实验6总结：通过这个单机模拟实验，我们完整体验了TDE数据库迁移的核心流程：

备份文件是加密的，没有TDE证书无法还原

还原顺序不能颠倒：先还原证书（CREATE CERTIFICATE ... FROM FILE），再还原数据库

在实际生产环境中，迁移前务必确认目标服务器已提前还原了TDE证书

知识点6：加密技术的综合比较与选型

加密方案选型指南：

场景	推荐方案	原因
防止数据库文件被窃取	TDE	对应用透明，性能影响小
保护特定敏感字段	列级加密	精确控制哪些字段加密
防止网络传输被窃听	SSL/TLS加密连接	加密客户端到服务器的通信
笔记本电脑防丢	TDE + BitLocker	双重保护
合规要求（等保三级）	TDE + 列级加密	同时满足文件和字段级要求
备份文件防泄露	TDE	自动加密备份文件

加密最佳实践：

密钥管理：所有密钥和证书必须定期备份，备份文件与数据文件分开存放
密码策略：密钥密码使用强密码（至少12位，包含大小写字母、数字和特殊字符）
最小权限原则：只有需要使用加密数据的用户才授予解密权限
分层防护：TDE + 列级加密组合使用，实现多层保护
定期轮换：定期更换对称密钥和证书（建议每年至少一次）
监控审计：对加密相关的操作进行审计记录

💡 知识点6小结：加密方案没有”万能钥0.

匙”，关键在于理解不同方案的适用场景。TDE擅长文件级保护（防数据库文件和备份文件被窃取），列级加密擅长字段级保护（防未授权用户查看敏感数据）。在生产环境中，两者往往组合使用，实现”文件+字段”的双重防护。记住加密运维的黄金法则：备份、备份、再备份——没有备份的加密，就是给自己挖坑。

本项目总结

知识体系回顾：

数据加密
├── 基础知识
│   ├── 对称加密（AES、DES、3DES）
│   ├── 非对称加密（RSA、ECC）
│   └── 混合加密机制（HTTPS）
├── SQL Server加密层次
│   ├── DPAPI → SMK → DMK → 证书 → 对称密钥 → 数据
│   └── 密钥管理（创建、备份、还原）
├── 列级加密
│   ├── EncryptByKey / DecryptByKey
│   └── 存储过程封装
└── TDE透明数据加密
    ├── 配置流程（证书 → DEK → 启用）
    ├── 加密效果验证
    └── 数据库迁移（证书备份与还原）

核心要点：

序号	要点	关键词
1	对称加密速度快，适合大量数据；非对称加密安全但慢	AES vs RSA
2	SQL Server加密是”套娃”结构，每层保护下一层	SMK → DMK → 证书 → 密钥 → 数据
3	证书丢失 = 数据永久丢失，必须立即备份	备份！备份！备份！
4	列级加密需要修改查询（打开密钥→解密→关闭密钥）	EncryptByKey / DecryptByKey
5	TDE对应用透明，加密整个数据库文件	透明、文件级、自动
6	TDE证书在master数据库中，迁移前必须先还原证书	master、迁移流程
7	TDE和列级加密可以组合使用	分层防护

💡 安全意识：加密是数据安全的最后一道防线。在《网络安全法》《数据安全法》《个人信息保护法》的背景下，数据库加密已经从”建议”变成了”合规要求”。作为数据库管理员，掌握加密技术不仅是技术能力，更是法律义务。请记住：未加密的敏感数据，就是等着泄露的数据。

04.项目四 数据加密

04.项目四 数据加密

04.项目四 数据加密

04.项目四 数据加密

上节课知识回顾

学习目标

重难点提示

任务一 加密的基础知识

知识点1：加密分类（对称加密与非对称加密）

实验1：了解SQL Server加密对象（SSMS图形界面）

知识点2：SQL Server加密层次结构

实验2：创建完整的密钥体系（SQL脚本）

任务二 数据列加密

知识点3：列级加密（Cell-level Encryption）

实验3：数据列加密与解密操作

任务三 多级密钥管理与证书备份

知识点4：密钥与证书的备份和还原

实验4：备份和还原证书（SQL方式）

任务四 透明数据加密（TDE）

知识点5：透明数据加密（TDE）

实验5：配置TDE透明数据加密（SQL脚本）

实验6：模拟TDE加密数据库的迁移（单机模拟）

知识点6：加密技术的综合比较与选型

本项目总结

04.项目四数据加密

04.项目四数据加密

04.项目四数据加密

04.项目四数据加密

任务一加密的基础知识

任务二数据列加密

任务三多级密钥管理与证书备份

任务四透明数据加密（TDE）