【SQL Server】数据库备份加密方案

Love The Way You Lie 2023-10-06 14:11 36阅读 0赞

#### 目录 ####

*   *  透明数据加密（TDE）
     *   *   *  工作原理
             *  加密层次结构
             *  开启TDE
             *  还原秘钥及证书
             *  删除主秘钥及加密证书
             *  秘钥操作
             *  目录视图和动态管理视图
     *  对备份文件进行压缩加密
     *   *   *  xp\_cmdshell服务器配置选项
             *  xp\_cmdshell

### 透明数据加密（TDE） ###

透明数据加密 (TDE) 技术可以加密 SQL Server、Azure SQL 数据库和 Azure Synapse Analytics（SQL 数据仓库） 数据文件。 这种加密方式称为静态数据加密。  
为了帮助保护数据库的安全，可以采取以下预防措施：

*  设计安全的系统。
 *  对机密资产加密。

在数据库服务器外围构建防火墙。  
但恶意方如果窃取了驱动器或备份磁带等物理介质，就可以还原或附加数据库并浏览其数据。  
一种解决方案是加密数据库中的敏感数据，并使用证书保护用于加密数据的密钥。 此解决方案可以防止没有密钥的人使用这些数据。 但必须提前规划好此类保护。  
TDE 对数据和日志文件进行实时 I/O 加密和解密。 加密使用的是数据库加密密钥 (DEK)。 数据库启动记录存储该密钥，供还原时使用。 DEK 是一种对称密钥。 它由服务器的 master 数据库存储的证书或 EKM 模块所保护的非对称密钥提供保护。  
TDE 保护静态数据，也就是数据和日志文件。 它让你可以遵循许多法律、法规和各个行业建立的准则。 借助此功能，软件开发人员可以使用 AES 和 3DES 加密算法来加密数据，且无需更改现有的应用程序。

##### 工作原理 #####

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

##### 加密层次结构 #####

下图显示了 TDE 加密体系结构。 在 SQL 数据库上使用 TDE 时，用户仅能配置数据库级项目（数据库加密密钥和 ALTER DATABASE 部分）。  
![在这里插入图片描述][format_png_pic_center]

##### 开启TDE #####

1.  创建主秘钥
2.  创建数据库证书
3.  备份数据库证书
4.  为数据库设置加密

USE master
    GO
    
    -- 添加数据库主秘钥
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord'
    GO
    
    -- 添加数据库证书,Cert_Name为证书的名称
    CREATE CERTIFICATE <Cert_Name> WITH SUBJECT = '<Cert_Description>'
    GO
    
    -- 备份数据库证书，PassWord为证书的加密秘钥
    BACKUP CERTIFICATE <Cert_Name> TO FILE = 'D:\\Program Files\\SQL\\BackupEncrypted\\<Cert_Name>.cer'
    WITH PRIVATE KEY (FILE = 'D:\\Program Files\\SQL\\BackupEncrypted\\<Cert_Name>.pkey', ENCRYPTION BY PASSWORD = 'PassWord')
    GO
    
    -- 设置数据库加密算法，并添加需要加密的数据库
    USE HBStation_Cancel_Agent
    
    CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE <Cert_Name>
    
    ALTER DATABASE HBStation_Cancel_Agent SET ENCRYPTION ON
    GO

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

##### 还原秘钥及证书 #####

对数据库加密备份后若需要在另一台服务器上进行还原，需要先创建相同的主秘钥，并还原证书

-- 添加数据库主秘钥
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord'
    GO
    
    -- 还原加密证书
    CREATE CERTIFICATE <Cert_Name> FROM FILE = 'D:\\Program Files\\SQL\\BackupEncrypted\\<Cert_Name>.cer'
    WITH PRIVATE KEY (FILE = 'D:\\Program Files\\SQL\\BackupEncrypted\\<Cert_Name>.pkey', DECRYPTION BY PASSWORD = 'PassWord')
    GO
    
    -- 还原数据库
    RESTORE DATABASE [DBName] FROM  DISK = N'D:\Program Files\SQL\databackup\full\DBName.bak' WITH  FILE = 1,  MOVE N'DBName' TO N'D:\Program Files\SQL\ListDBData\DBName.ndf',  MOVE N'DBName_log' TO N'D:\Program Files\SQL\ListDBData\DBName_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
    GO

