在第 3 步中，您提到您从数据库服务器导出证书，以确保最大的安全性，永远将您的证书存储在数据库服务器.服务器不需要访问证书.

In step 3 you mention that you export the certificate from the Database Server, to ensure maximum security, never store your certificate on the Database Server. The server does not need to have access to the certificate.

如果 SysAdmin 登录(SQL 身份验证)连接到 SSMS附加参数 Column Encryption Setting=Enabled, 显示纯文本数据(期望加密数据).我的理解是，没有另一个然后应用程序用户应该看到纯文本数据).能有没有人解释一下?

If a SysAdmin login (SQL Authentication) connects to SSMS with additional parameter Column Encryption Setting=Enabled, It is shows plain text data (expecting encrypted data). My understanding is, no one other then application users should see the plain text data). Can anyone please clarify?

如果系统管理员从具有证书的客户端计算机连接到 SSMS，并且系统管理员有权访问证书，那么他们将看到纯文本数据.

If the SysAdmin is connecting to SSMS from a client machine that has the certificate and if the SysAdmin has permission to access the certificate, then they will see the plain text data.

粗略地说，Always Encrypted 提供了以下安全保证，明文数据仅对有权访问 ColumnMasterKey(证书)的实体可见

Roughly speaking, Always Encrypted provides the following security guarantee, Plaintext data will only be visible to entities that have access to the ColumnMasterKey (Certificate)

详细来说，请考虑以下场景.

To elaborate, Consider the following scenario.

考虑两台机器:

• MachineA:运行 SQL Server 的机器
• MachineT:客户端机器.

• MachineA: Machine on which SQL Server is running
• MachineT: Client Machine.

考虑两个用户

• UserA(这在技术上可以是一组用户，但为了简单起见，我将考虑单个用户的场景):谁是 MachineA的管理员>、管理SQL服务器，是SQL服务器上的SysAdmin.但是，userA 对 MachineT 没有任何类型的访问权限，UserA 应该无法解密存储在机器上的 SQL Server 中的任何加密数据A(加密数据，在此答案的上下文中是使用 SQL Server 的始终加密功能加密的数据).

• UserA (this can technically be a group of users, but I will be considering a scenario with single user for simplicity): Who is an Administrator on MachineA, managing SQL server and is SysAdmin on SQL server. However, userA does not have any kind of access to MachineT and UserA should not be able to decrypt any encrypted data stored in SQL Server on Machine A (Encrypted data, in the context of this answer is data that is encrypted using Always Encrypted feature of SQL Server).

UserT(这在技术上可以是一组用户，但为了简单起见，我将考虑单个用户的场景):是受信任的用户，可以访问 MachineT，可以访问位于 MachineA 上的 SQL Server 中的数据库 db 中的所有数据.此外，由于 userT 是可信的，他/她应该能够解密加密数据.

UserT (this can technically be a group of users, but I will be considering a scenario with single user for simplicity): Is a trusted user, has access to MachineT, has access to all data in database db which is hosted in SQL Server on MachineA. Also, since userT is trusted, he/she should be able to decrypt the encrypted data.

考虑在 MachineA 上运行的 SQL Server 具有数据库 db 和 表 t.

Consider SQL Server running on MachineA has database db and table t.

我们的目标是保护一个属于表t的列，比如ssnCol，这样只有userT应该能够看到ssnCol 明文.

Our goal is to secure a column belonging to table t, say ssnCol, such that only userT should be able to see the ssnCol in plaintext.

上述目标可以通过以下步骤实现.

The goal described above can be achieved using the following steps.

• UserT 登录到 MachineT.
• UserT 在 MachineT 中打开 SSMS.
• UserT 连接到 MachineA
上的 SQL Server
• UserT 使用 这篇文章
• 在此步骤之后，列 ssnCol 将被加密.

• UserT logs into MachineT.
• UserT opens SSMS in MachineT.
• UserT connects to SQL Server on MachineA
• UserT encrypts ssnCol in table t using the steps mentioned in the Encrypt columns (configure Always Encrypted) section of this article
• After this step, the column ssnCol would be encrypted.

当userT以上述方式加密ssnCol时，会生成两个密钥

When userT encrypts ssnCol in the manner described above, two keys are generated

• CMK:CMK 又名列主密钥是用于加密 CEK/s 的密钥.此密钥存储在 MachineT 的 Windows 证书存储中.
• CEK:CEK 又名列加密密钥是用于加密 ssnCol 的密钥，此密钥以加密形式存储在 MachineA 并且不会以明文形式保存在任何地方.

• CMK: CMK aka column master key is the key that is used to encrypt CEK/s. This key is stored in the windows certificate store of MachineT.
• CEK: CEK aka column encryption key is the key that is used to encrypt ssnCol, this key is stored in encrypted form in SQL Server on MachineA and is not persisted anywhere in plaintext.

因此，为了解密ssnCol，需要CEK，但是，为了解密CEK，需要CMK.

Hence, In order to decrypt ssnCol, CEK is required, however, in order to decrypt CEK, CMK is required.

由于 CMK 位于 machineT 的 Windows 证书存储中，因此只有 userT 可以访问 CMK、解密 CEK 和解密 ssnCol.

Since CMK is in the Windows certificate store of machineT, only userT can access the CMK, decrypt the CEK and decrypt ssnCol.

userA 是 machineA 上的管理员，也是 SQL Server 上的系统管理员，但是，由于他/她无权访问 CMK，userA 不能以明文形式访问 ssnCol.您可以通过使用 MachineA 的 SSMS，以 userA 身份登录并查询 ssnCol

userA is an administrator on machineA and also a SysAdmin on SQL Server, but, since he/she does not have access to the CMK, userA can not access ssnCol in plaintext. You can verify this by, using SSMS from MachineA, logging in as userA and querying ssnCol

如果您还有其他问题，请在评论部分提出，我可以回答.

If you have additional questions please put them in the comments section and I can answer them.