Biegral Blog

原文出处：http://blog.csdn.net/kk185800961/article/details/53166616  （博主还有很多关于SQL的原创文章）

从 SQL Server 2014 开始，SQL Server 可在创建备份时加密数据。通过在创建备份时指定加密算法和加密程序（证书或非对称密钥），可创建加密的备份文件。

若要在备份期间加密，必须指定加密算法以及用于保护加密密钥的加密程序。支持以下加密选项：

加密算法：支持的加密算法包括：AES 128、AES 192、AES 256 和 Triple DES
加密程序：证书或非对称密钥

优点：

1. 加密数据库备份有助于保证数据安全：SQL Server 提供用于在创建备份时加密备份数据的选项。

2. 加密还可用于使用 TDE 加密的数据库。

3. 由 Microsoft Azure 的 SQL Server 托管备份 进行的备份支持加密，这样可提高站点外备份的安全性。

4. 此功能支持多个最高 AES 256 位的加密算法。这样可选择符合要求的算法。

5. 可将加密密钥与扩展密钥管理 (EKM) 提供程序集成。（仅支持位于扩展密钥管理 (EKM) 中的非对称密钥。）

限制：

1. 如果使用非对称密钥加密备份数据，则仅支持位于 EKM 提供程序中的非对称密钥。

2. SQL Server Express 和 SQL Server Web 不支持在备份期间进行加密。但是，支持从加密的备份还原到 SQL Server Express 或 SQL Server Web 的实例。

3. 旧版 SQL Server 无法读取加密的备份。

4. 加密的备份不支持追加到现有的备份集选项。

当前测试，证书加密备份，从另一个实例中还原。

use master  
go  
  
--创建主密钥  
create master key encryption by password = N'Hello@MyMasterKey'  
go  
  
--创建证书  
create certificate Mycertificate    
with subject = N'EnryptData certificate',    
start_date = N'20160101',    
expiry_date = N'20990101';    
go    
  
--证书加密备份数据库  
backup database [DemoDB]    
to disk = N'E:\TEST\DemoDB.bak'    
with    
    compression, stats = 10,   
    encryption     
    (    
    algorithm = aes_256,    
    server certificate = mycertificate    
    )  
go    
  
--备份主密钥  
backup master key   
to file = N'E:\TEST\master_SMK.key'     
encryption by password = N'Hello@MyMasterKey'  
go  
  
--备份证书和私钥  
backup certificate Mycertificate     
to file = N'E:\TEST\Mycertificate.cer' --用于加密的证书备份路径    
with private key (     
    file = N'E:\TEST\Mycertificate_saleskey.pvk' ,--用于解密证书私钥文件路径     
    encryption by password = N'Hello@Mycertificate' );--对私钥进行加密的密码    
go  

USE [master]  
GO  
RESTORE DATABASE [DemoDB]  
FROM  DISK = N'E:\TEST\DemoDB.bak'   
WITH  FILE = 1,  
MOVE N'DemoDB' TO N'E:\TEST\DemoDB0.mdf',  
MOVE N'DemoDB1' TO N'E:\TEST\DemoDB01.ndf',    
MOVE N'DemoDB2' TO N'E:\TEST\DemoDB02.ndf',    
MOVE N'DemoDB_log' TO N'E:\TEST\DemoDB0_log.ldf',    
NOUNLOAD,  STATS = 5  
GO  
  
/*  
消息 33111，级别 16，状态 3，第 4 行  
找不到指纹为 '0xFC76058DC0D3DD4FEF179B862CE8BA168D59F007' 的服务器 证书。  
消息 3013，级别 16，状态 1，第 4 行  
RESTORE DATABASE 正在异常终止。  
*/  

--还原主密钥  
USE master  
GO  
RESTORE MASTER KEY  
FROM FILE = N'E:\TEST\master_SMK.key'  
DECRYPTION BY PASSWORD = N'Hello@MyMasterKey'  
ENCRYPTION BY PASSWORD = N'Hello@MyMasterKey' FORCE  
GO  
  
/*还原主密,两实例的服务启动账户必须相同，否则出现以下错误：  
  
消息 15317，级别 16，状态 2，第 22 行  
主密钥文件不存在或格式无效。  
The master key file does not exist or has invalid format  
*/  
  
  
--打开密钥  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'Hello@MyMasterKey'  
GO  
  
--还原证书  
CREATE CERTIFICATE Mycertificate     
FROM FILE = N'E:\TEST\Mycertificate.cer'     
WITH PRIVATE KEY (    
    FILE = N'E:\TEST\Mycertificate_saleskey.pvk',     
    DECRYPTION BY PASSWORD = 'Hello@Mycertificate');    
GO  
  
  
--  再还原数据库，正常！  
USE [master]  
GO  
RESTORE DATABASE [DemoDB]  
FROM  DISK = N'E:\TEST\DemoDB.bak'   
WITH  FILE = 1,  
MOVE N'DemoDB' TO N'E:\TEST\DemoDB0.mdf',  
MOVE N'DemoDB1' TO N'E:\TEST\DemoDB01.ndf',    
MOVE N'DemoDB2' TO N'E:\TEST\DemoDB02.ndf',    
MOVE N'DemoDB_log' TO N'E:\TEST\DemoDB0_log.ldf',    
NOUNLOAD,  STATS = 5  
GO  

【补充】

如果想了解更多关于SQL加密可参考《对 sql server 数据库的备份进行加密》