##### 删除主秘钥及加密证书 #####

当停止数据加密后不再需要主秘钥时，可以删除主秘钥和加密证书

USE master
    
    -- 删除加密证书
    DROP CERTIFICATE <Cert_Name>;
    GO
    
    -- 删除主秘钥
    DROP MASTER KEY;
    GO

##### 秘钥操作 #####

1.  `CREATE DATABASE ENCRYPTION KEY`

创建用于透明数据库加密的秘钥

-- Syntax for SQL Server  
    CREATE DATABASE ENCRYPTION KEY  
           WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }  
       ENCRYPTION BY SERVER   
        {  
            CERTIFICATE Encryptor_Name |  
            ASYMMETRIC KEY Encryptor_Name  
        }  
    [ ; ]

参数

*  WITH ALGORITHM = \{ AES\_128 | AES\_192 | AES\_256 | TRIPLE\_DES\_3KEY \}  
    指定用于加密密钥的加密算法。
 *  ENCRYPTION BY SERVER CERTIFICATE Encryptor\_Name  
    指定用于加密数据库加密密钥的加密程序的名称。
 *  ENCRYPTION BY SERVER ASYMMETRIC KEY Encryptor\_Name  
    指定用于加密数据库加密密钥的非对称密钥的名称。 要使用非对称密钥对数据库加密密钥进行加密，非对称密钥必须驻留在可扩展密钥管理提供程序上。

1.  `ALTER DATABASE ENCRYPTION KEY`

修改秘钥信息

-- Syntax for SQL Server  
    ALTER DATABASE ENCRYPTION KEY  
          REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }  
       |  
       ENCRYPTION BY SERVER   
        {  
            CERTIFICATE Encryptor_Name |  
            ASYMMETRIC KEY Encryptor_Name  
        }  
    [ ; ]

参数

*  REGENERATE WITH ALGORITHM = \{ AES\_128 | AES\_192 | AES\_256 | TRIPLE\_DES\_3KEY \}  
    指定用于加密密钥的加密算法。
 *  ENCRYPTION BY SERVER CERTIFICATE Encryptor\_Name  
    指定用于加密数据库加密密钥的证书的名称。
 *  ENCRYPTION BY SERVER ASYMMETRIC KEY Encryptor\_Name  
    指定用于加密数据库加密密钥的非对称密钥的名称。

> 注意  
> 用于加密数据库加密密钥的证书或非对称密钥必须位于 master 系统数据库中。  
> 数据库所有者 (dbo) 发生更改时，不必重新生成数据库加密密钥。  
> 在数据库加密密钥修改过两次后，必须执行日志备份才能再次对数据库加密密钥进行修改。

1.  `DROP DATABASE ENCRYPTION KEY`

删除数据库秘钥

DROP DATABASE ENCRYPTION KEY

> 如果数据库已加密，则必须先使用 ALTER DATABASE 语句对数据库进行解密。 请先等待解密完成，再删除数据库加密密钥。

示例

ALTER DATABASE <DBName> SET ENCRYPTION OFF;
    GO
    
    USE <DBName>
    GO
    
    DROP DATABASE ENCRYPTION KEY;
    GO

##### 目录视图和动态管理视图 #####

1.  sys.databases

查看数据库的基础信息

SELECT name, is_encrypted FROM sys.databases

1.  sys.certificates

查看数据库证书信息

1.  sys.dm\_database\_encryption\_keys

