1

We got instruction from HQ to implement encryption on our database. The reason be, should a client gets hacked, the hackers cannot access personal info such as names, phone numbers, addresses, etc.

I have done a fair bit of research and it seems that Cell Level Encryption could be the way to got but I have some questions regarding the implementation (I am a developer, not really DBA material...).

Am I correct in saying the following:

  1. All data types, for the columns we want to encrypt, must be changed to varbinary
  2. We have to change all our SELECT statements to include DecryptByKey for encrypted columns
  3. We have to change all INSERT & UPDATE statements to include EncryptByKey for encrypted columns

Since 99% of all selects, inserts and updates in our system happen in stored procedures, how would I implement the opening and closing of the SYMMETRIC KEY? Surely, any hacker worth something, can look at the SP code to see which key was used, or just execute the SP the get the data?

Also, is there a way to determine if a column contains encrypted data? We have generic insert/update functions and these would need to know when to use EncryptByKey.

Any guidance will be greatly appreciated!

Improve this question
3
  • Your question is heavily based on the premise that column-level encryption is the correct encryption choice. Using SQL Server Always Encrypted will solve many of these questions, though may raise new questions. Did you evaluate Always Encrypted and determine it wasn't a good fit? Or did you miss it because it's a newer feature?
    – AMtwo
    Jan 28, 2021 at 13:43
  • @AMtwo, no I did not miss this feature. We have clients who are still on SQL 2008, with the majority on 2014. I am also concerned about performance, that is why TDA did not look like an option.
    – Johan
    Jan 28, 2021 at 13:53
  • @Johan: Always Encrypted is a different feature than TDE (and it does require a few changes in the client application).
    – Razvan Socol
    Jan 28, 2021 at 16:04

2 Answers 2

Reset to default
1

Based on your requirements, I would actually recommend Transparent Data Encryption (TDE) instead of CLE.

TDE encrypts at the database level and therefore you don't have to worry about being compromised by storing the decryption process in a stored procedure. Rather, TDE works on the traditional private and asymmetric key pattern to prevent access to the unencrypted data without the proper key (which you can handle authenticating outside of the database to decouple the security concerns).

It's also better in my opinion because it doesn't force you to change your data types to VARBINARY which almost certainly could affect the performance of your queries. Rather it keeps everything as you designed under the hood (hence Transparent) when you access your database.

Improve this answer
6
  • thanks for the reply. I am very concerned about performance, we already have issues at large clients. What would the impact be of TDE vs CLE? Going the TDE route, what changes would I need to make to my client app?
    – Johan
    Jan 28, 2021 at 13:59
  • @Johan If you're concerned about performance then I'd carefully research into CLE and performance implications before implementing it, as VARBINARY is certainly not a performant data type to index on, or create predicates against. While I haven't personally used CLE, knowing how the SQL Engine works, I'd be weary of that drawback. My understanding (though again I've only looked into it in theory, haven't in practice) is your client app would have minimal changes with TDE as you'd only need a way to authenticate with the database via the public key from your app.
    – J.D.
    Jan 28, 2021 at 14:06
  • TDE requires no changes to the client application, but does not bring any security benefits if the client application is hacked. It helps if the hard drive of the server is stolen (an unlikely scenario in my opinion) or the MDF/LDF files are copied by a hacker from the server.
    – Razvan Socol
    Jan 28, 2021 at 16:01
  • 1
    TDE has minimal performance impact and requires no application changes.
    – David Browne - Microsoft
    Jan 28, 2021 at 16:03
  • OP mentions the goal is to protect against a client that gets "hacked." If a bad actor gains access to credentials in a way that allows connecting to the instance, TDE doesn't protect against that attack vector. TDE really only protects against stolen data files or backups or disk-level access.
    – AMtwo
    Jan 29, 2021 at 0:19
1

Based on our application architecture (15 year old multi-layer COM+ where everything is done in SPs), not one of the 3 options will satisfy our requirements.

CLE:

  • Code to open the SYMMETRIC KEY will have to be inside the SP, which will make it accessible to anybody
  • It will take months to change all SPs to implement DecryptByKey/EncryptByKey and opening/closing of key

TDE

  • Probably our best option since no client changes are required and does provide some level of protection
  • Does not provide any security if hackers gain access to machine (see comments from Razvan and AMtwo)

Always Encrypted

  • Only available with SQL Server 2016 and later (we have a lot of clients on earlier versions)
  • Encryption occurs client-side via Always Encrypted enabled client driver, therefore it won't work with SPs
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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