在第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身份验证）使用
附加参数Column Encryption Setting = Enabled连接到SSMS，则显示
纯文本数据（期望加密的数据）。我的理解是，没有
，那么应用程序用户就应该看到纯文本数据）。
任何人都可以澄清吗？

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?

如果SysAdmin从具有证书的客户端计算机连接到SSMS，并且SysAdmin有权访问证书，然后他们将看到纯文本数据。

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.

总的来说，始终加密提供以下安全保证，纯文本数据仅对有权访问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 Server上的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 ，可以访问数据库 db 托管在 MachineA 上的SQL Server中。另外，由于 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 使用表t中提到的步骤对表t中的 ssnCol 进行加密加密列（配置始终加密）部分-always-encrypted rel = nofollow noreferrer>本文


• 在此步骤之后，列 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 aka列主密钥是用于加密CEK / s的密钥。此密钥存储在 MachineT 的Windows证书存储区中。


• CEK ：CEK aka列加密密钥是所使用的密钥为了加密 ssnCol ，此密钥以加密形式存储在 MachineA 上的SQL Server中，并且不会以纯文本形式持久保存。

• 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上的SysAdmin，但是，由于他/她确实没有访问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.