SQL Server 加密案例解析

一、概述

加密是一种安全措施，有时候甚至是法律要求。作为攻破Windows系统的最后一道防线，通过加密可以保证在没有密钥的情况下获取备份或者物理介质变得毫无意义。

二、概念

加密层次结构

加密层次结构的每一层是如何对它下面的一层进行加密的，并且显示了最常用的加密配置。对层次结构的开始进行的访问通常受密码保护。SQL Server 用分层加密和密钥管理基础结构来加密数据。每一层都使用证书、非对称密钥和对称密钥的组合对它下面的一层进行加密。非对称密钥和对称密钥可以存储在 SQL Server 之外的可扩展密钥管理 (EKM) 模块中。

注意：

为了获得最佳性能，使用对称密钥（而不是证书或非对称密钥）加密数据。
数据库主密钥受服务主密钥保护。服务主密钥由 SQL Server 安装程序创建，并且使用 Windows 数据保护 API (DPAPI) 进行加密。
堆叠其他层的其他加密层次结构是可能的。
可扩展密钥管理 (EKM) 模块将对称密钥或非对称密钥保存在 SQL Server 的外部。
透明数据加密 (TDE) 必须使用称为数据库加密密钥的对称密钥，该密钥受由 master 数据库的数据库主密钥保护的证书保护，或者受存储在 EKM 中的非对称密钥保护。
服务主密钥和所有数据库主密钥是对称密钥。

1.服务主密钥(Service Master Key)

每一个实例只有一个服务主密钥，服务主密钥用于加密数据库主密钥，服务主密钥为 SQL Server 加密层次结构的根。服务主密钥是首次需要它来加密其他密钥时自动生成的。默认情况下，服务主密钥使用 Windows 数据保护 API 和本地计算机密钥进行加密。只有创建服务主密钥的 Windows 服务帐户或有权访问服务帐户名称和密码的主体能够打开服务主密钥。

---备份服务主密钥
BACKUP SERVICE MASTER KEY TO FILE = 'D:\DECRYPTION\ServerMasterKey' 
    ENCRYPTION BY PASSWORD = 'password'

----还原服务主密钥
RESTORE SERVICE MASTER KEY FROM FILE = 'D:\DECRYPTION\ServerMasterKey'
    DECRYPTION BY PASSWORD = 'password' 
    [FORCE];   ----即使存在数据丢失的风险，也要强制替换服务主密钥。

注意：
1.服务主密钥直接或间接地保护树中的所有其他密钥。如果在强制的还原过程中不能对某个相关密钥进行解密，则由该密钥所保护的数据便会丢失。
2.重新生成加密层次结构是一种消耗大量资源的操作。您应当将该操作安排在资源需求较低的时段进行。
3.当还原服务主密钥时，SQL Server 将对所有已使用当前服务主密钥加密的密钥和机密内容进行解密，然后使用从备份文件中加载的服务主密钥对这些密钥和机密内容进行加密。

2.数据库主密钥

数据库主密钥创建于对应数据库下，具体的保护对象可以参考下面的数据库范围的安全对象。如果要对数据库备份或者透明数据库加密那么需要将服务主密钥创建于Master数据库下。

----1.创建数据库主密钥
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
---2.删除数据库主密钥
DROP MASTER KEY

---3.备份数据库主密钥
/*
主密钥必须为打开状态，因此在备份主密钥之前应对其进行解密。如果主密钥使用服务主密钥进行加密，则不必显式打开。但如果主密钥仅使用密码进行加密，则必须显式打开。
建议在创建主密钥之后立即对其进行备份，并存储于另外一个安全的位置中。
*/

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MasterKey'; ---打开数据库主密钥,这里的密码为创建主密钥时设的密码
BACKUP MASTER KEY TO FILE = 'D:\DECRYPTION\MasterKey' -----主密钥私钥文件
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主密钥私钥文件
GO 
---4.还原数据库主密钥
/*
还原主密钥之后，SQL Server 会对使用当前活动的主密钥加密的所有密钥进行解密，然后使用还原后的主密钥对这些密钥进行加密。这种大量消耗资源的操作应当安排在资源需求较低的时段执行。如果当前的数据库主密钥未打开或无法打开，或者无法对任何使用该主密钥加密的密钥进行解密，则还原操作将失败。
如果当前数据库中没有主密钥，则 RESTORE MASTER KEY 将创建一个主密钥。新的主密钥不会自动使用服务主密钥进行加密。
请仅在主密钥无法恢复或解密失败时，才使用 FORCE 选项。仅由不可恢复密钥加密的信息将会丢失。
如果主密钥通过服务主密钥进行加密，则还原后的主密钥也通过该服务主密钥进行加密(当前服务器)。
*/
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'  
ENCRYPTION BY PASSWORD = 'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开，或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密，RESTORE 过程也应继续执行。
GO 

