Always encrypted Behavior in SQL Server 2016

Solution 1:

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.

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?

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.

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.

Consider two machines:

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

Consider two users

• 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 (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.

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

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

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

• 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.

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

Since CMK is in the Windows certificate store of machineT, only userT can access the CMK, decrypt the CEK and decrypt 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.

Solution 2:

One additional and very important consideration:

The primary goals of Always Encrypted is to protect your data from malware running on the machine hosting SQL Server and from malicious high privilege users on the machine hosting SQL Server (DBAs, sys admins). If these are the attack vectors you want to address in you application, you should never provision keys for Always Encrypted on a machine hosting a SQL Server instance that contains a database with columns you want to protect. If you run a key provisioning tool, e.g. SSMS or PowerShell, on a machine hosting your instance, and the machine is compromised, an attacker can steal your keys, e.g. by scraping SSMS memory. And, of course, if you generate a certificate and put in the certificate store on the server machine, it is even easier for an attacker to get it.

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