用于提供有关数据库加密密钥的信息以及加密状态的动态管理视图。

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>数据类型</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>database_id</td> 
   <td>int</td> 
   <td align="left">数据库 ID。</td> 
  </tr> 
  <tr> 
   <td>encryption_state</td> 
   <td>int</td> 
   <td align="left">指示数据库是加密的还是未加密的。<br> 0 = 不存在数据库加密密钥，未加密<br> 1 = 未加密 <br>2 = 正在进行加密 <br>3 = 已加密<br>4 = 正在更改密钥<br>5 = 正在进行解密 <br>6 = 正在进行保护更改（正在更改对数据库加密密钥进行加密的证书或非对称密钥）。</td> 
  </tr> 
  <tr> 
   <td>create_date</td> 
   <td>datetime</td> 
   <td align="left">显示加密密钥的创建日期（UTC）。</td> 
  </tr> 
  <tr> 
   <td>regenerate_date</td> 
   <td>datetime</td> 
   <td align="left">显示重新生成加密密钥的日期（UTC）。</td> 
  </tr> 
  <tr> 
   <td>modify_date</td> 
   <td>datetime</td> 
   <td align="left">显示加密密钥的修改日期（UTC）。</td> 
  </tr> 
  <tr> 
   <td>set_date</td> 
   <td>datetime</td> 
   <td align="left">显示加密密钥应用于数据库的日期（UTC）。</td> 
  </tr> 
  <tr> 
   <td>opened_date</td> 
   <td>datetime</td> 
   <td align="left">显示上次打开数据库密钥的时间（UTC）。</td> 
  </tr> 
  <tr> 
   <td>key_algorithm</td> 
   <td>nvarchar(32)</td> 
   <td align="left">显示用于密钥的算法。</td> 
  </tr> 
  <tr> 
   <td>key_length</td> 
   <td>int</td> 
   <td align="left">显示密钥的长度。</td> 
  </tr> 
  <tr> 
   <td>encryptor_thumbprint</td> 
   <td>varbinary(20)</td> 
   <td align="left">显示加密程序的指纹。</td> 
  </tr> 
  <tr> 
   <td>encryptor_type</td> 
   <td>nvarchar(32)</td> 
   <td align="left">适用范围： SQL Server （ SQL Server 2012 (11.x) 到当前版本）。<br>描述加密程序。</td> 
  </tr> 
  <tr> 
   <td>percent_complete</td> 
   <td>real</td> 
   <td align="left">数据库加密状态更改的完成百分比。 如果未发生状态更改，则为 0。</td> 
  </tr> 
  <tr> 
   <td>encryption_state_desc</td> 
   <td>nvarchar(32)</td> 
   <td align="left">适用于：SQL Server 2019 (15.x) 及更高版本。<br>指示数据库是否已加密或未加密的字符串。<br> NONE <br>未加密过<br>DECRYPTION_IN_PROGRESS<br>ENCRYPTION_IN_PROGRESS <br>KEY_CHANGE_IN_PROGRESS<br>PROTECTION_CHANGE_IN_PROGRESS</td> 
  </tr> 
  <tr> 
   <td>encryption_scan_state</td> 
   <td>int</td> 
   <td align="left">适用于：SQL Server 2019 (15.x) 及更高版本。<br>指示加密扫描的当前状态。<br>0 = 未启动任何扫描，TDE 未启用 <br>1 = 正在进行扫描。<br>2 = 正在进行扫描，但已挂起，用户可以继续。<br>3 = 由于某种原因中止扫描，需要手动干预。 请联系 Microsoft 支持部门以获得更多帮助。<br> 4 = 扫描已成功完成，TDE 已启用并且加密已完成。</td> 
  </tr> 
  <tr> 
   <td>encryption_scan_state_desc</td> 
   <td>nvarchar(32)</td> 
   <td align="left">适用于：SQL Server 2019 (15.x) 及更高版本。<br>指示加密扫描当前状态的字符串。<br>NONE<br>RUNNING<br>SUSPENDED <br>ABORTED<br>完成</td> 
  </tr> 
  <tr> 
   <td>encryption_scan_modify_date</td> 
   <td>datetime</td> 
   <td align="left">适用于：SQL Server 2019 (15.x) 及更高版本。<br>显示上次修改加密扫描状态的日期（UTC）。</td> 
  </tr> 
 </tbody> 
