SQL Server – End to End Encryption Options

encryptionsql serversql server 2014sql-server-2016

I'm familiar with database encryption options for SQL Server such as TDE and ensuring backups / backup locations are encrypted and secured properly.

I'm doing some work for a new client who has asked me to detail options for end to end encryption, in other words, to ensure data sent from database server to application server remains encrypted en route and cannot be intercepted (or rather, interpreted) by any sort of packet monitoring.

I know about using SSL certificates to encrypt connections to the database, though it is something I have seldom configured. I really don't know if that is an adequate option for end to end encryption as surely this cannot be configured using SQL Server alone?

In my mind there has to be something at the application end, or on the network route that enables end to end encryption like this?

My knowledge of network security is basically limited to firewall rules and general routing, so I'm looking for options available to me to either research further or put into practice.

I wary that this question will be closed because it is opinion based, so I'm really asking for solid options within SQL Server itself (or within the remit of a DBA) to configure end to end encryption of data.

Best Answer

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.

Related Question