ASP.NET - Data Encryption & Decryption framework designs

Question

I have the following app scenario where i would need to implement the cell-level encryption.

• Database - MS SQL Server
• Web Technology - ASP.net MVC

Existing app scenario:- Currently the web application is serving multiple clients and the app is attached to multiple db's such as ClientX, ClientY, ClientZ etc. All these client db's are having the same schema's but different data.. All these client db's are residing on our database end only.

Now there is a table present in all the client DB's known by the name Customer and it has some important columns such as TaxId, amount etc. Now i need to encrypt these columns and also have to perform decryption on-demand from the new UI page.

I am going to utilize the Symmetric key with the Certificates to handle the encryption for these specific columns.

Please suggest the framework design approach.

My major concerns are:-

• Do i have to implement data encryption with the same key in all different client DB's ? Which means to have to create the symmetric key, certificate in all the client db's.
• The encrypted data would be shown in the various report sProcs and pages across the application.
• How effectively can we do the key management area in the project implementation ?
• If the decryption request is happening very often or frequently, should i saving the decrypted data somewhere in the new table and use it for the app.

Answer 1

Since you are using a symmetric key, both client and server must use the same key.

The key exchange is always a concern, in addition to how you store it in the client.

I wouldn't store the decrypted data somewhere in your db, that will defeat the main purpose.

You can also move decryption to an api between your dB and clients. It will save you from storing the keys in all clients.