Always encrypted behavior in SQL Server 2016

Question

I did a demo in SQL Server 2016 for the topic Always Encrypted. You have little doubt. Following are the following steps:

In the database server (hosted in Microsoft Azure VM):

• Column Encryption Key (CEK) and Master Encryption Key (CMK) created in MyTable table
• Select * from MyTable , shows encrypted data (both from the application server and from the database)
• Export a certificate from a database server
• Imported certificate into Application Server (my local computer)
• Added Column Encryption Setting=Enabled to the connection string of my application.
• It works fine, now it shows plain text data as expected.

Doubt:

In the database server (in MS Azure VM), if SysAdmin login (SQL Authentication) is connected to SSMS with the optional Column Encryption Setting=Enabled parameter, it displays text data (waiting for encrypted data). I understand that no one except those who use the application does not see text data . Can anyone clarify?

Answer 1

In step 3, you specify that you export the certificate from the database server to ensure maximum security; never store the certificate on the database server. The server does not need to have access to the certificate.

If SysAdmin login (SQL Authentication) is connected to SSMS using an additional parameter. Encryption Encryption Setting = Enabled. It shows text data (waiting for encrypted data). I understand that there is no other, then users of the application should see the plain text data). Can someone clarify?

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

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

---

To develop, consider the following scenario.

Consider two cars:

• MachineA : machine running SQL Server
• MachineT : client machine.

Consider two users

• UserA (this may be technically a user group, but I will consider a single-user scenario for simplicity): who is the administrator on MachineA manages the SQL server and SysAdmin on the SQL server. However, userA does not have any access to MachineT, and UserA must not decrypt any encrypted data stored in SQL Server on machine A (the encrypted data in the context of this answer is data that is encrypted using SQL Server's Always Encrypted function).

• UserT (this may be technically a user group, but I will consider a single-user scenario for simplicity): does the trusted user have access to MachineT, has access to all the data in the db database hosted in SQL Server on MachineA. In addition, since user T is trusted, he / she should be able to decrypt the encrypted data.

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

Our goal is to provide columns belonging to table t, for example ssnCol , so that only userT should be able to see ssnCol in clear text.

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

• UserT registers with MachineT.
• UserT opens SSMS in MachineT.
• UserT connects to SQL Server on MachineA
• UserT encrypts ssnCol in table t using the steps in Encrypt columns (configure Always Encrypted) in this article.
• After this step, the ssnCol column will be encrypted.

When userT encrypts ssnCol as described above, two keys are generated

• CMK : the key key CMK aka column is the key used to encrypt CEK / s. This key is stored in the Windows MachineT certificate store.
• CEK : CEK security key aka column is the key used to encrypt ssnCol, this key is stored in encrypted form in SQL Server on MachineA and is not stored anywhere in plain text.

Therefore, to decrypt ssnCol, CEK is required, however, to decrypt CEK, CMK is required.

Since the CMK is located in the Windows machineT certificate store, only user T can access the CMK, decrypt the CEK, and decrypt ssnCol.

userA is the administrator on machineA, as well as SysAdmin on SQL Server, but since he / she does not have access to the CMK, userA cannot access ssnCol in clear text. You can verify this using SSMS from MachineA, registering as userA and requesting ssnCol

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

Answer 2

Another very important consideration:

The main goals of Always Encrypted are to protect your data from malware running on the computer hosting SQL Server and from users of malicious high-level users on the computer hosting SQL Server (database administrators, sys administrators). If these are attack vectors that you want to address in your application, you should never provide the keys for Always Encrypted on the machine hosting the instance of SQL Server that contains the database with the columns that you want to protect. If you run the key preparation tool, for example. SSMS or PowerShell, on the computer where your instance is located and the machine is compromised, an attacker could steal your keys, for example. by clearing SSMS memory. And, of course, if you create a certificate and put it in the certificate store on the server machine, it will be even easier for an attacker.

Please refer to https://msdn.microsoft.com/en-us/library/mt708953.aspx#SecurityForKeyManagement for more information and useful recommendations.