实现SQL Server多层面加密机制

■ 河南 郭建伟

SQL Server作为常用的数据库，在实际工作中应用的极为广泛。如何提高SQL Server的安全性，防止其中的数据被盗取或者篡改，是管理员必须面对的重要问题。使用加密技术，可以有效保护SQL Server数据库的安全。随着SQL Server版本的演进，其中不断添加了各种加密机制，能够有力的保护数据的安全。这里就以SQL Server 2016为例，从加密传输、加密数据库、加密字段等多个层面，来说明具体的实现方法。

创建所需的的证书

对于数字证书来说，可以从网上购买公网证书，也可以生成企业私有证书，对于后者来说，最好在客户端安装根证书，来信任证书链。证书准备好之后，接下来在SQL Server服务器上上启用传输加密功能，在客户端进行连接时，需要指定传输加密的连接，例如在相关的客户端程序中添加诸如“Encrypt=True”，“TrustServerCertificate=Ture”等参数。

在SQL Server服务器上点击“Windows+R”键，运行“mmc”程序，在管理控制台上点击菜单“文件”→“添加/删除管理单元”项，在打开窗口左侧选择“证书”项，点击“添加”按钮按钮将其添加进来。

点击“确定”按钮，选择“计算机账户”项，点击“完成”按钮，在控制台左侧选择“个人”→“证书”项，在右侧可以看到在域环境中由企业CA颁发的证书。

如果没有的话，可以在右键菜单上点击“所有任务”→“申请新证书”项，在向导界面中选择“Active Directory注册策略”项，点击“下一步”按钮，选择所需的证书。

这些证书是由CA服务器发布出来的。例如选择“Web服务器”项，点击“注册此证书需要详细信息，单击这里以配置设置”链接，在证书属性窗口中的“使用者”面板的“类型”列表中选择“公用名”项，在“值”栏中输入本机的FQDN计算机全名。

点击“添加”按钮，将其添加进来。在“常规”面板中输入输入其友好名称，在“私钥”面板中的“密钥选项”栏中选择“使私钥可以导出”项，便于导出私钥。保存配置信息后，点击“注册”按钮，可以申请到所需的证书。

对于该证书来说，默认只能由管理员和系统来访问其私钥，并利用该证书加密数据，对于其他用户是不允许的。

管理SQL Server服务账户

打开SQL Server 2016配置管理器，在左侧选择“SQL Server服务”项，在右侧显示所有的服务和实例，选择所需的实例（例如，“SQL Server(MSSQLSERVER)”等），在“登录身份为”列中查看其使用的服务账户信息。一般来说，该账户都不是特权账户。在上述控制台上选择证书，在其右键菜单中选择“所有任务”→“管理私钥”项，在打开窗口中点击“添加”按钮，输入上述服务账户名称，将其添加到“组或用户名”列表中。选择该账户，在权限列表中的“允许”列中选择“读取”项，即赋予其读取私钥的权限。

注意，还需要添加SQL Server默认的内置服务账户，但是该账户是无法直接显示的。

点击“添加”按钮，在打开窗口中点击“位置”按钮，选择本机名称。之后输入“nt servicemssqlserver”，将其该账户添加进来，按照同样的方法，赋予其读取私钥的权限。

在SQL Server 2016配置管理器左侧选择“SQL Ser ver网络配置”→“MSSQLSER VER的协议”项，在其右键菜单上点击“属性”项，在打开窗口中的“标志”面板中的“Force Envryption”栏中选择“是”项，启用强制加密功能。在“证书”面板中的“证书”列表中选择上述证书，点击“确定”按钮保存配置信息。

在左侧选择“SQL Serve r”项，在右侧选择“SQL Ser ver(MSSQLSERVER)”服务，在其右键菜单上点击“重新启动”项，重启该服务使上述配置生效。

激活加密传输功能

之后在本机上运行Micr osoft SQL Server Managem ent Studio（简称MSSMS）进行连接测试，在连接到服务器窗口中的“选项”按钮，在“连接属性”面板中选择“加密连接”和“信任服务器证书”项。

在连接窗口中的“服务器名称”栏中输入上述证书中指定的计算机全名，点击“连接”按钮，才可以顺利连接SQL Server服务器，否则系统会出现“目标主要名称不正确”的错误提示。

对于加密的连接来说，可以打开“数据库”→“master”→“视图”→“系统视图”项，在其中选择“sys.dm_exec_connections”或者“sys.dm_exec_sessions”视图，根据显示信息，可以观察到当前连接是否加密状态。

