目录

  • 透明数据加密（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 部分）。

SQL Server 实现 AES 加密 sql server 数据库加密_Server

开启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

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

列名

数据类型

说明

database_id

int

数据库 ID。

encryption_state

int

指示数据库是加密的还是未加密的。

0 = 不存在数据库加密密钥，未加密

1 = 未加密

2 = 正在进行加密

3 = 已加密

4 = 正在更改密钥

5 = 正在进行解密

6 = 正在进行保护更改（正在更改对数据库加密密钥进行加密的证书或非对称密钥）。

create_date

datetime

显示加密密钥的创建日期（UTC）。

regenerate_date

datetime

显示重新生成加密密钥的日期（UTC）。

modify_date

datetime

显示加密密钥的修改日期（UTC）。

set_date

datetime

显示加密密钥应用于数据库的日期（UTC）。

opened_date

datetime

显示上次打开数据库密钥的时间（UTC）。

key_algorithm

nvarchar(32)

显示用于密钥的算法。

key_length

int

显示密钥的长度。

encryptor_thumbprint

varbinary(20)

显示加密程序的指纹。

encryptor_type

nvarchar(32)

适用范围： SQL Server （ SQL Server 2012 (11.x) 到当前版本）。

描述加密程序。

percent_complete

real

数据库加密状态更改的完成百分比。 如果未发生状态更改，则为 0。

encryption_state_desc

nvarchar(32)

适用于：SQL Server 2019 (15.x) 及更高版本。

指示数据库是否已加密或未加密的字符串。

NONE

未加密过

DECRYPTION_IN_PROGRESS

ENCRYPTION_IN_PROGRESS

KEY_CHANGE_IN_PROGRESS

PROTECTION_CHANGE_IN_PROGRESS

encryption_scan_state

int

适用于：SQL Server 2019 (15.x) 及更高版本。

指示加密扫描的当前状态。

0 = 未启动任何扫描，TDE 未启用

1 = 正在进行扫描。

2 = 正在进行扫描，但已挂起，用户可以继续。

3 = 由于某种原因中止扫描，需要手动干预。 请联系 Microsoft 支持部门以获得更多帮助。

4 = 扫描已成功完成，TDE 已启用并且加密已完成。

encryption_scan_state_desc

nvarchar(32)

适用于：SQL Server 2019 (15.x) 及更高版本。

指示加密扫描当前状态的字符串。

NONE

RUNNING

SUSPENDED

ABORTED

完成

encryption_scan_modify_date

datetime

适用于：SQL Server 2019 (15.x) 及更高版本。

显示上次修改加密扫描状态的日期（UTC）。

当数据库设置为加密时，将加密全文检索。 在 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