</table>

> 当数据库设置为加密时，将加密全文检索。 在 SQL Server 2008 之前的 SQL Server 版本中创建的这些索引由 SQL Server 2008 或更高版本导入数据库，并由 TDE 加密。

### 对备份文件进行压缩加密 ###

压缩备份的基础是利用WinRAR，使用数据库xp\_cmdshell存储过程，执行cmd命令来对文件进行压缩加密备份

##### xp\_cmdshell服务器配置选项 #####

如果需要启用 xp\_cmdshell，可以使用基于策略的管理或运行 sp\_configure 系统存储过程，如以下代码示例所示：

-- To allow advanced options to be changed.  
    EXECUTE sp_configure 'show advanced options', 1;  
    GO  
    -- To update the currently configured value for advanced options.  
    RECONFIGURE;  
    GO  
    -- To enable the feature.  
    EXECUTE sp_configure 'xp_cmdshell', 1;  
    GO  
    -- To update the currently configured value for this feature.  
    RECONFIGURE;  
    GO

##### xp\_cmdshell #####

EXEC xp_cmdshell { 'command_string' } [ , no_output ]

参数

*  " command\_string "  
    包含要传递到操作系统的命令的字符串。 command\_string为varchar （8000） 或nvarchar （4000），无默认值。 command\_string不能包含一组以上的双引号。 如果在command\_string中引用的文件路径或程序名称中有空格，则需要使用一对引号。 如果不方便使用内含的空格，则可考虑使用 FAT 8.3 文件名作为解决方法。
 *  no\_output  
    可选参数，指定不应向客户端返回任何输出。

返回代码值  
0（成功）或 1（失败）

> Xp\_cmdshell生成的 Windows 进程与服务帐户具有相同的安全权限 SQL Server 。  
> xp\_cmdshell同步操作。 在命令 shell 命令执行完毕之前，不会将控制权返回给调用方。  
> 可以使用基于策略的管理或通过执行sp\_configure来启用和禁用xp\_cmdshell 。

示例：

DECLARE @path varchar(2000) = dbo.a_getInitConfig('Backup_Path')
    DECLARE @FileName varchar(200) = 'HBStation_Cancel_Agent' + '_' + REPLACE(CONVERT(varchar, GETDATE(), 23), '-', '') + '_Full'
    DECLARE @bakName varchar(200) = @FileName + '.bak'
    DECLARE @rarName varchar(200) = @FileName + '.rar'
    DECLARE @cmd nvarchar(2000)
    
    -- 完成备份数据库
    EXEC('BACKUP DATABASE [HBStation_Cancel_Agent] TO DISK = '''+@path + @bakName+''' WITH NOFORMAT, INIT,  NAME = '''+@bakName +''', COMPRESSION ')
    
    -- password为压缩密码
    -- 将备份文件进行压缩，并删除原文件
    SET @cmd = 'C:/Progra~1/WinRAR/RAR.exe a -ppassword -df -ep1 "'+ @path + @rarName +'" "'+ @path + @bakName +'"'
    
    EXEC xp_cmdshell @cmd

[format_png_pic_center]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9kb2NzLm1pY3Jvc29mdC5jb20vemgtY24vc3FsL3JlbGF0aW9uYWwtZGF0YWJhc2VzL3NlY3VyaXR5L2VuY3J5cHRpb24vbWVkaWEvdGRlLWFyY2hpdGVjdHVyZS5wbmc?x-oss-process=image/format,png#pic_center