Always Encrypted feature in SQL server, which is a client side encryption technology designed to provided end to end encryption, meaning it will encrypt the data in the driver of your client application. This means the data will be encrypted on the network between the client and SQL server, it is encrypted in the memory of SQL server and it is encrypted on disk. If implemented correctly, it can also protect the data from an intruder that gains admin privileges on SQL server machine (& cloud administrators if your data is in cloud)
Roughly speaking, Always Encrypted provides the following security guarantee, Plaintext data will only be visible to entities that have access to the ColumnMasterKey (Certificate/AKV key/HSM based key, etc.)
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.