sql - 执行此操作之前，请在数据库中创建主 key 或在 session 中打开主 key

Question

尝试恢复加密备份时，即使副本具有从生成备份的原始/主服务器恢复的主 key (dmk)、服务主 key 、证书和私钥，我也会在辅助副本上收到以下错误。

Msg 15581, Level 16, State 7, Line 137
Please create a master key in the database or open the master key in the session before performing this operation.
Msg 3013, Level 16, State 1, Line 137
VERIFY DATABASE is terminating abnormally.

为了避免该错误，我围绕这样的操作打开和关闭主 key 。但是，在主数据库上，我不需要打开和关闭主 key 来执行操作。

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyTest!M4st3rPass';
RESTORE VERIFYONLY FROM DISK = '\\FS1\SqlBackups\SQL01\SystemDbs\msdb_backup_2017_09_22_171915_6346240.bak' WITH FILE = 1, NOUNLOAD, NOREWIND;
CLOSE MASTER KEY ;

我相信这是因为主数据库具有带有加密指纹的备份历史记录，但我想知道是否缺少与辅助数据库相关的其他内容。

但是，毕竟，由于证书在辅助节点上恢复，我将其分配给备份加密的 SystemsDB 备份维护计划选项，但如果我出于同样的原因选中“验证”选项，则作业会失败。

Source: Back Up Database Task
Executing query "BACKUP DATABASE [master] TO  DISK = N'\\FS1\SqlBac...".: 50% complete
End Progress  
Error: 2017-09-22 17:08:09.28
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
**Description**: Executing the query "declare @backupSetId as int  select @backupSetId =..." 
failed with the following error: "Please create a master key in the database or open the master key in the session before performing this operation.
VERIFY DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error 

Answer 1

已修复。

引用:https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

这一段泄露了它:

The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

在我的辅助节点上运行以下命令。

1. 删除证书...
2. 删除主 key
3. 创建主 key ...
4. 从文件创建证书...

检查后得出解决方案。

--on primary, output: master 
select name from sys.databases where is_master_key_encrypted_by_server=1

--on secondary, output: nothing...
select name from sys.databases where is_master_key_encrypted_by_server=1

所以我想如果我能让主 key 默认由服务主 key 加密，那么这将自动解密。

--on secondary
drop certificate [BackupCertWithPK]
drop master key

--Skipped restore master key from file.
--Instead, I ran create master key with password.
create master key encryption by password = 'MyTest!Mast3rP4ss';

--verify by open/close.
open master key decryption by password = 'MyTest!Mast3rP4ss';
close master key;

--proceed to restore/create cert from file.
create cerfiticate [BackupCertWithPK] 
from file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.cer' 
with private key (file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.key' , decryption by password = 'key_Test!prim@ryP4ss') ; 

再次运行上述选择后。

--on secondary, output: master, now there was hope again!
select name from sys.databases where is_master_key_encrypted_by_server=1

最后，我成功地使用验证和加密选项设置重新运行了备份作业。验证步骤没有失败，也没有提示打开/关闭主 key 。

以下内容按预期工作，无需打开/关闭主 key 。

RESTORE VERIFYONLY FROM DISK = '\\FS1\SqlBackups\SQL01\SystemDbs\msdb_backup_2017_09_22_171915_6346240.bak' WITH FILE = 1, NOUNLOAD, NOREWIND;

哇哦!任务完成。