SQL Server CLE Encryption Implementation

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!

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.

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