---5.打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  

GO

注意：

1.数据库主密钥是指用于保护证书私钥的对称密钥以及数据库中存在的非对称密钥。当创建主密钥时，会使用 Triple DES 算法以及用户提供的密码对其进行加密。
2.请使用服务主密钥对该主密钥的副本进行加密，并将副本存储在数据库和 master 中。通常，每当主密钥更改时，便会在不进行提示的情况下更新存储在 master 中的副本。
3.在当前服务器下创建的数据库主密钥默认就使用了服务主密钥加密和自动解密，不必使用 OPEN MASTER KEY 语句。如果还原到了新的服务器那么服务主密钥则不存在.必须使用 OPEN MASTER KEY 语句解密数据库主密钥。一旦数据库主密钥解密后，通过使用 ALTER MASTER KEY 语句向服务器提供数据库主密钥（使用服务主密钥加密）的副本，即可拥有将来启用自动解密的选项。
4.通过使用带 DROP ENCRYPTION BY SERVICE MASTER KEY 选项的 ALTER MASTER KEY 语句，可从自动密钥管理中排除特定数据库的数据库主密钥。然后，必须显式打开带密码的数据库主密钥。
5.数据库主密钥使用公钥对证书、非对称密钥进行加密，使用私钥进行解密，如果在当前创建的服务器上默认自动解密，如果还原到一台新的服务上时可能需要使用OPEN MASTER KEY进行解密。

3.证书

证书使用公钥对安全对象进行加密,使用私钥进行解密,默认证书存在就自动解密。

----1.创建自我签名的证书,使用数据库主密钥进行加密证书
USE MASTER;
GO
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间，不指定开始时间默认开始时间为当前时间
GO

---使用密码进行加密证书
USE MASTER;
GO
CREATE CERTIFICATE CertsByPW 
   ENCRYPTION BY PASSWORD = 'CertsByPW111'
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间，不指定开始时间默认开始时间为当前时间
GO

----2.备份证书
----警告: 用于对数据库加密密钥进行加密的证书尚未备份。应当立即备份该证书以及与该证书关联的私钥。如果该证书不可用，或者您必须在另一台服务器上还原或附加数据库，则必须对该证书和私钥均进行备份，否则将无法打开该数据库。
BACKUP CERTIFICATE MyCerts TO FILE = 'D:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

---备份使用私钥进行加密的证书,必须先对私钥进行解密
BACKUP CERTIFICATE CertsByPW TO FILE = 'D:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'CertsByPW111',----解密证书
                       FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

----3.通过备份文件创建证书,还原证书,
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件


---4.删除证书
DROP CERTIFICATE MyCerts

注意：

当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。

只有在使用密码对私钥进行加密时，才使用该选项。

如果未指定密码，则使用数据库主密钥对证书的私钥进行加密。如果数据库主密钥无法打开，则省略该子句会导致错误。

4.非对称密钥

“非对称密钥”是数据库级的安全对象实体。该实体的默认格式包含公钥和私钥。当未使用 FROM 子句执行时，CREATE ASYMMETRIC KEY 会生成新的密钥对。当使用 FROM 子句执行时，CREATE ASYMMETRIC KEY 会从文件中导入密钥对，或从程序集中导入公钥。
默认情况下，私钥受数据库主密钥保护。如果尚未创建任何数据库主密钥，则需要使用密码保护私钥。如果不存在数据库主密钥，则可以选择性地使用密码。

通常使用RSA加密算法，RSA_512、RSA_1024、RSA_2048。

