故障转移组辅助实例Azure SQL托管实例CLE的解密问题

Question

我们有一个带有主实例和辅助实例的Azure SQL托管实例故障转移组设置--我遇到的问题是，我们对一些数据库表列使用单元格(列)级加密(CLE)。我有限的理解是，这些密钥的解密取决于服务主密钥。我认为问题是数据库主密钥使用服务主密钥加密，然后数据库在实例之间进行同步，但是同步不会对服务器(实例)级别的数据(即服务主密钥…)产生影响。因此，在主实例上，数据可以解密，但是在故障转移实例上，它不能解密。因此，您会得到这样的错误：

请在执行此操作之前在数据库中创建主键或在会话中打开主键。

如果我在我的用户数据库上运行下面的SQL，它将修复这个问题，直到故障转移，此时我需要再次运行它。因此，从故障转移的角度来看并不理想，这也意味着我不能将次要实例用作只读实例。

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘XXX’ 
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘XXX'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

下面是我唯一能找到的描述这个问题的文章(滚动到末尾，上面写着“在新的主副本中解密数据”)，它通过从主实例中备份服务主密钥并将其还原到次要实例来解决这个问题，但是它是一个前提上的设置与我们的Azure设置，问题是--我不知道如何(或者如果可能的话)在Azure中备份和恢复服务主密钥。

https://www.sqlshack.com/column-level-sql-server-encryption-with-sql-server-always-on-availability-groups/

我确实尝试过从主实例备份服务主键，以便将其还原到辅助实例，但我看不到在Azure SQL托管实例https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql?view=sql-server-ver15…中执行此导出的方法。我试着给它blob存储位置，这有点牵强，但它不喜欢它：

BACKUP SERVICE MASTER KEY TO FILE = 'https://ourstorage.blob.core.windows.net/database-backups/service_master_key.key' ENCRYPTION BY PASSWORD = 'YYYY';

Msg 3078、级别16、状态2、第69行文件名"https://pptefsaaseprd.blob.core.windows.net/database-backups/ase_prod_service_master_key“作为指定设备类型的备份设备名称无效。使用有效的文件名和设备类型重新发出BACKUP语句。

我听说过可能使用Azure Key Vault代替，但找不到任何例子，理想情况下不想导致代码/sql的任何破坏更改。

为了提供更多的上下文，我们当前的存储过程执行如下操作：

       OPEN SYMMETRIC KEY SSN_Key_Surname
          DECRYPTION BY CERTIFICATE Surname;
 
       /* SQL making use of the decrypted column */
 
       CLOSE SYMMETRIC KEY SSN_Key_Surname;

所以这就是我现在的处境。希望我只是错过了一个简单的步骤--这不是一种罕见的情况吗？例如，如果您在故障转移组中有Azure SQL托管实例，并使用列级加密(其中数据库主密钥由服务主密钥加密)，那么如何配置数据以便在主实例和次要实例上都解密？

--我想要让它起作用，您需要能够从主实例备份服务主密钥并将其还原到次要实例--这在Azure?中是可能的吗？

Answer 1

正如预期的那样，我只是遗漏了一个简单的步骤，如这里所描述的，https://stackoverflow.com/a/58228431/1450351

数据库主密钥(DMK)是用服务主密钥(SMK)加密的，这是每个Service实例所特有的，您希望这样做。

Server有一种解密DMK的替代方法。如果DMK无法使用SMK解密，它将在凭据存储区中搜索与同一家族GUID匹配的密码。如果它找到一个与数据库匹配的系列GUID，它将尝试用存储的密码解密DMK。如果成功，那么它将使用DMK加密或解密数据库中的凭据或密钥。

因此，使用sp_control_dbmasterkey_password将存储一个基于数据库名和密码的家族GUID，该密码对主数据库中的DMK进行解密。

为了确保DMK在AG从主服务器到辅助服务器失败时工作，在辅助服务器上运行sp_control_dbmasterkey_password，作为将数据库连接到AG的过程的一部分。

因此，在次要实例上，我不得不在主 DB上运行这个

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