为了便于客户端访问，需要在防火墙上放行TCP 1433，UDP1434端口，后者是被SQL Server Browser服务器使用，其主要用于管理多个SQL Server实例。

在MSSMS中选择“实例名称”→“安全性”→“登录名”项，在其右键菜单上点击“新建登录名”项，创建新用户（例如“user1”等），使用SQL Server身份验证方式。

在左侧选择“用户映射”项，在右侧的“映射到此登录名的用户”栏选择合适的数据库名称，在“数据库角色成员身份”列表中选择“db_datareader”项，使其针对该数据库拥有读取权限。

注意，对于没有加入域的客户端来说，只能使用SQL Server账户进行连接。

在客户端上可以打开CMD窗口，执行“psping xxx:1433”命令，使用psping工具，对目标SQL Server服务器进行探测，其中的“xxx”为具体的域名。之后运行MSSMS等工具，使用上述SQL Server账户进行连接，输入密码后，可以顺利连接到目标服务器上，并且只能对指定的数据库有读取权限。

SQL Server数据库的加密架构

对数据库加密来说，加密的对象是存储在磁盘上的数据库文件。出于性能和安全性的考虑，SQL Server使用的是对称密钥加密方式。为了保护密钥的安全，还可以使用非对称加密技术，对该对称密钥进行保护。对于非对称密钥（例如证书等）来说，则使用数据库主密钥进行保护。对于整个SQL Server加密体系来说，最外层的是Windows系统，其依靠Data Protection API组件来保护服务主密钥。

可以看出，对于SQL Server加密体系来说，其并不是独立存在的，其最终是依靠操作系统层面提供保护的。当SQL Server安装完成后，会自动生成服务主密钥，用来保护数据库主密钥。对于数据库文件加密来说，主要使用SQL Server内置的透明数据加密TDE技术来实现。当然，也可以使用EFS和BitLocker来加密数据。对于TDE来说，最外层是依靠Windows的DPAPI组件。

在此基础上，可以创建数据库主密钥，其存在于Master数据库中。在该数据库中创建一张证书，该证书用来保护最终加密数据使用到的Database Encryption Key对称密钥。注意，该密钥是存放在需要加密的目标数据库中的。这样，就形成为完整的保护链。除了数据库证书外，其余的密钥都是对称密钥。从整体上来说，TDE需要使用到数据库证书，其加密对象是存储到磁盘的数据文件，可以防止数据文件的物理窃取，同时将加密日志和备份文件，采用的是后台加密方式。

在实际使用SQL Server过程中，有时需要更改其服务账户，其方法是打开SQL Server配置管理器，在其中双击SQL Server服务，在其属性窗口中打开“登录”面板，在其中选择“本账户”项，设置新的账户和密码信息。在此过程中，服务主密钥也需要对更换后账户进行保护，即服务主密钥是通过该服务的密码来实现实现保护的。如果直接在系统内置的服务器管理器打开SQL Server服务属性窗口，在其中更改服务账户的话，就容易造成服务主密钥异常，导致数据加密失败的情况发生。

生成TDE加密证书

当然，也可以在MSSMS查询窗口中执行“backup service master key to fil e='f:akfileServiceMas terKey.bak ENCRYPTION NY PASSWORD='xxxxxx'”，“Go”语句，来备份服务主密钥。对应的执行“restore service master key from file='f:akfileServiceMasterKey.bak DECRYPTION NY PASSWORD='xxxxxx'”，“Go”语句，来恢复服务主密钥，其中的“xxxxxx”为具体的密码。执行“Select * From sys.symmetric_keys”命令，来查看数据库主密钥的信息和状态。

执行“SELECT name,IS_MASTER_KEY_ENCRYPTED_BY_SERVER FROM sys.databases”命令，可以查看是否被服务主密钥加密。

执行“backup master key to file='f:akfileD BMasterKey.bak ENCRYPTION BY PASSWORD='xxxxxx'”命令，备份数据库数据库主密钥到指定的文件。

执行“restore master key from file='f:akfileDBMasterKey.bak DECRYPTION NY PASSWORD='x xxxxx' ENCRYPTION BY PASS WORD='xxxxxx'”命令，从指定的备份文件恢复数据库主密钥。”执行“USE master;”，“Go”，“CREATE MASTER KEY ENCRYPTION BY PASSWORD='xxxxxx'”，“Go”，“CREATE CERTIFICATE TDECert WITH SUBJECT='MY TDE CERT'”，“Go”语句，来创建TDE使用的加密证书。

