I'm working on a Web App where the data is quite sensitive and is required to be encrypted before it is stored in the database. Now there are two ways through which I can achieve this:Encrypt data in the codeEncrypt data in stored procedures (using symmetric keys)Now I don't know which approach will be better regarding security and performance.
Encrypt data in code or stored procedures?
sql server;encryption;stored procedures;asp.net mvc5
The concern isn't so much about where you perform encryption/decryption operations, but how you are managing keys, encrypted data, and how you actually go about the encryption itself.The immediate concern I have with doing this through the use of stored procedures is that the key will most easily be accessed from within the database tier.  This presents a vulnerability in that your database now presents as a component that if it alone becomes compromised, then your sensitive data is almost assuredly compromised as well.The above is a REAL vulnerability that will prevent you from achieving standards compliance across a number of different standards (Eg. HIPAA, PCI, etc..)!Ultimately the weakest point in a symmetric encryption scheme is weakness in how keys are managed.  Some of the most secure (and expensive) solutions utilize something called an HSM that will securely store keys and provide them as needed to applications in a secure way.Other ways you can solve this problem is by encrypting the channel between your application and database servers, and requiring the application servers to provide the key to stored procedures for encryption/decryption.  This is a good solution because an attacker that compromised the database would not have access to the key, or if the attacker compromised the application server then they would not have access to the database (theoretically).  This prevents a single point of failure.