Is there a way to add database encryption on certain columns without changing code base?

Question

I've experienced a few companies that have various different codebases ranging from 20 year old code without the source anymore to 1 to 10 year old code with the source but thousands of database calls. For certain database fields the companies want to encrypt a column, for example a person's address, without needing to update all the code that calls to that table. They also want the column to still be searchable in where clauses such as "what people are located at 1st street" where the where clause is street like '%1st street%'.

Is there a way to easily perform this encryption so that SQL server handles the database encryption and decryption all in the background on the server and not have to change all the code to handle it? If not, what would be the simplest way to accomplish this that would help alleviate massive code changes, unless massive code changes is the only way?

• J.D.: Just making sure you actually mean encryption and not data obfuscation?...which is a typical business requirement as well, and is solvable through means of data masking or row-level security. The reason I ask is because of the example you mentioned for an Address column which I'd more so expect for a business to want to obfuscate as opposed to more sensitive information like SSN or CreditCardNumber type columns, where encryption is a more sensible ask IMO.
• Meyssam Toluie: Being both encrypted and searchable makes no sense. When is searchable, it means it can be a guest.

Answer 1

The best thing you could try and use is the AlwaysEncrypted feature. There are pros and cons to that - but you can effectively "turn it on" on the column and then use a cert to decrypt it. So it can be done "outside" of code and only require some changes to connection strings and use of certs.

You can read more about that feature here. It's the closest you can get without buying a third-party product or changing code.

Answer 2

As D.J. has mentioned in the comment you are probably looking for obfuscation and not encryption.

What you are looking for is Dynamic Data Masking.

With Dynamic Data Masking if the user NotAllowedUser run this query:

SELECT * FROM Employee_Financial; 

he/she will not see the data

enter image description here

• you don't need to update the code.
• you can still search with where clause is street like '%1st street%'.
• SQL Server manages the obfuscation for you based on the user.

• SolidSnake4444: Do you know if this would meet industry standards like PCI compliance in preventing personal data from being read if the database was stolen/hacked?
• Francesco Mantovani: No, this is not PCI compliant because is only "data masking". What you need for PCI compliant is encryption

Answer 3

Since both Always Encrypted (AE) and Dynamic Data Masking (DDM) was mentioned, I just want to throw in a few notes on those technologies. Since we're discussing security, we probably want to be careful to point out caveats:

The basic principle behind AE is that the key is at the client side, not accessible to the database engine (and potentially also not accessible to a DBA, for instance). Since SQL Server only has access to the encrypted values, you have limitations on what you can do in your queries. Forget about BETWEEN, manipulating data inside function etc. Secure Enclaves that came in 2019 relieves some of those, but far from all.

As for DDM, a huge aspect is that the data isn't masked for your WHERE clause. I.e., you can derive information through "clever" usage of the WHERE clause. For instance Joe Obbish "demask" 1,000,000 SSN numbers in 0.3 seconds by using some clever SQL. I.e., be very careful if the user can construct their own SQL.