SQL SCCM数据库服务器通过证书解密

Question

我根据本指南创建了证书和数据库主密钥

SELECT name FROM sys.symmetric_keys
##MS_DatabaseMasterKey##
SELECT name from sys.certificates WHERE name = 'BitLockerManagement_CERT'
BitLockerManagement_CERT

现在，我需要解密数据库dbo中的RecoveryKey。恢复和硬件核心密钥

OPEN SYMMETRIC KEY ##MS_DatabaseMasterKey##
DECRYPTION BY CERTIFICATE BitLockerManagement_CERT WITH PASSWORD = 'pass'

我得到错误：

不允许使用全局临时密钥。您只能使用本地临时密钥。

更新：

OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'pass'
select RecoveryKey as 'encrypted', convert(nvarchar, decryptbykey(recoverykey)) from dbo.RecoveryAndHardwareCore_Keys

返回空

Answer 1

已解决，无需通过证书解密，也无需打开主密钥：

SELECT RecoveryAndHardwareCore.DecryptString(RecoveryAndHardwareCore_Keys.RecoveryKey, DEFAULT) AS RecoveryKey FROM RecoveryAndHardwareCore_Keys