3

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.

2

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.

3

You are correct; SSL is the official means to support encryption for data in motion.

Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer (technet)

Always Encrypted is a bit deeper. It encrypts the data at the client application before it is transmitted, and never stores it in any unencrypted way. So the data that's encrypted via Always Encrypted is "safe" while in transit and at rest (stored as encrypted data), but the packets themselves are NOT encrypted at the network level. (MS Docs)

So if you want to prevent your DBAs and/or report writers from viewing cleartext data (such as credit card numbers or social security numbers), then Always Encrypted is a possible tool to get that level of security.

But if you don't want a packet monitoring tool to see your database username and password, or to see cleartext data as it moves from DB to application, then you must use SSL.

  • Well as Always Encrypted encrypt data at rest and in motion, you don't require SSL to encrypt your database username/password/... as it won't be sent as cleartext (If using AE on these colums) – P-L Jun 21 '18 at 19:15
2

TDE (starting from 2008) was encryption at rest, meaning whoever tries to restore your DB somewhere else without proper security management, it was impossible. Though you could still sniff the data in motion using 3rd party tools, which was another security issue that was addressed using SSL security, or IPSEC.

Introducing SQL 2016, Always Encrypted is enabled which is something you might want to consider. It basically encrypts the data at rest and in motion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.