使用TDE加密数据库

其中的“xxxxxx”为具体的密钥，即数据库主密钥即通过服务主密钥进行保护，也通过该密码进行保护，实现双重保护效果。

打开“数据库”→“系统数据库”→“master”→“安全性”→“证书”项，在其下可以看到名为“TDECert”的证书。

在MSSMS左侧的“对象资源管理器”列表中选择目标数据库（例如“TDE_DB”），在其右键菜单上点击“任务”→“管理数据库加密”项，在打开窗口（如图1）中的“加密算法”列表中选择合适的加密算法。

选择“使用服务器证书”项，在列表中选择“TDECert”证书，选择“将数据库加密设置为ON”项，点击“确定”按钮，激活加密功能。

注意，在执行加密设置时，该数据库必须处于独占访问状态。

图1 配置字段加密功能

其方式是执行“ALTER DATABASE TPE_DB 4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE”命令，即可将目标数据库设置为独占状态。

这样，即使别人将该数据库文件窃取，当在其他的SQL Server数据库实例中附加该数据库文件时，会出现“无法为该请求检索数据”的提示，使其无法访问该加密的数据库文件。因为证书实际上是保存在Master数据库中的，因此只要备份了Master数据库，就相当于备份了证书信息，当对其进行恢复后，就可以恢复TDE证书了。

由于服务主密钥和数据库主密钥极为重要，所以一定要对其进行备份。

常用字段加密类型

对于SQL Server数据库中的某些字段来说，可能包含了一些敏感的信息，为了防止别人随意查看，需要对目标字段进行加密。利用加密方式，可以加密字段的所有内容。在字段数据存储之前就进行了加密处理，当数据存储之后，自然就处于加密状态了。针对字段加密，在SQL Server中提供了两种实现方法，其一是使用加解密函数和密钥，其二是利用Always Encrypted总是加密技术来实现。

对于前者来说，是先生成数据库主密钥，并据此来生成证书，利用该证书创建对称密钥，最后利用该对称密钥对数据进行加解密，其特点是实现了存储前加密，加密技术比较陈旧，具有很好的兼容性，但是缺点是管理者角色不分离，即不能防范管理人员查看加密数据。因为管理员可以读取证书，这样就可以得到密钥，所以其可以很轻松的解密数据。对于SQL Server 2016来说，可以使用总是加密这一技术，来避开上述问题。

该技术的实现需要一些前提条件，例如需要证书的存储（包括Windows或者云端的证书存储），需要.Net Framework 4.6组件的支持，查询语句需要实现参数化的设置等。其也可以实现存储前加密，配置简单技术新颖，可以实现管理角色分离，大大提高了数据的安全性。这对于云计算云存储等场景来说，显得极为重要，用户可以将数据存放在任何位置，而不必担心其安全性。

创建证书和对称密钥

在MSSMS中选择目标数据库，在查询窗口中执行“CREATE MASTER KEY ENCRYPTION BY PASSWORD='p@ssw0rd!'”命令，创建所需的主密钥，这里为“p@ssw0rd!”。

执行“OPEN MASTER KEY DECRYPTION BY PASSWORD='p@ssw0rd!'”命令，打开数据库主密钥。

注意，字段加密和TDE加密不同，TDE加密需要将主密钥放置到目标数据库之外的地方。

执行“CREATE CERTIFIC ATE cert_zs -ENCRYPTION BY PASSWORD=' p@ssw0rd!' WITH SUBJECT='New ce rt01',START_DATE='1/1/20 18',EXPIRY_DATE='1/1/20 80';”命令，创建名为“cert_zs”的证书，设置其有效期等参数。如果仅仅使用数据库主密钥创建证书，则可以不指定具体的密码，当然，如果指定了密码，则可以对证书进行备份。

为了保护证书的安全，可以执行“BACKUP CERTIFICATE cert_zs TO FILE='f:certbakcert_zs.bak' WITH PRIVATE KEY”命令，可以将证书备份到指定的文件。

如果指定了加密密码，可以执行“BACKUP CERTIFICATE -DECRYPTION BY PASSWORD='p@ssw0rd!',F ILE='f:certbakcert_zs.bak',ENCRYPTION BY PAS SWORD='pASSword00!'”之类的语句，将证书备份到指定的文件中，当然，该备份文件是处于加密状态的。

执行“SELECT * FROM sys.certificates”命令，可以查看证书信息。对应的，执行“CREATE CERTIFICATE cert_zs FROM FILE=f:c ertbakcert_zs1.bak'WITH PRIVATE KEY”命令，可以使用数据库主密钥来恢复证书。