---1.创建非对称密钥；非对称密钥可以由密码、数据库主密钥、EKM模块加密
--使用密码加密
CREATE ASYMMETRIC KEY AsymmetricByPW
    WITH ALGORITHM = RSA_2048   ---使用RSA_2048加密算法
    ENCRYPTION BY PASSWORD = 'AsymmetricByPW111'; 
GO
--2.通过文件创建非对称密钥
CREATE ASYMMETRIC KEY AsymmetricByFile 
    AUTHORIZATION Christina ----授予Christina用户使用该非对称密钥
    FROM FILE = 'c:\PacSales\Managers\ChristinaCerts.tmp'  
    ENCRYPTION BY PASSWORD = 'AsymmetricByFile111';
GO
---3.使用数据库主密钥加密
CREATE ASYMMETRIC KEY AsymmetricByMasterKey
    WITH ALGORITHM = RSA_2048;   ---使用RSA_2048加密算法

---4.删除非对称密钥
DROP ASYMMETRIC KEY AsymmetricByPW
GO

5.对称密钥

创建对称密钥时，必须至少使用以下项之一来对该对称密钥进行加密：证书、密码、对称密钥、非对称密钥或 PROVIDER。可使用上述每种类型中的多项对密钥进行加密。换言之，可以同时使用多个证书、密码、对称密钥以及非对称密钥对单个对称密钥进行加密

通常使用AES算法，有AES_128、AES_192、AES_256

--1.创建对称密钥，对称密钥可以由密码、非对称密钥、对称密钥、EKM模块加密
---使用密码加密
CREATE SYMMETRIC KEY SymmetricByPW
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'SymmetricByPW111';
GO
--注意:当使用密码（而不是数据库主密钥的公钥）对对称密钥进行加密时，便会使用 TRIPLE DES 加密算法。因此，用强加密算法（如 AES）创建的密钥本身受较弱算法的保护。
---2.使用证书加密
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

---3.删除非对称密钥
DROP SYMMETRIC KEY TestSymmetric
GO

三、安全对象

安全对象是 SQL Server 数据库引擎授权系统控制对其进行访问的资源。通过创建可以为自己设置安全性的名为“范围”的嵌套层次结构，可以将某些安全对象包含在其他安全对象中。安全对象范围有服务器、数据库和架构。

1.安全对象范围：服务器

包含以下安全对象：

端点
登录帐户
数据库

2.安全对象范围：数据库

包含以下安全对象：

用户
角色
应用程序角色
程序集
消息类型
路由
服务
远程服务绑定
全文目录
证书
非对称密钥
对称密钥
约定
架构

3.安全对象范围：架构

包含以下安全对象：

类型
XML 架构集合
对象

对象

下面是对象类的成员：

- 聚合
- 约束
- 函数
- 过程
- 队列
- 统计信息
- 同义词
- 表
- 视图

四、案例

案例1.备份加密

通过使用证书加密备份，如果需要在新的服务器上还原备份，先还原数据库主密钥和证书，然后就可以自动解密还原备份。

--1.备份数据库主密钥
BACKUP MASTER KEY TO FILE = 'D:\DECRYPTION\MasterKey' -----主密钥私钥文件
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主密钥私钥文件
GO 
--2.备份证书
BACKUP CERTIFICATE MyCerts TO FILE = 'D:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

--3.备份数据库
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'D:\BackDB\EncryDb.bak'    
WITH  COMPRESSION, stats = 10,   
    ENCRYPTION     
    (    
    ALGORITHM = AES_256,  
    SERVER CERTIFICATE = MyCerts
    )  
GO 
---4.在新服务器上还原数据库主密钥
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主密钥文件
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开，或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密，RESTORE 过程也应继续执行。
GO 
---5.在新服务器上还原证书
USE MASTER
GO
---需要先打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件

---6.在新服务器上还原数据库
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb]  
FROM  DISK = N'C:\DECRYPTION\EncryDb.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:\DECRYPTION\EncryDb.mdf',     
MOVE N'EncryDb_log' TO N'C:\DECRYPTION\EncryDb_log.ldf',    
NOUNLOAD,  STATS = 5  
GO

