April 28, 2020 at 6:47 am
Hi Experts ,
we want to encrypt sensitive information like ssn ,tax_id , phone no and address we are thinking of using column level encryption . But there are number of stored procedures which use this tables around 300 stored procedures few of them perform insert option and most of them retrieve information from these tables from front end application. Is it possible to implement column level encryption in this scenarion.
Note :- we will not be able to use "Always encryption" as the tables containing sensitive information may be populated through back end and not from application.
April 28, 2020 at 2:53 pm
You can, but this requires code changes. You need to open keys, encrypt/decrypt data, and then run your code.
May 4, 2020 at 4:15 am
You want to edit all of those procedures. For the sake of the life of the system. Do encryption the right way.
However...
You can open keys in a trigger.
You would probably want to look into INSTEAD OF INSERT, UPDATE... but that means you need to do the same things the same way from that day forward. you are letting your Trigger encrypt it, so sending encrypted data from newer procedures is a no. That data must be in the same format as the 300 others.
If you want to decrypt in a view, instead of editing your select procedures... you might be adding extra steps to your data, and not security to it. But again, it is possible using a non password based key and DECRYPTBYKEYAUTOASYMKEY.
The best way to approach this is to just query sys.procedures for OBJECT_DEFINITION(object_id) like 'your table name' so you can see how much work you have... to do it the best way for the system.
MCSA, Data Architect
SQL Master Data Management
https://youtube.com/elricsims-dataarchitect
9001st Normal Form
May 4, 2020 at 8:57 pm
You state that 300 stored procedures access that sensitive data. This seems like a great opportunity to review whether those stored procedures should access it. In some cases the answer will be yes, but in many cases the answer will be no and this will allow you to lock down access to sensitive data.
I agree with elric.sims, we use instead of triggers and encrypt the data as it is written to the server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply