故障转移组的辅助实例上Azure SQL托管实例CLE的解密问题 [英] Decryption issue with Azure SQL Managed Instance CLE on secondary instance of a failover group

问题描述

我们有一个包含主实例和辅助实例的Azure SQL托管实例故障转移组设置–我遇到的问题是，我们对某些数据库表列使用了单元(列)级加密(CLE).我有限的理解是，这些解密取决于服务主密钥.我认为问题在于数据库主密钥已使用服务主密钥加密，然后数据库在实例之间进行同步，但是同步不会处理服务器(实例)级别的数据，即服务主密钥…所以在主数据库上实例可以解密数据，但在故障转移实例上不能解密.因此，您会收到如下错误:

We have an Azure SQL Managed Instance Failover Group setup with a primary and secondary instance – the issue I’m hitting is that we use cell (column) level encryption (CLE) for some of our database table columns. My limited understanding is that the decryption of these depends on the service master key. I think the issue is that the database master key gets encrypted with the service master key and then the databases get synchronised between instances but synchronisation won’t do the server (instance) level data i.e. Service Master Key… so on the primary instance the data can be decrypted but on the failover instance it can’t. Hence you get an error like this:

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

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

如果我在用户数据库上运行以下SQL，它将解决该问题，直到我进行故障转移为止，此时，我将需要再次运行它.因此，从故障转移的角度来看并不理想，这也意味着我不能将辅助实例用作只读实例.

If I run the below SQL on my user database it will fix the issue until I failover, at which point I’ll need to run it again. So not ideal from a failover perspective and also means I can’t use the secondary instance as a readonly instance.

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设置，问题是我不知道如何(甚至可能)在Windows Server 2003中备份和还原服务主密钥Azure .

Below is the only article I could find describing the problem (scroll towards end where it says "Decrypt data in the new primary replica"), and it solves the problem by backing up the service master key from the primary instance and restoring it to the secondary instance, but it's an on-premise setup vs our Azure setup, and the issue is I don’t know how (or if its even possible) to do a backup and restore of the service master key in Azure.

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

我确实尝试从主实例备份服务主密钥，以便可以将其还原到辅助实例，但是我看不到在Azure SQL托管实例中执行此导出的方法-

I did try and backup the service master key from the primary instance so I could restore it to the secondary instance but I could not see a way to do this export in an Azure SQL Managed Instance - https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql?view=sql-server-ver15 … I tried giving it blob storage location which was a bit of a stretch and it didn’t like it:

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语句.

Msg 3078, Level 16, State 2, Line 69 The file name "https://pptefsaaseprd.blob.core.windows.net/database-backups/ase_prod_service_master_key" is invalid as a backup device name for the specified device type. Reissue the BACKUP statement with a valid file name and device type.

我听说过有人提到可能改用Azure Key Vault，但找不到任何示例，理想情况下是不想对代码/sql进行任何重大更改.

I’ve heard mention of perhaps using Azure Key Vault instead but couldn’t find any examples and ideally don’t want to cause any breaking changes to code/sql.

要提供更多背景信息，我们当前的存储过程应执行以下操作:

To give some more context our current stored procedures do something like the following:

       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托管实例，并且具有列级加密(其中数据库主密钥由服务主密钥加密)，您如何配置事物以便可以在主实例和辅助实例上解密数据?

So that’s where I’m at. Hopefully I’m just missing a simple step – surely this is not an uncommon scenario? i.e. if you have Azure SQL Managed Instances in a failover group, with column level encryption where the database master key is encrypted by the service master key, how do you configure things so data can be decrypted on both primary and secondary instance?

我想为此工作，您需要能够从主实例备份服务主密钥并将其还原到辅助实例-在Azure中可以做到这一点吗?

推荐答案

正如我所料，我只是错过了一个简单的步骤，如此处所述 https://stackoverflow.com/a/58228431/1450351

As expected I was just missing a simple step as described here https://stackoverflow.com/a/58228431/1450351

数据库主密钥(DMK)使用服务主密钥(SMK)加密，该密钥对于每个SQL Service实例都是唯一的，并且您希望以此方式进行操作.

The Database Master Key (DMK) is encrypted with the Service Master Key (SMK) which is unique to each SQL Service instance and you want it this way.

SQL Server有另一种解密DMK的方法.如果DMK无法用SMK解密，它将在凭证存储区中搜索与相同家族GUID匹配的密码.如果找到与您的数据库匹配的家族GUID，它将尝试使用存储的密码解密DMK.如果成功，它将使用DMK加密或解密数据库中的凭据或密钥.

SQL Server has an alternate way of decrypting the DMK. If the DMK cannot be decrypted with the SMK, it searches the credential store for a password that matches the same family GUID. If it finds a family GUID that matches your database it will attempt to decrypt the DMK with the stored password. If that succeeds then it will use the DMK to encrypt or decrypt credentials or keys within the database.

因此，使用 sp_control_dbmasterkey_password 将基于数据库名称和解密DMK的密码在主数据库中存储家族GUID.

So the use of sp_control_dbmasterkey_password will store a family GUID based on the database name and the password decrypting the DMK in the master database.

要确保当AG从主服务器故障切换到辅助服务器时DMK能够正常工作，在辅助服务器上运行sp_control_dbmasterkey_password作为将数据库加入AG的过程的一部分.

To ensure that the DMK works when a AG fails from the primary to a secondary, run the sp_control_dbmasterkey_password on the secondary as part of your process to join an database to an AG.

因此，在辅助实例上，我必须在 master 数据库

So on the secondary instance I had to run this on the master DB

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

这篇关于故障转移组的辅助实例上Azure SQL托管实例CLE的解密问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！