SQL Server 加密案例解析

一、概述

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

二、概念

加密层次结构

SQL Server 加密案例解析

加密层次结构的每一层是如何对它下面的一层进行加密的，并且显示了最常用的加密配置。对层次结构的开始进行的访问通常受密码保护。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数据库中创建数据库主**和证书。

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

SQL Server 加密案例解析

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

通过使用证书加密数据库

SQL Server 加密案例解析

步骤操作如下：

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

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版本中还原保持如下：

SQL Server 加密案例解析

案例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;