“始终在线可用性”组中的数据库的列加密 [英] Column Encryption for a database in an Always On Availability group

问题描述

我们创建了一个带密码等的主密钥，如本文所示：

和
将其恢复到辅助。但是SMK是实例级配置，请测试在应用于生产环境之前，它在您的开发环境中。

 

有关详细信息，请参阅博客：

使用Always On Availability Groups进行单元级加密

 

最好的问候，

艾米丽

We created a master key with password etc, like this article:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2014

I created the master key on all 3 always on servers.

All works fine on the primary server but can't seem to find a way it will work correctly if we fail over to the secondary. When trying to just open the symmetric key with the certificate, we get this error:

Msg 15581, Level 16, State 7, Line 31
Please create a master key in the database or open the master key in the session before performing this operation.

I tried opening the master key and then opening the symmetric key:

Msg 15581, Level 16, State 7, Line 31
Please create a master key in the database or open the master key in the session before performing this operation.

This makes me suspicious, this encryption may not work when on the secondary becomes the primary...
Is there a better way to encrypt a field in Always On?

解决方案

Hi lslmustang,

 

From the Encryption Hierarchy, we can see that the Service Master Key (SMK) can automatically encrypt and decrypt Database Master Keys (DMK).

 

When you created the database master key in the primary database, the Database Master Key was encrypted by SMK of primary. The DMK was also synchronized to the secondary database. As a result, the DMK in secondary database can't be opened with its own SMK automatically.

 

As workarounds, you can open the DMK manually via its password.

 

 

 Not open the DMK manually.

 Open the DMK manually via its password.

 

Or you can backup the SMK of the primary replica and restore it to the secondary. However the SMK is an instance level configuration, please test it in your development environment before applying to production environment.

 

For more details, please refer to the blog: Cell Level Encryption With Always On Availability Groups

 

Best Regards,

Emily

这篇关于“始终在线可用性”组中的数据库的列加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！