SQL 执行此操作前，请在数据库中创建主密钥或在会话中打开主密钥

Please create a master key in the database or open the master key in the session before performing this operation

提问by Hiram

I get the following error on secondary replicas when trying to restore an encrypted backup even though the replica has the master key (dmk), service master key, certificates and private keys restored from the originating/primary server that generated the backup.

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

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.

To circumvent the error I open and close the master key around the operation like such. However, on the primary, I don't need to open and close the master key to do the operation.

为了规避错误，我在这样的操作周围打开和关闭主密钥。但是，在主节点上，我不需要打开和关闭主密钥来进行操作。

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 ;

I believe this is because the primary has the backup history with the encryption thumbprint, but I am wondering if I am missing something else related to the secondaries.

我相信这是因为主服务器具有带有加密指纹的备份历史记录，但我想知道我是否遗漏了与辅助服务器相关的其他内容。

However, after all, since the cert is restored on the secondaries I assign it to the SystemsDB Backup Maintenance Plan options for Backup Encryption, yet the job fails if I keep the Verify option checked for the same reason.

但是，毕竟，由于证书是在辅助节点上恢复的，我将它分配给备份加密的 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 

采纳答案by Hiram

Fixed.

固定的。

Referenced: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

参考：https: //docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

This paragraph gave it away:

这一段给了它：

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

每当 DMK 更改时，存储在主系统数据库中的 DMK 副本都会以静默方式更新。但是，可以使用ALTER MASTER KEY语句的DROP ENCRYPTION BY SERVICE MASTER KEY选项更改此默认值。未由服务主密钥加密的 DMK 必须使用OPEN MASTER KEY语句和密码打开。

Ran the following on my secondary nodes.

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

1. Drop Certificate...
2. Drop master key
3. Create master key...
4. Create certificate from file...

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

Arrived at the solution after checking this.

检查后得出解决方案。

--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

So I figured if I could get the master key to be encrypted by default by the service master key then this would automate the decryption.

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

--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') ; 

After this ran the above select again.

在此之后再次运行上面的选择。

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

Finally, I re-ran my backup job with options set for Verify and Encryption successfully. Verify step did not fail nor prompted to open/close the master key.

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

The following simply worked as intended without needing to open/close the master key.

以下只是按预期工作，无需打开/关闭主密钥。

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

Wohooo! Mission accomplished.

哇哦！任务完成。

回答by Borogal

I had the same situation, but instead of recreating the MDK I ran the following to fix the issue: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

我遇到了同样的情况，但我没有重新创建 MDK，而是运行以下命令来解决问题：ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

回答by ConstantineK

I am not certain if this is exactly what you are looking for, but the OPEN MASTER KEY remarks had something that seemed relevant.

我不确定这是否正是您要查找的内容，但 OPEN MASTER KEY 的评论似乎有些相关。

You will 100% want to test this not in production, but it seems that once the master key has been opened, you have the option to not require that with the ALTER MASTER KEY REGENERATEcommand.

您将 100% 想在不在生产环境中进行测试，但似乎一旦打开了主密钥，您就可以选择不使用ALTER MASTER KEY REGENERATE命令来要求它。

If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server.

You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK).

When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

如果数据库主密钥是用服务主密钥加密的，当需要解密或加密时会自动打开。在这种情况下，没有必要使用 OPEN MASTER KEY 语句。

首次将数据库附加或还原到 SQL Server 的新实例时，数据库主密钥（由服务主密钥加密）的副本尚未存储在服务器中。

您必须使用 OPEN MASTER KEY 语句来解密数据库主密钥 (DMK)。解密 DMK 后，您可以选择在将来启用自动解密，方法是使用 ALTER MASTER KEY REGENERATE 语句为服务器提供使用服务主密钥 (SMK) 加密的 DMK 副本。

当数据库从较早版本升级后，应重新生成 DMK 以使用较新的 AES 算法。有关重新生成 DMK 的详细信息，请参阅 ALTER MASTER KEY (Transact-SQL)。重新生成 DMK 密钥以升级到 AES 所需的时间取决于受 DMK 保护的对象数量。重新生成 DMK 密钥以升级到 AES 只需要一次，并且作为密钥轮换策略的一部分对未来的重新生成没有影响。

https://docs.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql