Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Properly Implementing Data Retrieval w/Column Level Encryption Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 8:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:23 AM
Points: 336, Visits: 388
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.
Post #1525019
Posted Saturday, December 21, 2013 12:40 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1525287
Posted Monday, December 23, 2013 8:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:23 AM
Points: 336, Visits: 388
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".
Post #1525563
Posted Monday, December 23, 2013 1:13 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1525621
Posted Thursday, December 26, 2013 7:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:23 AM
Points: 336, Visits: 388
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.
Post #1525969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse