SQL|SQL Server 对称秘钥的管理 SQL|Server|安全管理|Server

对称秘钥，加密和解密使用相同的秘钥。
对称秘钥的应用：《SQL Server 对称密钥在数据加密中的应用》
对称秘钥的管理包括创建、打开、修改、关闭和删除，本文将逐一使用实例进行介绍。
创建对称秘钥
创建对称秘钥必定要指定一种加密方式，而对称秘钥有五种加密方式，分别为证书、密码、对称秘钥、非对称秘钥和PROVIDER。我们将按照对称秘钥的加密方式给出创建秘钥的样例。
为了演示创建过程，我们先创建一个测试数据库：

--创建测试数据库 CREATE DATABASE SymmetricKeyAdmin ON PRIMARY (NAME='SymmetricKeyAdmin',FILENAME='D:\database\SymmetricKeyAdmin.mdf') LOG ON (NAME='SymmetricKeyAdmin_log',FILENAME='D:\database\SymmetricKeyAdmin_log.ldf')

创建证书加密的对称秘钥

USE SymmetricKeyAdmin GO --创建数据库主密钥 CREATE MASTER KEY ENCRYPTION BY PASSWORD='[J,XJK8|AwE*rLk' --创建证书 CREATE CERTIFICATE CertForEncryption WITH SUBJECT ='Certficate for Encrypton Symmetric' --创建证书加密的对称密钥 CREATE SYMMETRIC KEY AsyEncryptByCert WITH ALGORITHM=AES_256 ENCRYPTION BY CERTIFICATE CertForEncryption

我们可以使用动态视图 sys.symmetric_keys 、sys.key_encryptions查看对称秘钥的信息：

SELECT * FROM sys.symmetric_keys SELECT * FROM sys.key_encryptions

结果如下：

文章图片

密码加密的对称秘钥

--创建密码加密的对称密钥 CREATE SYMMETRIC KEY SyEncryptByPwd WITH ALGORITHM=AES_256 ENCRYPTION BY PASSWORD ='0faalc23(gI0r; >'

创建对称秘钥加密的对称秘钥

--打开用来加密的对称密钥 OPEN SYMMETRIC KEY SyEncryptByPwd DECRYPTION BY PASSWORD='0faalc23(gI0r; >' --创建对称密钥加密的对称密钥 CREATE SYMMETRIC KEY SyEncryptBySy WITH ALGORITHM=AES_256 ENCRYPTION BY SYMMETRIC KEY SyEncryptByPwd --关闭用来加密的对称密钥 CLOSE SYMMETRIC KEY SyEncryptByPwd

注意：

这里我们直接使用上面创建的密码加密的对称秘钥AsyEncryptByPwd 来加密新秘钥。
创建对称秘钥加密的对称秘钥时，需要先打开用来加密的对称秘钥，才能创建

创建非对称秘钥加密的对称秘钥

--创建非对称密钥 CREATE ASYMMETRIC KEY AsyForSyEncrypt WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='0ipLMYf48w; do}?' --创建非对称密钥加密的对称密钥 CREATE SYMMETRIC KEY SyEncryptByAsy WITH ALGORITHM =AES_256 ENCRYPTION BY ASYMMETRIC KEY AsyForSyEncrypt

注意：以上几种对称秘钥创建时可用的加密算法包括DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256，但从SQL Server 2016（13.x）开始，除 AES_128 | AES_192 | AES_256三种算法外，其他算法都被废弃了。具体算法的选择可参考《SQL Server 非对称秘钥的加密算法》一文。
使用EKM创建对称秘钥
在使用EKM时，我们首先需要启用EKM provider，脚本如下：

-- 启动高级配置 sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO -- 启动EKM provider sp_configure 'EKM provider enabled', 1 ; GO RECONFIGURE ; GO

假设第三加密提供程序存储在E:\CertExeFile路径下，文件名为UDF_CLR.dll

CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov FROM FILE = 'E:\CertExeFile\UDF_CLR.dll' ; GO

消息 33027，级别 16，状态 1，第 211 行
无法加载库 'E:\CertExeFile\UDF_CLR_Interface.dll'。有关详细信息，请参阅错误日志。

文章图片

Failed to verify Authenticode signature on DLL 'E:\CertExeFile\UDF_CLR.dll'.
解决方案：
右击文件UDF_CLR→属性→数字签名→点选证书→点选右下角的“详细信息”

文章图片

在数字签名详细信息弹窗中，点击右下角的“查看证书”：

文章图片

在证书弹窗中，常规标签下，点击下面的“安装证书”：

文章图片

在证书导入向导中，选择“本地计算机（L）”，点击下一步：

文章图片

选择“将所有的证书都放入下列存储（P）”，然后点证书存储文本框右侧的“浏览”：

文章图片

在弹出的选择证书存储弹窗中，选择“受信任的根证书颁发机构”：

文章图片

点击确定后，返回证书导入向导：

文章图片

点击“下一步”

文章图片

点击完成：

文章图片

跳出“导入成功”提示弹窗，点击确定即可。
再次执行创建CRYPTOGRAPHICPROVIDER脚本，报dll缺少方法的错误：

文章图片

消息 33085，级别 16，状态 1，第 211 行
在加密提供程序库 'E:\CertExeFile\UDF_CLR.dll' 中找不到一个或多个方法。

当遇到这样的问题时，只能根据提供程序的供应商文档解决了。
使用EKM创建对称秘钥：

CREATE SYMMETRIC KEY SymFromEKM AUTHORIZATION User1 FROM PROVIDER EKM_Prov WITH PROVIDER_KEY_NAME='KeyForSensitiveData', CREATION_DISPOSITION=OPEN_EXISTING; GO

注意：CREATION_DISPOSITION参数有两个值，一个为REATE_NEW，在EKM 设备上创建一个新的秘钥，如果设备已经存在，则创建语句将出错；另外一个即实例中使用的OPEN_EXISTING，将SQL Server对称秘钥映射到一个已经存在的EKM秘钥。默认情况下，使用的是CREATE_NEW。
如需深入理解EMK，可参考《SQL Server 扩展秘钥管理（EKM）》一文。
创建临时对称秘钥
创建临时对称秘钥是在名称前加一个#号
不能使用EKM创建临时证书
临时证书为创建者所拥有，临时秘钥只对当前的会话有效。
IDENTITY_VALUE 产生一个GUID，使用它标记由新对称秘钥加密的的数据。这个标记可以用来匹配秘钥和加密数据。由特定语句生成的GUID总是相同的。在语句被使用生成GUID 后，这个语句直到至少一个会话已经调用该语句，才能重新被使用。IDENTITY_VALUE是可选选项，然而，当我们存储临时秘钥加密的数据时，我们推荐使用它。
对称秘钥的更改
对称秘钥的更改包括增加新的加密方式，或者删除已有的加密方式。值得注意的是，对称秘钥需要至少一种加密方式，因此，我们最好是现在增加加密方式，再删除已有的加密方式。
修改对称秘钥时，我们需要先打开它，如下面我们修改上文中创建的使用对称秘钥加密的对称秘钥 SyEncryptByCert

文章图片

--打开对称密钥 OPEN SYMMETRIC KEY SyEncryptByPwd DECRYPTION BY PASSWORD='0faalc23(gI0r; >' OPEN SYMMETRIC KEY SyEncryptBySy DECRYPTION BY SYMMETRIC KEY SyEncryptByPwd --增加密码加密 ALTER SYMMETRIC KEY SyEncryptBySy ADD ENCRYPTION BY PASSWORD='0ipLMYf48w; do}?' --删除对称密钥加密 ALTER SYMMETRIC KEY SyEncryptBySy DROP ENCRYPTION BY SYMMETRIC KEY SyEncryptByPwd --关闭对称密钥 CLOSE SYMMETRIC KEY SyEncryptBySy

注意：因为要修改的对称秘钥是用对称秘钥加密的，所以除了在打开要更改的对称秘钥之前，还需要打开加密它的对称秘钥。修改完成后，最后还需要关闭该对称秘钥。
删除不需要的对称秘钥
删除对称秘钥SyEncryptBySy ，脚本如下：