我恢复了主密钥,然后使用以下命令创建了证书和对称密钥 SSMS:
use AdventureWorks2022
DROP MASTER KEY
RESTORE MASTER KEY
FROM FILE = 'c:\stuff\master_key_backup.mas'
DECRYPTION BY PASSWORD = 'ASDF$E£%^IJYK*&234'
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'
OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
-- create new certificate and symkey to encode a new column
CREATE CERTIFICATE Certificate_test2 WITH SUBJECT = 'Protect my second data';
CREATE SYMMETRIC KEY SymKey_test2 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test2;
然后我使用以下命令加密表中的一列:
open symmetric key SymKey_test2 decryption by certificate Certificate_test2
update AdventureWorks2022.[Person].[EmailAddress]
set email_encrypt = ENCRYPTBYKEY(key_guid('symkey_test2'), emailaddress)
from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2
并测试了它是否可以与以下设备配合使用:
open symmetric key symkey_test2 decryption by certificate certificate_test2
select convert(nvarchar, decryptbykey(email_encrypt)) as 'decrypted email',*
from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2
我把有效的代码放入新脚本中,但是出现了错误:
Please create a master key in the database or open the master key in the session before performing this operation.
如果我包含以下行,我可以使其工作:OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
,但我不想在将在存储过程和 Excel 数据调用中调用的新脚本中暴露我的密码。
有没有办法打开主密钥而不暴露密码?最终目标是在由 excel 数据连接调用的存储过程中进行设置,但当然,我不希望最终用户能够访问主密码。
我认为你应该将主密钥设置为自动打开
恢复后您应该执行一次此操作。