如何在SQL Server中备份对称密钥？ [英] How to backup Symmetric Key in SQL Server?

问题描述

我使用下一个代码创建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加密认证MyEncryptCert 
  

如何加密数据

  OPEN SYMMETRIC KEY MySymmetricKey证书证书MyEncryptCert 
 SET @Result = ENCRYPTBYKEY（KEY_GUID（'MySymmetricKey'），'<字符串加密>'）
关闭对称关键字MySymmetricKey 
  / pre> 
 
 
我可以备份数据库主密钥和证书。 
  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>'通过证书加密MyEncryptCert 
  
 
 
解决方案
如果您需要复制对称密钥的能力，  KEY_SOURCE 和 IDENTITY_VALUE 。您的评估是正确的，因为通过了解这两个值，您可以重新创建密钥。请注意以下代码，显示我可以使用第一个密钥加密值来证明我创建相同的密钥，删除密钥，使用相同的 KEY_SOURCE 和 IDENTITY_VALUE ，然后解密加密的值。
  CREATE SYMMETRIC KEY MySymmetricKey WITH 
 KEY_SOURCE ='< Pass1>'，
 ALGORITHM = AES_256，
 IDENTITY_VALUE ='&Pass2>'
 ENCRYPTION BY Password ='foobar！ 23'
 
打开对称密钥MySymmetricKey 
通过password ='foobar！23'解密; 
 declare @encrypted varbinary（max）; 
选择@encrypted = ENCRYPTBYKEY（KEY_GUID（'MySymmetricKey'），'我的秘密！'）; 
 
关闭对称密钥MySymmetricKey; 
 drop对称密钥MySymmetricKey; 
 
 CREATE SYMMETRIC KEY MySymmetricKey WITH 
 KEY_SOURCE ='< Pass1>'，
 ALGORITHM = AES_256，
 IDENTITY_VALUE ='&Pass2>'
加密密码='foobar！23'
 
打开对称密钥MySymmetricKey 
通过password ='foobar！23'解密; 
 
 select cast（DECRYPTBYKEY（@encrypted）as varchar（max））
 close对称密钥MySymmetricKey; 
 drop对称密钥MySymmetricKey; 
  
 
I use the next code to create SQL Encryption keys
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
How I encrypt data
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyEncryptCert
SET @Result = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), '<String to encrypt>')
CLOSE SYMMETRIC KEY MySymmetricKey
I am able to backup Database Master Key and Certificate. 
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')
But I can not backup Symmetric Key. Without it I can not decrypt the encrypted data if I move the encrypted table to another Database. 


Is there any solutions?

P.S. I tried the next code, but seems it is not safe to me, because if you know KEY_SOURCE and IDENTITY_VALUE you actually do not need original Database Master Key and Certificate to decrypt the data
CREATE SYMMETRIC KEY MySymmetricKey WITH KEY_SOURCE = '<Pass1>', ALGORITHM = AES_256, IDENTITY_VALUE = '<Pass2>' ENCRYPTION BY CERTIFICATE MyEncryptCert

解决方案
If you need to have the ability to duplicate a symmetric key, you should provide KEY_SOURCE and IDENTITY_VALUE. Your assessment is correct in that by knowing those two values, you can re-create the key. Observe the following code that shows that I can create the same key twice as is evidence by my encrypting a value with the "first" key, dropping the key, re-generating it with the same KEY_SOURCE and IDENTITY_VALUE, and then decrypting the encrypted 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;


                        
这篇关于如何在SQL Server中备份对称密钥？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！