注意：在master数据库中创建数据库主密钥和证书。

如果没有还原数据库主密钥和证书直接还原数据库报错如下

案例2.TDE透明数据库加密

通过使用证书加密数据库

步骤操作如下：

创建主密钥
创建或获取由主密钥保护的证书
创建数据库加密密钥并通过此证书保护该密钥
将数据库设置为使用加密

USE EncryDb;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyCerts;
GO
USE EncryDb;
GO
---启用数据库加密
ALTER DATABASE EncryDb
SET ENCRYPTION ON;
GO
USE EncryDb;
GO
---禁用数据库加密
ALTER DATABASE EncryDb
SET ENCRYPTION OFF;
GO

1.“透明数据加密”(TDE) 可对数据和日志文件执行实时 I/O 加密和解密。这种加密使用数据库加密密钥 (DEK)，该密钥存储在数据库引导记录中以供恢复时使用。DEK 是使用存储在服务器的 master 数据库中的证书保护的对称密钥，或者是由 EKM 模块保护的非对称密钥。TDE 保护“处于休眠状态”的数据，即数据和日志文件。它提供了遵从许多法律、法规和各个行业建立的准则的能力。软件开发人员籍此可以使用 AES 和 3DES 加密算法来加密数据，且无需更改现有的应用程序。
2.启用 TDE 时，应该立即备份证书和与证书相关联的私钥。如果证书变为不可用，或者如果必须在另一台服务器上还原或附加数据库，则必须同时具有证书和私钥的备份，否则将无法打开该数据库。即使不再对数据库启用 TDE，也应该保留加密证书或非对称密钥。即使数据库没有加密，数据库加密密钥可能也保留在数据库中，执行某些操作时可能需要访问这些加密密钥。
3.数据库文件的加密在页级执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时会进行解密。TDE 不会增加已加密数据库的大小。

注意：在master数据库中创建数据库主密钥和证书。

测试在新的数据库中还原TDE透明加密数据库

---备份数据库
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'C:\DECRYPTION\EncryDb0122.bak'    
WITH  COMPRESSION, stats = 10  

---在新服务器中还原数据库主密钥
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主密钥文件
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开，或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密，RESTORE 过程也应继续执行。
GO 
---在新服务器上还原证书
USE MASTER
GO
---需要先打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件


---还原备份
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb_20180122]  
FROM  DISK = N'C:\DECRYPTION\EncryDb0122.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:\DECRYPTION\EncryDb0122.mdf',     
MOVE N'EncryDb_log' TO N'C:\DECRYPTION\EncryDb0122_log.ldf',    
NOUNLOAD,  STATS = 5  
GO

注意：经测试发现只有同数据库版本可以还原成功，在搞版本中还原提示会提示错误页，比如2014版本加密的数据库在2016版本中还原保持如下：

案例3.存储过程加密

在AS前增加WITH ENCRYPTION加密选项即可

USE EncryDb;
GO
CREATE PROCEDURE Sptest
WITH ENCRYPTION ---加密选项
AS
BEGIN


END

注意:
1.加密前先保留存储副本，否则加密完再需要解密就很麻烦
2.加密过的存储过程不影响修改、删除，但是无法查看存储过程的定义比如:sp_helptext、生成create语句、生成alter语句等。

案例4.数据列加密

CREATE DATABASE TestDb
GO
USE [TestDb]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
GO
---创建证书
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间，不指定开始时间默认开始时间为当前时间
GO
----创建对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

----创建测试表
USE TestDb;
GO
DROP TABLE Test
GO
CREATE TABLE Test
(Id INT NOT NULL,
Name NVARCHAR(30) NOT NULL,
EncryptionName varbinary(500) null
);
GO
INSERT INTO Test(Id,Name) VALUES(1,'aa'),(2,'bb');
GO

----加密列
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
UPDATE Test
SET EncryptionName= EncryptByKey(Key_GUID('SymmetricByCert'), Name);  
GO

SELECT * FROM TEST

GO
---解密查询
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
select Id,
Name,
EncryptionName,
convert(nvarchar(30), DecryptByKey(EncryptionName)) ConvertEncryptionName  ----nvarchar(30)值和明文字段类型长度保持一致 
from test;