有时，我们需要在故障转移后手动打开加密密钥，而有时则不需要 [英] Sometime we need to open encryption key manually after failover and sometime not

问题描述

我已使用url的本机还原将数据库从本地SQL Server迁移到托管实例，并为其配置了故障转移组.我已经在主数据库和辅助数据库上都打开了加密密钥，但是仍然需要在故障转移后重新打开加密密钥.

解决方案

问题是数据库主密钥的解密是基于服务主密钥的，这在您的主实例和辅助实例之间是不同的(而且我还没有这样做)在Azure SQL托管实例中找到了一种在实例之间备份和还原该SMK的方法-非Azure托管SQL Server允许这样做).因此，使用当前的方法，只有在运行OPEN MASTER KEY ... ALTER MASTER KEY ...语句时，才能在主实例上解密，而在次实例上，它将失败.

答案是什么?根据 https://stackoverflow.com/a/66467547/1450351 ，您需要在辅助实例的主数据库(如果数据库主密钥的解密不能与服务主密钥一起使用，则它将查找数据库主密钥密码-这样，就不需要在服务主密钥上使用相同的密码了)两个实例)，例如

  EXEC sp_control_dbmasterkey_password @db_name = N'MyDatabaseWithCLE'，@password = N'XX我的主密码XX'，@action = N'add'; 

I have migrated my database from on premises SQL server, using native restore from url, to managed instance and configured failover group to it. I have opened encryption key on both primary and secondary database but still sometime need to re open encryption key after fail over.

解决方案

The problem is that the decryption of your database master key is based off your service master key - which is different between your primary and secondary instance (and I haven't found a way in Azure SQL Managed Instance to backup and restore that SMK between instances - non-Azure hosted SQL Server allows this). So with your current approach you'll only be able to decrypt on the primary instance when you've run your OPEN MASTER KEY... ALTER MASTER KEY... statements... and on the secondary instance it will fail.

What's the answer? As per https://stackoverflow.com/a/66467547/1450351 you need to add a database master key password on the master database of the secondary instance (if the decryption of the database master key doesn't work with the service master key, it looks for a database master key password - that way it didn't need the service master key to be the same on both instances) e.g.

EXEC sp_control_dbmasterkey_password @db_name = N'MyDatabaseWithCLE',   
    @password = N'XX MY MASTER KEY PASSWORD XX’, @action = N'add';

这篇关于有时，我们需要在故障转移后手动打开加密密钥，而有时则不需要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！