December 20, 2013 at 8:30 am
I'm implementing encryption on a few columns in a database table and have no issue with the creation of the DMK, certificate, symmetric key, and the encryption/decryption of the data. However, I'm just a bit confused on the best way to go about implementing this with our application/website. If I were to create a stored procedure that would open the symmetric key and return my result set with all of the data decrypted, wouldn't this defeat the purpose of encrypting the data in the first place if an unauthorized user is able to gain access to the database and execute the stored procedure? Would it be better to have the stored procedure only return a single record? I'm guess I'm just having difficulty in understanding how to properly implement the data retrieval for the front-end application/website.
December 21, 2013 at 12:40 pm
You raise some valid questions there. Indeed, if it is possible to retrieve the encrypted data by running a stored procedure, you only have security by obscurity.
I can see three ways to go. One is to use passphrases to protect the keys or the data (with encrypt/decryptbypassphrase) and send in the passphrase from the application. At least you need two components to get to the data.
A second option is provide the encryption key with a hardware device plugged into the server. This solution requires physical access to the server to remove/attach the key device. This works well for a laptop database, but alas EKM is only available in Enterprise Edition which you don't run on a laptop.
The last option is to encrypt client side. This is likely to be the most secure, but also means that you cannot perform any server-side analysis of the data.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 23, 2013 at 8:19 am
Hi Erland,
Thank you for your reply. That makes sense on the "Security by Obscurity" as I wasn't too sure how using EncryptByKey/DecryptByKey would be used in any other way. I'll likely look into using the EncryptByPassphrase/DecryptByPassphrase with the client-side having the key stored somewhere "safe".
December 23, 2013 at 1:13 pm
What I did not mention, but which you should be aware of is that a solution where the application sends the password to the database is open for a man-in-the-middle attack. That is, someone plugs in a network listener and sniffs the data. The easiest network listener to use is Profiler, but if you call the parameter @password, it will not display the data. But that will not stop a rogue network adminstrator or DBA who wants to steal the data.
Whether you really need to be concerned about this, depends how sensitive the data is, and what the requirements are from the stakeholders. It may be perfectly OK to trust your employees, but I thought I should make you aware of the risk.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 26, 2013 at 7:55 am
Thank you for the follow-up and the additional note regarding the "Man in the Middle Attack". Any outside transmission of the data will be sent via SSL so I'm hoping this helps mitigate the ability for others to eavesdrop on the external communications. As for the internal risk of a rogue employee/DBA stealing the data, that's a valid concern to be aware of. At the current time our development team size is relatively small and myself being the only DBA so I'll need to put safeguards into place for this as we continue to grow.
Thank you for all your help and input. Have a great day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply