我使用下一个代码来创建 SQL 加密密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Pass>'
CREATE CERTIFICATE MyEncryptCert WITH SUBJECT = 'Descryption', EXPIRY_DATE = '2115-1-1'
CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyEncryptCert
我如何加密数据
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyEncryptCert
SET @Result = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), '<String to encrypt>')
CLOSE SYMMETRIC KEY MySymmetricKey
我能够备份数据库主密钥和证书。
BACKUP MASTER KEY TO FILE = 'c:\temp\key' ENCRYPTION BY PASSWORD = '<Pass>';
BACKUP CERTIFICATE MyEncryptCert TO FILE = 'c:\temp\cert' WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='<Pass>', FILE='C:\temp\cert.pvk')
但我无法备份对称密钥。如果没有它,如果我将加密表移动到另一个数据库,我将无法解密加密数据。
有什么解决办法吗?
附:我尝试了下一个代码,但似乎对我来说不安全,因为如果您知道 KEY_SOURCE 和 IDENTITY_VALUE 您实际上不需要原始数据库主密钥和证书来解密数据
CREATE SYMMETRIC KEY MySymmetricKey WITH KEY_SOURCE = '<Pass1>', ALGORITHM = AES_256, IDENTITY_VALUE = '<Pass2>' ENCRYPTION BY CERTIFICATE MyEncryptCert
如果您需要能够复制对称密钥,您应该提供KEY_SOURCE
and IDENTITY_VALUE
。您的评估是正确的,因为通过了解这两个值,您可以重新创建密钥。观察下面的代码,它显示我可以创建相同的密钥两次,这可以通过我使用“第一个”密钥加密一个值、删除该密钥、使用相同的密钥重新生成它来证明。KEY_SOURCE
and IDENTITY_VALUE
,然后解密加密值。
CREATE SYMMETRIC KEY MySymmetricKey WITH
KEY_SOURCE = '<Pass1>',
ALGORITHM = AES_256,
IDENTITY_VALUE = '<Pass2>'
ENCRYPTION BY Password = 'foobar!23'
open symmetric key MySymmetricKey
decryption by password = 'foobar!23';
declare @encrypted varbinary(max);
select @encrypted = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), 'my secrets!');
close symmetric key MySymmetricKey;
drop symmetric key MySymmetricKey;
CREATE SYMMETRIC KEY MySymmetricKey WITH
KEY_SOURCE = '<Pass1>',
ALGORITHM = AES_256,
IDENTITY_VALUE = '<Pass2>'
ENCRYPTION BY Password = 'foobar!23'
open symmetric key MySymmetricKey
decryption by password = 'foobar!23';
select cast(DECRYPTBYKEY(@encrypted) as varchar(max))
close symmetric key MySymmetricKey;
drop symmetric key MySymmetricKey;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)