如果为证书指定了密码，则需要执行“CREATE CERTIFICATE cert_zs FROM FILE=f:certbakcert_zs1.bak' DECRYPTION BY PASSWORD='@ssw0rd!'”之类的语句，从备份文件恢复证书。

执行“CREATE SYMMETRI C KEY newkey1 WITH ALGOR ITHM=AES_256 ENCRYPTION BY CERTIFICATE cert_zs”命令，使用上述证书，利用指定的加密算法，来产生名为“newkey1”的对称密钥，该对称密钥是受到证书保护的。

执行“SELECT * FROM sys.symmetric_keys”，“SELECT * FROM sys.openkeys”命令，可以查看对称密钥信息。

使用函数加解密字段

准备好了对称密钥后，就可以对字段进行加密了。

例如，执行“CREATE TABLE Table1 (zduan1 INT PRIMARY KEY,zuduan2 NVARCHAR(20) NOT NULL,ziduan3 MONEY NOT NULL,ziduan9 VARBINARY(MAX) NULL)”语句，在当前的数据库中创建名为“Table1”的表，执行“INSERT Table1 VALUES(1,'a100',9000,null)”，“INSERT Table1 VALUES(1,'a200',8 000,null)”等语句，向该表中插入一些数据。执行“OPEN SYMMETRIC KEY newkey1 DECRYPTION BY CERTIFICATE cert_zs”命令，打开上述名为“newkey1”的对称密钥。

执行“UPDATE Table1 S ET ziduan9=ENCRYPTBYKEY(K EYGUID('newkey1'),CAST(z iduan3 AS VARCHAR(20)))”命令，使用名为“ENCRYPTBYK EY”的函数，对“ziduan9”字段进行加密，并将加密后的内容存储到“ziduan3”字段中，这样便于查看加密效果。

执行“Select * from Table1”命令，可以看到实际的加密效果。

执行“CLOSE SYMMETRIC KEY newkey1”，“Go”语句，关闭对称密钥。

在执行解密操作时，先执行“OPEN SYMMETRIC KEY newkey1 DECRYPTION BY CERTIFICATE cert_zs”命令，打开上述对称密钥。

执行“SELECT ziduan 1,ziduan2,ziduan3,CONVE RT(VARCHAR(20),DECRYPTB YKEY(zuduan9)) AS PAY_DEC FROM Table1”命令，使用“DECRYPTBYKEY”函数对加密的“ziduan9”字段进行解密，显示真实的数据内容。执行“CLOSE SYMMETRIC KEY newkey1”命令，关闭对称密钥。

使用Always Encrypted加密

同上述字段加密方法相比，使用总是加密技术可以更加有效提高安全性。但是，这要求使用的MSSMS的版本是17.6及其以上方可。总是加密技术将密钥和数据库进行了分离，在目标数据库中选择需要加密的表，在其右键菜单上选择“加密列”项，在向导界面中选择需要加密的列，在“加密类型”列中提供了“确定性密钥”和“随机密钥”两种类型，前者性能较好，后者加密强度较高。一般来说选择确定性密码即可。

点击“下一步”按钮，在打开窗口（如图2）中的“选择列主密钥”列表中选择“自动生成列主密钥”项，在“选择密钥存储提供程序”栏中可以选择证书存储类型，包括WIndows证书存储和Azure Key Vault类型。在“选择主密钥源”列表中选择“当前用户”项，表示不管采用哪一种类型，必须针对当前用户有效，即将权限和角色进行了分离，只有掌握主密钥的用户才可以拥有对加密数据的控制权。

注意，操作向导运行在哪台主机上，主密钥就保存在该机上。

图2 配置字段加密功能

点击“完成”按钮，可以生成主密钥，并据此生成列加密密钥。对于列加密密钥来说，是存储在数据库中的。但是，该密钥必须必须通过主密钥才能发挥作用。主密钥是保存在指定的证书存储中的。即其和数据库服务器是分开存储的。

当加密完成后，打开该数据表，可以看到指定的列已经处于加密状态了。在MSSMS中选择目标数据库，选择“安全性”→“Always Encrypted密钥”项，在其下可以显示列主密钥和列加密密钥信息。对于后者来说，需要使用前者进行加密。因此，只要掌控了列主密钥，就可以对数据进行解密。当然，列主密钥并不会存储到数据库中的，其保存在指定的证书存储中。